diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 21 |
1 files changed, 21 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 094148fb..19c2d2fa 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1046,6 +1046,27 @@ ORDER BY dealid, date DESC, periodenddate DESC END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION public.list_ir_capfloor_positions(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) + RETURNS TABLE(deal_id character varying, strategy swaption_strat, amount double precision, + cap_or_floor cap_or_floor, strike double precision, floating_rate_index character varying, + expiration_date date, initial_margin_percentage double precision, + comments character varying, cp_code character varying) AS $$ +BEGIN +RETURN QUERY +SELECT a.dealid, folder, + (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * (a.amount - coalesce(terminated_amount, 0.)), + a.cap_or_floor, a.strike, a.floating_rate_index, a.expiration_date, a.initial_margin_percentage, + a.comments, a.cp_code +FROM capfloors a +LEFT JOIN (SELECT dealid, SUM(termination_amount) AS terminated_amount + FROM terminations WHERE termination_date <= p_date GROUP BY dealid) b USING (dealid) +WHERE a.amount IS DISTINCT FROM terminated_amount +AND a.expiration_date > p_date +AND a.trade_date <= p_date +AND fund=p_fund; +END; +$$ LANGUAGE plpgsql; + CREATE OR REPLACE FUNCTION list_ir_swaption_positions(IN p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(deal_id character varying, strategy swaption_strat, notional double precision, option_type option_type, strike double precision, security_id varchar, expiration_date date, maturity date, |