diff options
| -rw-r--r-- | sql/dawn.sql | 37 |
1 files changed, 36 insertions, 1 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index e8f4d803..bdb4b0c5 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1263,6 +1263,41 @@ RETURN QUERY END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE function list_cds_positions_fcm(p_date date, + p_fcm text, + p_fund fund DEFAULT 'SERCGMAST'::fund) +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, + fixed_rate float, currency currency, notional float) AS $$ +BEGIN +RETURN QUERY + WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, + cds.currency, + SUM(cds.notional) + OVER (PARTITION BY cds.security_id, cds.maturity, cds.fcm) AS notional + FROM list_cds(p_date, p_fund) cds WHERE (cds.attach is NULL AND cds.folder != 'MBSCDS' AND cds.fcm=p_fcm)) + SELECT DISTINCT ON (tmp.security_id, tmp.maturity) * FROM tmp WHERE abs(tmp.notional) > 0.1; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION trade_upload_baml(p_date date, p_fcm text, p_fund fund DEFAULT 'SERCGMAST::fund') +RETURNS TABLE("Type" text, "CCP ID" text, "B/S Protection" text, "Identifier" text, "Notional" float, "Fixed Rate" integer, "Trade Date" date, "Maturity Date" date, CCY text, "Payment Freq" text) +SELECT 'CDX', + 123456, + CASE WHEN notional >0 THEN 'BUY' ELSE 'SELL' END, + tradeid, + abs(notional), + fixed_rate * 100, + '6/27/2022', + l.maturity, + currency, + 'QUARTERLY' +FROM + list_cds_positions_fcm('2022-06-27', 'BAML') l +LEFT JOIN + index_maturity_markit imm ON redindexcode=security_id AND imm.maturity=l.maturity +END; +$$ LANGUAGE plpgsql; + CREATE OR REPLACE FUNCTION cds_globeop_name(redcode text, fixed_rate float, maturity date, index index_type, tenor tenor) RETURNS text AS $$ @@ -3295,4 +3330,4 @@ CREATE TABLE citco_submission_status( identifier text null, committed bool not null default False, submit_date timestamptz NOT NULL DEFAULT now() -);
\ No newline at end of file +); |