diff options
| -rw-r--r-- | sql/dawn.sql | 39 |
1 files changed, 39 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 96bf50af..e2b430a5 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1053,6 +1053,45 @@ WHERE fund=p_fund and notional IS DISTINCT FROM terminated_amount END; $$ LANGUAGE plpgsql; + +CREATE TYPE LIST_SWAPTION AS( + id integer, + trade_date date, + busell bool, + option_type option_type, + security_id varchar(12), + security_desc varchar(32), + maturity date, + fixed_rate float, + currency currency, + folder swaption_strat, + notional float, + strike float, + expiration_date date, + initial_margin_percentage float, + cpty_id text, + cp_code varchar(12) +); + +CREATE OR REPLACE function list_swaptions(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) +-- Do not include unsettled terminations +RETURNS SETOF LIST_SWAPTION AS $$ +BEGIN +RETURN QUERY +SELECT id, trade_date, security_id, security_desc, maturity, fixed_rate, currency, + folder, buysell, (notional - coalesce(terminated_amount, 0.)) * + (2* buysell -1) AS notional, + strike, expiration_date, initial_margin_percentage, + cpty_id, + cp_code +FROM swaptions LEFT JOIN ( + SELECT dealid, SUM(termination_amount) AS terminated_amount + FROM terminations WHERE fee_payment_date < p_date GROUP BY dealid) b USING (dealid) +WHERE fund=p_fund and notional IS DISTINCT FROM terminated_amount + AND trade_date <= p_date AND expiration_date > p_date; +END; +$$ LANGUAGE plpgsql; + CREATE OR REPLACE function list_cds_positions (p_date date, strat cds_strat DEFAULT NULL::cds_strat, fund fund DEFAULT 'SERCGMAST'::fund) |