diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 180 |
1 files changed, 180 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 01a1e10b..54515e27 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -3054,6 +3054,186 @@ CREATE TABLE beta_crt( PRIMARY KEY (date, strategy) ); +CREATE OR REPLACE FUNCTION get_bond_settlements() +RETURNS TABLE ( + id text, + settle_date date, + fund fund, + cp_code text, + asset_class text, + account text, + currency currency, + payment_amount numeric(11,2) +) AS $$ +BEGIN + RETURN QUERY + SELECT bt.id::text AS id, + bt.settle_date, + bt.fund::fund, + bt.cp_code::text, + 'BOND' AS asset_class, + bt.account::text, + 'USD'::currency AS currency, + CASE + WHEN bt.buysell THEN - bt.net_amount::numeric(11,2) + WHEN NOT bt.buysell THEN bt.net_amount::numeric(11,2) + ELSE NULL::numeric(11,2) + END AS payment_amount + FROM bond_trades bt LEFT JOIN accounts a ON bt.account=a.code + WHERE bt.tradeid IS NOT NULL; +END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION get_tranche_settlements() +RETURNS TABLE ( + id text, + settle_date date, + fund fund, + cp_code text, + asset_class text, + account text, + currency currency, + payment_amount numeric(11,2) +) AS $$ +BEGIN + RETURN QUERY + SELECT + ct.id::text, + ct.settle_date, + ct.fund::fund, + ct.cp_code::text, + 'TRANCHE'::text AS asset_class, + 'OTC'::text AS account, + ct.currency::currency, + ct.upfront::numeric(11,2) AS payment_amount + FROM cds_trades ct + WHERE orig_attach IS NOT NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION get_tranchepayment_settlements() +RETURNS TABLE ( + id text, + settle_date date, + fund fund, + cp_code text, + asset_class text, + account text, + currency currency, + payment_amount numeric(11,2) +) AS $$ +BEGIN + RETURN QUERY + SELECT tc.tranche_id::TEXT AS id, tc.date AS settle_date, + cds.fund::fund, cds.cp_code::text, 'TRANCHE_CF' AS asset_class, + 'OTC'::text AS account, tc.currency::currency, + (COALESCE(tc.principal,0) + COALESCE(tc.accrued,0))::numeric(11,2) AS payment_amount + FROM tranche_cashflows tc LEFT JOIN cds ON tranche_id=cds.id ; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION get_swaption_settlements() +RETURNS TABLE ( + id text, + settle_date date, + fund fund, + cp_code text, + asset_class text, + account text, + currency currency, + payment_amount numeric(11,2) +) AS $$ +BEGIN + RETURN QUERY + SELECT swaptions.id::text AS id, + swaptions.settle_date, + swaptions.fund::fund, + swaptions.cp_code::text, + 'SWAPTION' AS asset_class, + 'OTC'::text AS account, + 'USD'::currency AS currency, + - swaptions.fee::numeric(11,2) AS payment_amount + FROM swaption_trades swaptions; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION get_trs_settlements() +RETURNS TABLE ( + id text, + settle_date date, + fund fund, + cp_code text, + asset_class text, + account text, + currency currency, + payment_amount numeric(11,2) +) AS $$ +BEGIN + RETURN QUERY + SELECT tt.id::text, + tt.settle_date, + tt.fund::fund, + tt.cp_code::text, + 'TRS' AS asset_class, + 'OTC'::text AS account, + 'USD'::currency AS currency, + tt.upfront::numeric(11,2) AS payment_amount + FROM trs_trades tt; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION get_spot_settlements() +RETURNS TABLE ( + id text, + settle_date date, + fund fund, + cp_code text, + asset_class text, + account text, + currency currency, + payment_amount numeric(11,2) +) AS $$ +BEGIN + RETURN QUERY + SELECT st.dealid::text AS dealid, + st.settle_date, + st.fund::fund, + st.cp_code::text, + 'SPOT' AS asset_class, + a.name AS account, + unnest(ARRAY[st.buy_currency, st.sell_currency])::currency AS currency, + unnest(ARRAY[st.buy_amount, - st.sell_amount])::numeric(11,2) AS amount + FROM spots st LEFT JOIN accounts2 a USING (cash_account); +END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION get_fxswap_settlements() +RETURNS TABLE ( + id text, + settle_date date, + fund fund, + cp_code text, + asset_class text, + account text, + currency currency, + payment_amount numeric(11,2) +) AS $$ +BEGIN + RETURN QUERY + SELECT ft.dealid::text, + unnest(ARRAY[ft.near_settle_date, ft.near_settle_date, ft.far_settle_date, ft.far_settle_date]) AS settle_date, + ft.fund::fund, + ft.cp_code::text, + 'SPOT' AS asset_class, + a.name AS account, + unnest(ARRAY[ft.near_buy_currency, ft.near_sell_currency, ft.far_buy_currency, ft.far_sell_currency])::currency AS currency, + unnest(ARRAY[ft.near_buy_amount, - ft.near_sell_amount, ft.far_buy_amount, - ft.far_sell_amount])::numeric(11,2) AS amount + FROM fx_swaps ft LEFT JOIN accounts2 a USING (cash_account); +END; +$$ LANGUAGE plpgsql; + CREATE OR REPLACE VIEW payment_settlements AS SELECT trades.settle_date, trades.fund, |