-- -*- mode: sql; sql-product: postgres; -*- CREATE TYPE bond_strat AS ENUM('M_STR_MAV', 'M_STR_MEZZ', 'CSO_TRANCH', 'M_CLO_BB20', 'M_CLO_AAA', 'M_CLO_BBB', 'M_MTG_IO', 'M_MTG_THRU', 'M_MTG_GOOD', 'M_MTG_B4PR', 'M_MTG_RW', 'M_MTG_FP', 'M_MTG_LMG', 'M_MTG_SD', 'M_MTG_PR', 'M_MTG_CRT_SD', 'CRT_LD', 'CRT_LD_JNR', 'MTG_REPO'); CREATE TYPE cds_strat AS ENUM('HEDGE_CSO', 'HEDGE_CLO', 'HEDGE_MAC', 'HEDGE_MBS', 'SER_IGSNR', 'SER_IGMEZ', 'SER_IGEQY', 'SER_IGINX', 'SER_HYSNR', 'SER_HYMEZ', 'SER_HYEQY', 'SER_HYINX', 'SER_IGCURVE', 'MBSCDS', 'IGOPTDEL', 'HYOPTDEL', 'SER_ITRXCURVE', 'HYEQY', 'HYMEZ', 'HYSNR', 'HYINX', 'IGEQY', 'IGMEZ', 'IGSNR', 'IGINX', 'XOEQY', 'XOMEZ', 'XOINX', 'EUEQY', 'EUMEZ', 'EUSNR', 'EUINX', 'BSPK', 'XCURVE', 'SER_HYCURVE', '*'); CREATE TYPE swaption_strat AS ENUM('IGPAYER', 'IGREC', 'HYPAYER', 'HYREC', 'STEEP', 'DV01'); CREATE TYPE swaption_type AS ENUM('CD_INDEX_OPTION', 'SWAPTION'); CREATE TYPE repo_strat AS ENUM(''); CREATE TYPE spot_strat AS ENUM('M_STR_MAV', 'M_STR_MEZZ', 'SER_ITRXCURVE', 'M_CSH_CASH', 'TCSH'); CREATE TYPE future_strat AS ENUM('M_STR_MAV', 'M_MTG_IO', 'M_STR_MEZZ', 'M_MTG_RW', 'SER_ITRXCURVE', 'M_CSH_CASH', 'DELTAONE', 'TCSH'); CREATE TYPE cash_strat AS ENUM('M_CSH_CASH', 'MBSCDSCSH', 'SER_IGCVECSH', 'SER_ITRXCVCSH', 'CSOCDSCSH', 'IGCDSCSH', 'HYCDSCSH', 'CLOCDSCSH', 'IGTCDSCSH', 'MACCDSCSH', 'M_STR_MEZZ', 'IRDEVCSH', 'TCSH', 'COCSH', 'SER_ITRXCURVE', 'XCURVE', 'BSPK', 'SER_HYINX', 'IGOPTDEL', 'IGINX', 'HYINX', 'HEDGE_CLO', 'CVECSH'); CREATE TYPE asset_class AS ENUM('CSO', 'Subprime', 'CLO', 'Tranches', 'Futures', 'Cash', 'FX', 'Cleared', 'CRT'); CREATE TYPE action AS ENUM('NEW', 'UPDATE', 'CANCEL'); CREATE TYPE currency AS ENUM('USD', 'CAD', 'EUR', 'JPY', 'GBP'); CREATE TYPE bbg_type AS ENUM('Mtge', 'Corp'); CREATE type day_count AS ENUM('ACT/360', 'ACT/ACT', '30/360', 'ACT/365'); CREATE type bus_day_convention AS ENUM('Modified Following', 'Following', 'Modified Preceding', 'Preceding', 'Second-Day-After', 'End-of-Month'); CREATE type last_period_convention AS ENUM('Adjusted', 'Unadjusted'); CREATE type index_type AS ENUM('IG', 'HY', 'EU', 'LCDX', 'XO', 'BS', 'HYBB'); CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr'); CREATE TYPE swap_type AS ENUM('CD_INDEX', 'CD_INDEX_TRANCHE', 'CD_BASKET_TRANCHE', 'ABS_CDS', 'CD_INDEX_OPTION', 'SWAPTION', 'CREDIT_DEFAULT_SWAP'); CREATE TYPE repo_type AS ENUM('REPO', 'REVERSE REPO'); CREATE TYPE option_type AS ENUM('PAYER', 'RECEIVER'); CREATE TYPE isda AS ENUM('ISDA2014', 'ISDA2003Cred'); CREATE TYPE protection AS ENUM('Buyer', 'Seller'); CREATE TYPE call_notice AS ENUM('24H', '48H', '3D', '4D', '5D', '6D', '1W', '8D', '9D', '10D', '2W', '1M', '2M'); CREATE TYPE settlement_type AS ENUM('Delivery', 'Cash'); CREATE TYPE beta_type AS ENUM('IG', 'CRT', 'EU', 'SUBPRIME', 'XO'); CREATE TYPE account_type AS ENUM('Cash', 'Brokerage', 'Fcm', 'Future'); CREATE TYPE equity_option_type AS ENUM ('CALL', 'PUT'); CREATE TABLE accounts( code varchar(5) PRIMARY KEY, name text, custodian text NOT NULL, cash_account text NOT NULL, counterparty varchar(12) REFERENCES counterparties(code), fund fund NOT NULL, account_type account_type NOT NULL, active boolean NOT NULL); CREATE TABLE counterparties(code varchar(12) PRIMARY KEY, name text, city text, state varchar(2), location text, dtc_number integer, sales_contact text, sales_email text, sales_phone text, valuation_contact1 text, valuation_email1 text, valuation_contact2 text, valuation_email2 text, valuation_contact3 text, valuation_email3 text, valuation_contact4 text, valuation_email4 text, notes text, instructions text, cash_counterparty boolean NOT NULL DEFAULT true, cds_counterparty boolean NOT NULL DEFAULT false); CREATE INDEX ON counterparties(name); -- deprecated CREATE TABLE bond_old(id serial primary key, dealid varchar(28) UNIQUE, fund fund NOT NULL DEFAULT 'SERCGMAST'; lastupdate timestamp DEFAULT now(), action action NOT NULL, folder bond_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(12) NOT NULL, cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, trade_date date NOT NULL, settle_date date NOT NULL, cusip varchar(9), isin varchar(12), identifier varchar(12), description varchar(32) NOT NULL, buysell bool NOT NULL, faceamount float NOT NULL, price float NOT NULL, accrued float, asset_class asset_class, ticket text, principal_payment float, accrued_payment float, CONSTRAINT bonds2_check CHECK (cusip IS NOT NULL OR isin IS NOT NULL)); CREATE TABLE bonds(id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, dealid varchar(28) GENERATED ALWAYS AS ('SC_'||CASE WHEN asset_class ='Subprime' THEN 'SUB' WHEN asset_class='CLO' THEN 'CLO' WHEN asset_class='CSO' THEN 'CSO' WHEN asset_class='CRT' THEN 'CRT' END ||id::text) STORED, folder bond_strat, portfolio portfolio NOT NULL, cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, trade_date date NOT NULL, settle_date date NOT NULL, cusip varchar(9), isin varchar(12), identifier varchar(12), description varchar(32) NOT NULL, buysell bool NOT NULL, faceamount float NOT NULL, price numeric NOT NULL, accrued numeric, asset_class asset_class NOT NULL, ticket text, principal_payment float, accrued_payment float, current_face float, allocated boolean NOT NULL DEFAULT false, stale bool NOT NULL DEFAULT true, emailed bool NULL DEFAULT false, bbg_ticket_id text NULL REFERENCES public.bond_tickets(bbg_ticket_id) ON DELETE SET NULL, CONSTRAINT bonds_check CHECK (((cusip IS NOT NULL) OR (isin IS NOT NULL))); CREATE OR REPLACE FUNCTION notify_id() RETURNS trigger AS $$ BEGIN PERFORM pg_notify(TG_TABLE_NAME, NEW.id::text); NEW.stale = true; RETURN NEW; END $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION update_portf() RETURNS TRIGGER AS $$ BEGIN NEW.portfolio = ( CASE WHEN NEW.folder::text LIKE '%MTG%' THEN 'MORTGAGES' WHEN NEW.folder::TEXT LIKE '%CLO%' THEN 'CLO' WHEN NEW.folder::TEXT LIKE '%CRT%' THEN 'MORTGAGES' WHEN NEW.folder::TEXT LIKE '%STR%' THEN 'STRUCTURED' WHEN NEW.folder::TEXT='M_CSH_CASH' THEN 'CASH' WHEN NEW.folder::TEXT='TCSH' THEN 'TRANCHE' WHEN NEW.folder::TEXT='SER_ITRXCURVE' THEN 'CURVE' END)::portfolio; RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER portf BEFORE INSERT OR UPDATE OF folder ON bonds FOR EACH ROW EXECUTE PROCEDURE update_portf(); CREATE TRIGGER fx_portf BEFORE INSERT OR UPDATE OF folder ON spots FOR EACH ROW EXECUTE PROCEDURE update_portf(); CREATE TRIGGER bond_notify BEFORE INSERT OR UPDATE OF identifier, cusip, isin, faceamount, price, trade_date, settle_date, accrued ON bonds FOR EACH ROW WHEN (current_setting('application_name') != 'update_loop') EXECUTE PROCEDURE notify_id(); CREATE OR REPLACE FUNCTION set_identifier() RETURNS trigger AS $$ BEGIN NEW.identifier = COALESCE(NEW.identifier, NEW.cusip, NEW.isin); RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER update_identifier BEFORE INSERT OR UPDATE OF identifier, cusip, isin ON bonds FOR EACH ROW EXECUTE PROCEDURE set_identifier(); CREATE TABLE bond_allocation( id integer generated always as identity primary key, tradeid integer not null references bonds ON DELETE CASCADE, notional float not null, code text not null references accounts, submitted boolean default False, UNIQUE (tradeid, code)); CREATE TYPE status AS ENUM('Pending', 'Processed', 'Submitted', 'Aknowledged'); CREATE TABLE bond_submission( id integer generated always as identity primary key, allocation_id integer not null references allocation on delete cascade, "action" action not null, submit_date = timestamptz DEFAULT now(), status status NOT NULL DEFAULT 'PENDING' ); CREATE TABLE cds(id serial primary key, dealid varchar(28) UNIQUE, fund fund NOT NULL DEFAULT 'SERCGMAST', lastupdate timestamptz DEFAULT now(), action action NOT NULL DEFAULT 'NEW', portfolio portfolio NOT NULL, folder cds_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(12) NOT NULL, cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, trade_date date NOT NULL, effective_date date NOT NULL, maturity date NOT NULL, currency currency NOT NULL, payment_rolldate bus_day_convention NOT NULL DEFAULT 'Following', notional float NOT NULL, fixed_rate float NOT NULL, day_count day_count NOT NULL DEFAULT 'ACT/360', frequency smallint NOT NULL DEFAULT 4, protection protection NOT NULL, security_id varchar(12) NOT NULL, security_desc varchar(32) NOT NULL, upfront float NOT NULL, upfront_settle_date date NOT NULL, swap_type swap_type NOT NULL, orig_attach smallint, orig_detach smallint, attach float, detach float, clearing_facility varchar(12) DEFAULT NULL, isda_definition isda, initial_margin_percentage float DEFAULT NULL, index_ref float DEFAULT NULL, corr_attach float DEFAULT NULL, corr_detach float DEFAULT NULL, account_code varchar(5) NOT NULL REFERENCES accounts(code), cpty_id text, globeop_id int, stale bool default true, traded_level numeric(9, 5) NULL, exercised_from text REFERENCES swaptions(dealid), CONSTRAINT tranche_check CHECK (( swap_type IN ('CD_INDEX_TRANCHE', 'BESPOKE') AND (orig_attach IS NOT NULL AND orig_detach IS NOT NULL AND clearing_facility IS NULL)) OR (swap_type='CD_INDEX' AND orig_attach IS NULL AND orig_detach IS NULL AND clearing_facility='ICE-CREDIT') OR (swap_type='ABS_CDS' AND orig_attach IS NULL AND orig_detach IS NULL AND clearing_faciliy IS NULL)) ); ALTER TABLE cds OWNER TO dawn_user; CREATE TRIGGER cds_dealid AFTER INSERT ON cds FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); CREATE TRIGGER cds_notify AFTER INSERT OR UPDATE OF security_id, maturity, trade_date, upfront_settle_date, traded_level, notional, protection ON cds FOR EACH ROW WHEN (current_setting('application_name') != 'autobooker') EXECUTE FUNCTION notify_id(); CREATE OR REPLACE function update_attach() RETURNS TRIGGER AS $$ DECLARE factor float; cum_loss float; BEGIN IF NEW.orig_attach IS NULL AND NEW.orig_detach IS NULL THEN RETURN NEW; ELSE SELECT indexfactor, cumulativeloss INTO factor, cum_loss FROM index_version WHERE redindexcode=NEW.security_id; NEW.attach = factor*LEAST(GREATEST((NEW.orig_attach - cum_loss)/factor, 0), 1); NEW.detach = factor*LEAST(GREATEST((NEW.orig_detach - cum_loss)/factor, 0), 1); RETURN NEW; END IF; END $$ LANGUAGE plpgsql; CREATE TRIGGER cds_attach BEFORE INSERT OR UPDATE OF orig_attach, orig_detach, security_id ON cds FOR EACH ROW EXECUTE PROCEDURE update_attach(); CREATE TABLE repos ( id int4 GENERATED ALWAYS AS IDENTITY PRIMARY KEY, dealid varchar(28) NULL GENERATED ALWAYS AS ((('SCREPO'::text || id::text))) STORED, "fund" fund NOT NULL, lastupdate timestamptz NULL DEFAULT now(), "action" action NULL, folder bond_strat NOT NULL, cp_code varchar(12) NOT NULL REFERENCES counterparties(code), trade_date date NOT NULL, settle_date date NOT NULL, cusip varchar(9) NULL, isin varchar(12) NULL, identifier varchar(12) NULL, description varchar(32) NOT NULL, transaction_indicator repo_type NULL, faceamount float8 NOT NULL, price float8 NOT NULL, "currency" currency NOT NULL DEFAULT 'USD'::currency, expiration_date date NULL, weighted_amount float8 NULL, haircut float8 NULL, repo_rate float8 NOT NULL, "call_notice" call_notice NULL, day_count day_count NULL, ticket varchar NULL, open_repo bool NULL, account_code text NOT NULL DEFAULT 'BAC' REFERENCES accounts(code), CHECK ((haircut IS NOT NULL AND weighted_amount IS NULL) OR (haircut IS NULL AND weighted_amount IS NOT NULL)), CHECK (cusip IS NOT NULL OR isin IS NOT NULL) ); ALTER TABLE repo OWNER TO dawn_user; CREATE TABLE swaptions(id serial PRIMARY KEY, dealid varchar(28) UNIQUE, fund fund NOT NULL DEFAULT 'SERCGMAST', lastupdate timestamptz DEFAULT now(), action action NOT NULL, portfolio portfolio NOT NULL, folder swaption_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(12) NOT NULL, cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, swap_type swaption_type NOT NULL, trade_date date NOT NULL, settle_date date NOT NULL, buysell bool NOT NULL, notional float NOT NULL, option_type option_type NOT NULL, strike float NOT NULL, price float NOT NULL, expiration_date date NOT NULL, initial_margin_percentage float, security_id varchar(12) NOT NULL, security_desc varchar(32), maturity date NOT NULL, currency currency NOT NULL, settlement_type settlement_type NOT NULL, fixed_rate float, termination_date date, termination_fee float, termination_amount float, termination_cp varchar(12) REFERENCES counterparties(code) ON UPDATE CASCADE, cpty_id text, globeop_id int, full_globeop_id text GENERATED ALWAYS AS ('SWO'||lpad(cast(globeop_id as text), 6, '0')|| 'K00SCLMA') STORED, ); CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); CREATE TABLE capfloors ( id serial PRIMARY KEY, fund fund NOT NULL DEFAULT 'SERCGMAST', dealid varchar(28) UNIQUE, lastupdate timestamp NULL DEFAULT now(), "action" action NOT NULL, folder swaption_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(12) NOT NULL, cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, "comments" varchar(100), floating_rate_index varchar(12) NOT NULL, floating_rate_index_desc varchar(32), buysell bool NOT NULL, cap_or_floor cap_or_floor NOT NULL, strike float8 NOT NULL, value_date date NOT NULL, expiration_date date NOT NULL, premium_percent float8 NOT NULL, pricing_type pricing_type NOT NULL, payment_frequency frequency NOT NULL, fixing_frequency frequency NOT NULL, day_count_counvention day_count NULL, bdc_convention bus_day_convention NULL, payment_mode payment_mode NOT NULL, payment_at_beginning_or_end begin_or_end NOT NULL, initial_margin_percentage float8 NULL, initial_margin_currency currency NULL, amount float8 NOT NULL, trade_date date NOT NULL, swap_type capfloor_type NOT NULL, reset_lag int4, trade_confirm varchar, cpty_id text, globeop_id text ) CREATE TRIGGER dealid AFTER INSERT ON capfloors FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); CREATE TABLE futures ( id serial NOT NULL, dealid varchar(28), lastupdate timestamp NULL DEFAULT now(), "action" action NOT NULL, folder future_strat NOT NULL, account_code varchar(12) NOT NULL REFERENCES accounts(code), trade_date date NOT NULL, settle_date date NOT NULL, buysell bool NOT NULL, bbg_ticker varchar(32) NOT NULL, quantity float8 NOT NULL, price float8 NOT NULL, commission float8 NULL, swap_type future_type NOT NULL, security_desc varchar(32) NOT NULL, maturity date NOT NULL, currency currency NOT NULL, exchange varchar(3) NOT NULL, fund fund NOT NULL DEFAULT 'SERCGMAST'::fund, CONSTRAINT futures_dealid_key UNIQUE (dealid), CONSTRAINT futures_pkey PRIMARY KEY (id), ); ALTER table futures OWNER TO dawn_user; CREATE trigger dealid after insert on futures for each row execute procedure auto_dealid(); CREATE TABLE wires ( id serial NOT NULL, dealid varchar(28) NULL, lastupdate timestamp NULL DEFAULT now(), "action" action NOT NULL, folder cash_strat NOT NULL, code varchar(5) NOT NULL, amount float8 NOT NULL, currency currency NOT NULL, trade_date date NOT NULL, CONSTRAINT wires_pkey PRIMARY KEY (id), CONSTRAINT wires_code_fkey FOREIGN KEY (code) REFERENCES accounts(code) ); ALTER table wires OWNER TO dawn_user; CREATE trigger dealid after insert on wires for each row execute procedure auto_dealid(); CREATE TABLE spots ( id serial NOT NULL, fund fund NOT NULL DEFAULT 'SERCGMAST'::fund, dealid varchar(28) NULL, lastupdate timestamp NULL DEFAULT now(), "action" action NOT NULL, folder spot_strat NOT NULL, account_code varchar(12) NOT NULL REFERENCES accounts(code), trade_date date NOT NULL, settle_date date NOT NULL, spot_rate float8 NOT NULL, buy_currency currency NOT NULL, buy_amount float8 NOT NULL, sell_currency currency NOT NULL, sell_amount float8 NOT NULL, commission_currency currency NULL, commission float8 NULL, CONSTRAINT spots_pkey PRIMARY KEY (id), ); CREATE TABLE terminations ( id int GENERATED BY default as identity primary KEY, dealid varchar(28) NOT NULL, termination_date date NOT NULL, termination_cp varchar(12) REFERENCES counterparties(code), termination_amount float8, termination_fee float8 NOT NULL, partial_termination bool NOT null default false, new_gtid int, fee_payment_date date NOT NULL, ); create trigger dealid after insert on spots for each row execute procedure auto_dealid();; CREATE OR REPLACE FUNCTION auto_dealid() RETURNS TRIGGER AS $$ DECLARE stub text; sqlstr text; BEGIN sqlstr:= format('UPDATE '|| TG_TABLE_SCHEMA ||'.' ||TG_TABLE_NAME || ' SET %s WHERE id = %L AND dealid is NULL'; IF (TG_TABLE_NAME = 'bonds') THEN stub := 'SC_'; sqlstr := format(sqlstr, 'dealid = $1||upper(left(asset_class::text,3))||id, identifier = COALESCE(identifier, cusip, isin)', NEW.id); ELSE CASE TG_TABLE_NAME WHEN 'cds' THEN stub := 'SCCDS'; WHEN 'swaptions' THEN stub := 'SWPTN'; WHEN 'futures' THEN stub := 'SCFUT'; WHEN 'wires' THEN stub := 'SCCSH'; WHEN 'capfloors' THEN stub := 'CAP'; WHEN 'spots' THEN stub := 'SCFX'; END CASE; sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); END IF; EXECUTE sqlstr USING stub; RETURN NEW; END; $$ language plpgsql; CREATE TRIGGER dealid AFTER INSERT ON capfloors FOR EACH ROW EXECUTE PROCEDURE auto_dealid() ; CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); ALTER TABLE swaptions OWNER TO dawn_user; CREATE TABLE securities(identifier varchar(12) PRIMARY KEY, cusip varchar(9), isin varchar(12), description varchar(32), face_amount float, maturity date, floater boolean, spread float, coupon float, frequency smallint, day_count day_count, first_coupon_date date, pay_delay smallint, currency currency default 'USD', bbg_type bbg_type default 'Mtge', asset_class asset_class, paid_down date default 'Infinity', start_accrued_date date, issuer text, reset_index text, coupon_type text, payment_day integer, issue_date date, figi text); ALTER TABLE securities OWNER TO dawn_user; CREATE TABLE marks( date date NOT NULL, price float NOT NULL, identifier varchar(12) NOT NULL REFERENCES securities(figi) ON DELETE CASCADE ON UPDATE CASCADE, id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, UNIQUE (date, identifier) ); CREATE UNIQUE INDEX ON marks(date, identifier); CREATE TABLE external_marks_deriv(date date NOT NULL, identifier text NOT NULL, local_nav float, --trade ccy nav base_nav float, --USD nav cpty varchar(4), ia float, -- ia in USD PRIMARY KEY(identifier, date)); CREATE OR REPLACE VIEW id_mappings AS SELECT trade_date, dealid, cpty_id, notional*price/100 as nav, 'SWAPTION'::trade_type as trade_type from swaptions UNION SELECT trade_date, dealid, cds.cpty_id, upfront as nav, 'TRANCHE' FROM cds WHERE attach IS NOT NULL ORDER BY trade_date; CREATE TABLE cashflow_history( identifier varchar(12) REFERENCES securities(figi) ON UPDATE CASCADE, date date, principal_bal float, principal float, interest float, coupon float, PRIMARY KEY (identifier, date)); CREATE TABLE risk_numbers( identifier varchar(12) REFERENCES securities, date date, delta float, index_delta index_type, duration float, wal float, undiscounted_price float, model_price float, PRIMARY KEY (identifier, date)); CREATE TABLE fx(date date PRIMARY KEY, eurusd float, cadusd float); CREATE TABLE external_marks( identifier varchar(12) REFERENCES securities ON UPDATE CASCADE, date date, mark float, source text, PRIMARY KEY (identifier, date, source)); CREATE TABLE mark_source_mapping( globeop text, final text, PRIMARY KEY (globeop)); CREATE TABLE subscription_and_fee( date date, fund fund, subscription float, redemption float, incentive_fee float, management_fee float, PRIMARY KEY (fund, date)); CREATE TYPE LS AS ENUM('L', 'S'); CREATE TABLE bbh_val( accounting_date date NOT NULL, row integer NOT NULL, custody_head_account_number float, security_id text, security_description text, asset_currency currency, original_face float, base_price float, local_unit_cost float, base_unit_cost float, local_market_value float, base_market_value float, security_id_type text, sub_security_type_code text, source text, investment_type_code text, investment_type_description text, security_long_description text, security_type_code text, total_current_assets float, total_current_liabilities float, total_net_assets float, interest_rate float, quantity float, quantity_scale float, long_short_indicator LS, fx_rate float, maturity_date date, PRIMARY KEY (accounting_date, row)); CREATE TABLE bbh_pnl( accounting_date date NOT NULL, row integer NOT NULL, security_id text, custody_head_account_number int, issue_name text, local_currency currency, base_market_value float, base_change_income float, base_change_fx_realized_gain_loss float, base_change_fx_unrealized_gain_loss float, base_change_unrealized_gain_loss float, base_change_realized_gain_loss float, base_change_miscellaneous_income float, base_change_expense float, base_change_total float, sub_security_type_code text, source text, PRIMARY KEY (accounting_date, row)); CREATE TYPE bony_asset_type AS ENUM( 'CASH & CASH EQUIVALENTS', 'FIXED INCOME SECURITIES', 'FUTURES CONTRACTS', 'NON CUSIP RELATED INCOME'); CREATE TABLE bowdst_val( row integer not null, as_of_date date not null, source_account_number integer REFERENCES bowdst_accounts(account_number), mellon_security_id text, asset_type bony_asset_type, security_description_1 text, security_description_2 text, maturity_date date, coupon_rate float, current_notional float, local_price float, local_currency_code currency, base_price float, local_cost float, base_cost float, local_market_value float, base_market_value float, local_unrealized_pnl float, base_unrealized_pnl float, local_notional_cost float, base_notional_cost float, local_notional_value float, base_notional_value float, gen_ledger_acct integer, report_run_date date, link_ref text, counterparty_name text, exchange_rate float, original_strike_price float, current_strike_price float, cusip text, ticker text, isin text, PRIMARY KEY (as_of_date, row) ); CREATE TABLE bowdst_accounts( account_number integer NOT NULL PRIMARY KEY, account_name text NOT NULL ); CREATE TABLE bowdst_pnl( row integer not null, begin_date date not null, end_date date not null, derivative_type text, security_id text, security_description_1 text, security_description_2 text, local_currency currency, coupon_rate float, asset_type_name text, asset_type_category bony_asset_type, unrealized_currency float, unrealized_investment float, total_unrealized float, ending_shares_par float, opening_receivables float, closing_receivables float, income float, journal_entry_income float, accretion_amortization float, journal_entry_accretion_amortization float, realized_gain_loss float, journal_entry_realized_gl float, realized_loss_impaired_securities float, net_investment_income float, cusip text, ticker text, isin text, link_ref text, realized_investment float, realized_currency float, realized_settled float, realized_traded float, PRIMARY KEY (begin_date, end_date, row) ); CREATE OR REPLACE function list_marks(p_date date) RETURNS TABLE(p_date date, identifier varchar(12), price float) AS $$ BEGIN RETURN SELECT DISTINCT ON (identifier) marks.date, marks.identifier, marks.price FROM marks WHERE date<= p_date ORDER BY identifier, marks.date DESC; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_marks_var(p_date date, VARIADIC p_identifier varchar(12)[]) RETURNS TABLE(identifier varchar(12), price float) AS $$ BEGIN RETURN QUERY SELECT a.identifier, b.price FROM (SELECT unnest(p_identifier) AS identifier) a LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_risk_numbers(p_date date, assetclass asset_class, include_unsettled boolean DEFAULT False, p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(identifier varchar(12), description varchar(32), mark float, delta float, index_delta index_type, duration float, wal float, undiscounted_price float, model_price float) AS $$ BEGIN RETURN QUERY SELECT a.identifier, a.description, b.price, c.delta, c.index_delta, c.duration, c.wal, c.undiscounted_price, c.model_price FROM list_positions(p_date, assetclass, include_unsettled, p_fund) a LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks WHERE date <= p_date ORDER BY identifier, date DESC) b ON a.figi=b.identifier LEFT JOIN (SELECT DISTINCT ON (identifier) * FROM risk_numbers WHERE date <=p_date ORDER BY identifier, date DESC) c USING ON a.figi=c.identifier; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE VIEW bond_trades AS SELECT bonds.dealid, bond_allocation.tradeid, bond_allocation.id, bonds.identifier, bonds.description, bonds.asset_class, bonds.trade_date, bonds.settle_date, bonds.buysell, bonds.price, bonds.accrued, bonds.folder, bonds.portfolio, bonds.principal_payment * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS principal_payment, bonds.accrued_payment * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS accrued_payment, (bonds.principal_payment + bonds.accrued_payment) * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS net_amount, bonds.current_face * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS current_face, bond_allocation.notional AS faceamount, accounts.fund, counterparties.code AS cp_code, counterparties.name AS counterparty FROM bonds LEFT JOIN bond_allocation ON bonds.id = bond_allocation.tradeid LEFT JOIN accounts USING (code) LEFT JOIN counterparties ON bonds.cp_code = counterparties.code WINDOW w AS (PARTITION BY bond_allocation.tradeid); CREATE OR REPLACE function list_positions(p_date date, p_class asset_class DEFAULT NULL, include_unsettled boolean DEFAULT True, p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(identifier varchar(12), description varchar(32), notional float, strategy strategy, curr_cpn float, start_accrued_date date, last_settle_date date, principal_payment float, accrued_payment float, currency currency, daycount day_count, bbg_type bbg_type, figi varchar(12)) AS $$ DECLARE sqlquery text; DECLARE asset_opt text; DECLARE unsettled_opt text; BEGIN IF p_class is not NULL THEN asset_opt := 'and securities.asset_class=$2 '; ELSE asset_opt := ''; END IF; IF include_unsettled THEN unsettled_opt = 'or settle_date>=$1'; ELSE unsettled_opt = ''; END IF; sqlquery := 'WITH temp AS (SELECT bond_trades.identifier, asset_class, settle_date, folder, principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) ) OVER (PARTITION BY bond_trades.identifier) notional FROM bond_trades WHERE trade_date<=$1 AND fund=$3) SELECT DISTINCT ON (identifier) securities.identifier, securities.description, notional, substring(folder::text FROM ''(?:M_)?(.*)'')::strategy AS folder, securities.coupon, start_accrued_date, settle_date, temp.principal_payment, temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type, securities.figi FROM temp LEFT JOIN securities USING (identifier) WHERE (temp.notional>0 '||unsettled_opt||') AND paid_down>$1 '||asset_opt ||' ORDER BY identifier, settle_date desc'; RETURN QUERY EXECUTE sqlquery USING p_date, p_class, p_fund; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_positions_range(start_date date, end_date date, p_class asset_class DEFAULT NULL) RETURNS TABLE(identifier varchar(12), description varchar(32), notional float, strategy bond_strat, curr_cpn float, start_accrued_date date, last_settle_date date, principal_payment float, accrued_payment float, currency currency, daycount day_count, bbg_type bbg_type) AS $$ DECLARE sqlquery text; DECLARE asset_opt text; DECLARE unsettled_opt text; BEGIN sqlquery := 'WITH temp AS (SELECT bonds.identifier, asset_class, settle_date, folder, principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) ) OVER (PARTITION BY bonds.identifier) notional FROM bonds WHERE trade_date <=$2) SELECT DISTINCT ON (temp.identifier) temp.identifier, securities.description, notional, folder, securities.coupon, start_accrued_date, settle_date, temp.principal_payment, temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type FROM temp LEFT JOIN securities USING (identifier) WHERE (temp.notional>0 or (temp.notional==0 and settle_date>=$1)) AND paid_down>$1 '||asset_opt ||' ORDER BY identifier, settle_date desc'; RETURN QUERY EXECUTE sqlquery USING start_date, end_date, p_class; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function risk_positions(p_date date, p_assetclass asset_class, p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE (description varchar(32), identifier varchar(12), notional float, price float, strategy strategy, factor float, local_market_value float, usd_market_value float, curr_cpn float, int_acc float, last_pay_date date, principal_payment float, accrued_payment float, last_settle_date date, figi varchar(12)) AS $$ BEGIN RETURN QUERY SELECT a.description, a.identifier, a.notional, c.price, a.strategy, coalesce(b.factor, 1), c.price/100. * a.notional * (CASE WHEN coalesce(b.factor,1)=0 THEN 1 ELSE coalesce(b.factor,1) END), c.price/100. * a.notional * (CASE WHEN coalesce(b.factor,1)=0 THEN 1 ELSE coalesce(b.factor,1) END) * fxrate, b.coupon, a.notional * coalesce(b.factor,1) * fxrate * yearfrac(case WHEN start_accrued_date>=p_date+1 THEN b.prev_cpn_date ELSE start_accrued_date END, p_date+1, daycount) * b.coupon/100., b.last_pay_date, a.principal_payment, a.accrued_payment, a.last_settle_date, a.figi FROM list_positions(p_date, p_assetclass, true, p_fund) a LEFT JOIN factors_history(p_date) b ON a.figi = b.identifier LEFT JOIN list_marks(p_date) c ON a.figi = c.identifier LEFT JOIN fx_rate(p_date) USING (currency) ORDER by identifier asc; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE VIEW orig_cds AS SELECT DISTINCT ON (dealid) dealid, folder, index, series, version, tenor, fixed_rate, notional, upfront, protection, attach, detach, trade_date, upfront_settle_date FROM cds JOIN index_desc ON index_desc.redindexcode=cds.security_id AND index_desc.maturity=cds.maturity ORDER BY dealid, trade_date; CREATE OR REPLACE VIEW external_marks_mapped AS select date, identifier, mark, b.final as source from external_marks a left join mark_source_mapping b on a.source = b.globeop order by a.date asc; CREATE OR REPLACE function query_positions(p_type text DEFAULT NULL) RETURNS text AS $$ DECLARE query text; BEGIN query := 'WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, cds.currency, %s %s SUM(cds.notional) OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach) AS notional FROM list_cds($1, $2) cds WHERE %s) SELECT DISTINCT ON (tmp.security_id, tmp.maturity %s) * FROM tmp WHERE tmp.notional!=0'; IF p_type = 'tranche' THEN RETURN format(query, 'cds.orig_attach,cds.orig_detach,cds.attach,cds.detach,' 'cds.initial_margin_percentage,', 'sum(initial_margin_percentage * abs(notional)/100) ' 'OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach)' 'as initial_margin, ', 'cds.orig_attach is NOT NULL', ',tmp.orig_attach'); ELSIF p_type = 'cds' THEN RETURN format(query, '', '', 'cds.attach is NULL AND cds.folder!=''MBSCDS''', ''); ELSIF p_type = 'abs' THEN RETURN format(query, '', '', 'cds.folder=''MBSCDS''', ''); ELSE RETURN format(query, 'cds.attach, cds.detach,', '', '', ',tmp.attach'); END IF; END; $$ LANGUAGE plpgsql; CREATE TYPE LIST_CDS AS( id integer, trade_date date, security_id varchar(12), security_desc varchar(32), maturity date, fixed_rate float, currency currency, folder cds_strat, protection protection, notional float, orig_attach smallint, orig_detach smallint, attach float, detach float, fcm text, initial_margin_percentage float, cpty_id text, cp_code varchar(12) ); CREATE OR REPLACE function list_cds(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS SETOF LIST_CDS AS $$ BEGIN RETURN QUERY SELECT id, trade_date, security_id, security_desc, maturity, fixed_rate, currency, folder, protection, (notional - coalesce(terminated_amount, 0.)) * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END) AS notional, orig_attach, orig_detach, attach, detach, account_code, initial_margin_percentage, cpty_id, cp_code FROM cds LEFT JOIN ( SELECT dealid, SUM(termination_amount) AS terminated_amount FROM terminations WHERE termination_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 maturity > p_date; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds2(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) -- Do not include unsettled terminations RETURNS SETOF LIST_CDS AS $$ BEGIN RETURN QUERY SELECT id, trade_date, security_id, security_desc, maturity, fixed_rate, currency, folder, protection, (notional - coalesce(terminated_amount, 0.)) * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END) AS notional, orig_attach, orig_detach, attach, detach, account_code, initial_margin_percentage, cpty_id, cp_code FROM cds 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 maturity > p_date; 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) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, fixed_rate float, currency currency, notional float) AS $$ BEGIN IF strat IS NULL THEN RETURN QUERY EXECUTE query_positions('cds') USING p_date, fund; ELSE RETURN QUERY SELECT a.security_id, a.security_desc, a.maturity, a.fixed_rate, a.currency, a.notional FROM list_cds_positions_by_strat(p_date, fund) a WHERE folder=strat; END IF; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_positions_by_strat(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, fixed_rate float, currency currency, folder cds_strat, notional float) AS $$ BEGIN RETURN QUERY WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, cds.currency, cds.folder, SUM(cds.notional) OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder) AS notional FROM list_cds(p_date, p_fund) cds WHERE (cds.attach is NULL AND cds.folder != 'MBSCDS')) SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_positions_by_strat_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, folder cds_strat, notional float) AS $$ BEGIN RETURN QUERY WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, cds.currency, cds.folder, SUM(cds.notional) OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder, 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, tmp.folder) * FROM tmp WHERE tmp.notional!=0; 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 $$ DECLARE result text; stub text; fcm text; BEGIN result := 'CDS_%s%s_%s.0000000000_ICE-CREDIT_%s_%s'; IF tenor = '3yr' AND redcode = '2I65BYDJ1' THEN stub := '.3'; ELSE stub := ''; END IF; IF redcode = '2I65BRQY9' OR redcode = '2I65BYBE4' OR redcode = '2I666VCT0' THEN fcm = 'WELLSFCM'; ELSE fcm = 'BOMLCM'; END IF; result := format(result, redcode, stub, fixed_rate, fcm, to_char(maturity, 'YYYYMMDD')); RETURN result; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_tranche_positions(p_date date, fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, fixed_rate float, currency currency, orig_attach smallint, orig_detach smallint, attach float, detach float, initial_margin_percentage float, initial_margin float, notional float) AS $$ BEGIN RETURN QUERY EXECUTE query_positions('tranche') USING p_date, fund; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_tranche_positions_by_strat(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, fixed_rate float, currency currency, orig_attach smallint, orig_detach smallint, attach float, detach float, folder cds_strat, notional float) AS $$ BEGIN RETURN QUERY WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, cds.currency, cds.orig_attach, cds.orig_detach, cds.attach, cds.detach, cds.folder, SUM(cds.notional) OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach, cds.folder) AS notional FROM list_cds(p_date, p_fund) cds WHERE cds.orig_attach is NOT NULL) SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.orig_attach, tmp.folder) * FROM tmp WHERE tmp.notional!=0; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_abscds_positions(p_date date, 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 EXECUTE query_positions('abs') USING p_date, fund; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_marks_old(p_date date, strat cds_strat DEFAULT NULL::cds_strat) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_type, p_series smallint, p_version smallint, tenor tenor, maturity date, notional float, factor float, name text, coupon float, duration float, theta float, price float, closespread float, clean_nav float, accrued float) AS $$ DECLARE days integer; eur_fx float; params text; sqlquery text; BEGIN days:=days_accrued(p_date); SELECT eurusd INTO eur_fx FROM fx WHERE date=p_date; IF strat IS NOT NULL THEN params := '$1, $4'; ELSE params := '$1'; END IF; sqlquery := format( 'WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.tenor, c.indexfactor/100. AS fact, cds_globeop_name(a.security_id, a.fixed_rate, a.maturity, c.index, c.tenor) FROM list_cds_positions(%s) a LEFT JOIN index_desc c ON (a.security_id=c.redindexcode AND a.maturity=c.maturity)), index_price AS (SELECT index, series, version, d.tenor, closeprice, d.duration, d.closespread, d.theta2 FROM index_quotes d WHERE date=$1) SELECT temp.security_id, temp.security_desc, temp.index, temp.series, temp.version, temp.tenor, temp.maturity, temp.notional, temp.fact, temp.cds_globeop_name, temp.fixed_rate/100, index_price.duration, index_price.theta2, index_price.closeprice, index_price.closespread, (1.-index_price.closeprice/100.) * temp.notional * temp.fact * (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END), -temp.notional * temp.fixed_rate/100. * temp.fact * $3 / 360 * (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END) FROM temp LEFT JOIN index_price USING (index, series, version, tenor)', params); IF strat IS NOT NULL THEN RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days, strat; ELSE RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days; END IF; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_marks_pre(p_date date, strat cds_strat DEFAULT NULL::cds_strat, fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_type, p_series smallint, p_version smallint, tenor tenor, maturity date, notional float, factor float, coupon float, duration float, theta float, price float, closespread float, clean_nav float, accrued float) AS $$ DECLARE days integer; eur_fx float; params text; sqlquery text; and_clause text; BEGIN days:=days_accrued(p_date); SELECT DISTINCT ON (date) eurusd INTO eur_fx FROM fx WHERE date BETWEEN p_date - INTERVAL '3 DAYS' AND p_date; IF strat IS NOT NULL THEN params := '$1, $5, $2'; and_clause := 'AND strat = ltrim($5::text, ''SER_'')::strategy'; ELSE params := '$1, NULL, $2'; and_clause := ''; END IF; sqlquery := format( 'WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.tenor, c.indexfactor/100. AS fact FROM list_cds_positions(%s) a LEFT JOIN index_desc c ON (a.security_id=c.redindexcode AND a.maturity=c.maturity)), index_price AS (SELECT DISTINCT ON (index, series, version, d.tenor) index, series, version, d.tenor, closeprice, d.duration2, d.closespread, d.theta2 FROM index_quotes d WHERE date BETWEEN $1 - INTERVAL ''3 days'' AND $1 ORDER BY index, series, version, d.tenor, date desc) SELECT temp.security_id, temp.security_desc, temp.index, temp.series, temp.version, temp.tenor, temp.maturity, temp.notional, temp.fact, temp.fixed_rate/100, index_price.duration2, index_price.theta2, index_price.closeprice, index_price.closespread, (1.-index_price.closeprice/100.) * temp.notional * temp.fact * (CASE WHEN temp.currency = ''EUR'' THEN $3 ELSE 1 END), -temp.notional * temp.fixed_rate/100. * temp.fact * $4 / 360 * (CASE WHEN temp.currency = ''EUR'' THEN $3 ELSE 1 END) FROM temp LEFT JOIN index_price USING (index, series, version, tenor)', params); IF strat IS NOT NULL THEN RETURN QUERY EXECUTE sqlquery USING p_date, fund, eur_fx, days, strat; ELSE RETURN QUERY EXECUTE sqlquery USING p_date, fund, eur_fx, days; END IF; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE VIEW bowdst_index AS SELECT as_of_date, p_index, p_series, maturity, admin_notional, admin_clean_nav FROM ( SELECT as_of_date, substring(security_description_1 FROM '(IG|HY|EU|XO)(?:R|VER)?(?:\.| )(?:[0-9]{2})')::index_type AS p_index, substring(security_description_1 FROM '(?:IG|HY|EUR|XOVER)(?:\.| )([0-9]{2})')::smallint as p_series, maturity_date AS maturity, (CASE WHEN coupon_rate=0 THEN current_notional ELSE -current_notional END) AS admin_notional, sum(base_market_value) OVER w as admin_clean_nav, row_number() OVER w FROM bowdst_val WHERE security_description_1 LIKE '%CCP%' WINDOW w AS (PARTITION BY as_of_date, substring(security_description_1 FROM '(IG|HY|EU|XO)(?:R|VER)?(?:\.| )(?:[0-9]{2})')::index_type, substring(security_description_1 FROM '(?:IG|HY|EUR|XOVER)(?:\.| )([0-9]{2})')::smallint, maturity_date) ) AS ss WHERE row_number=1; CREATE OR REPLACE function list_cds_marks( p_date date, strat cds_strat DEFAULT NULL::cds_strat, p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), index index_type, series smallint, version smallint, tenor tenor, maturity date, notional float, factor float, coupon float, duration float, theta float, price float, closespread float, clean_nav float, accrued float, globeop_nav float, globeop_notional float) AS $$ DECLARE sqlquery text; and_clause text; BEGIN IF strat IS NOT NULL THEN and_clause := 'AND strat = ltrim($2::text, ''SER_'')::strategy'; ELSE and_clause := ''; END IF; IF p_fund = 'SERCGMAST' OR p_fund = 'BOWDST' THEN sqlquery := format(' SELECT l.*, b.globeop_nav, b.globeop_notional FROM list_cds_marks_pre($1, $2, $3) l JOIN (SELECT a.security_id, a.maturity, sum(endbooknav) AS globeop_nav, sum(endqty) AS globeop_notional FROM (SELECT periodenddate as date, fund, endqty, endbooknav, split_part(invid, ''_'', 2) AS security_id, split_part(invid, ''_'', 6)::date AS maturity FROM valuation_reports WHERE invid LIKE ''CDS\_%%'' %s) a GROUP BY date, fund, a.security_id, a.maturity HAVING date=$1 AND fund=$3) b USING (security_id, maturity)', and_clause); -- ELSIF p_fund = 'BOWDST' THEN -- sqlquery := 'SELECT a.*, bowdst_index.admin_clean_nav, bowdst_index.admin_notional FROM list_cds_marks_pre($1, $2, $3) a LEFT JOIN bowdst_index USING (p_index, p_series, maturity) WHERE as_of_date=$1'; ELSIF p_fund = 'BRINKER' THEN sqlquery := 'SELECT *, 0::double precision, 0::double precision FROM list_cds_marks_pre($1, $2, $3)'; END IF; RETURN QUERY EXECUTE sqlquery USING p_date, strat, p_fund; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_marks_by_strat(p_date date) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), strategy cds_strat, p_index index_type, p_series smallint, p_version smallint, tenor tenor, maturity date, notional float, factor float, name text, coupon float, duration float, theta float, price float, closespread float, clean_nav float, accrued float) AS $$ DECLARE days integer; eur_fx float; params text; sqlquery text; BEGIN days:=days_accrued(p_date); SELECT eurusd INTO eur_fx FROM fx WHERE date=p_date; sqlquery := 'WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.tenor, c.indexfactor/100. AS fact, cds_globeop_name(a.security_id, a.fixed_rate, a.maturity, c.index, c.tenor) FROM list_cds_positions_by_strat($1) a LEFT JOIN index_desc c ON (a.security_id=c.redindexcode AND a.maturity=c.maturity)), index_price AS (SELECT index, series, version, d.tenor, closeprice, d.duration, d.closespread, d.theta FROM index_quotes d WHERE date=$1) SELECT temp.security_id, temp.security_desc, temp.folder, temp.index, temp.series, temp.version, temp.tenor, temp.maturity, temp.notional, temp.fact, temp.cds_globeop_name, temp.fixed_rate/100, index_price.duration, index_price.theta, index_price.closeprice, index_price.closespread, (1.-index_price.closeprice/100.) * temp.notional * temp.fact * (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END), -temp.notional * temp.fixed_rate/100. * temp.fact * $3 / 360 * (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END) FROM temp LEFT JOIN index_price USING (index, series, version, tenor)'; RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days; END; $$ LANGUAGE plpgsql; CREATE TABLE tranche_risk( date date, tranche_id integer REFERENCES cds(id), notional float, clean_nav float, accrued float, duration float, delta float, gamma float, theta float, corr01 float, tranche_factor float, upfront float, running float, corr_attach float, corr_detach float, index_refprice float, index_refspread float, index_duration float, hy_equiv float, theta_amount float, PRIMARY KEY (date, tranche_id)) CREATE OR REPLACE function list_tranche_marks(p_date date, fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_type, p_series smallint, p_version smallint, p_tenor tenor, maturity date, notional float, factor float, coupon integer, clean_nav float, accrued float, initial_margin float, theta float, duration float, tranchedelta float4, trancheupfrontmid float, indexrefprice float4, indexrefspread smallint, attach smallint, detach smallint, index_duration float) AS $$ DECLARE days integer; eur_fx float; BEGIN days:=days_accrued(p_date); SELECT eurusd INTO eur_fx FROM fx WHERE date=p_date; RETURN QUERY WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.basketid, d.tenor, (a.detach-a.attach)/(a.orig_detach-a.orig_attach) * c.indexfactor / 100 AS fact FROM list_tranche_positions(p_date, fund) a LEFT JOIN index_version c ON a.security_id=c.redindexcode LEFT JOIN index_maturity d USING (index, series, maturity)), risk_num AS (SELECT DISTINCT ON (index, series, a.attach, a.detach, tenor) * from risk_num_per_quote a WHERE quotedate BETWEEN p_date - interval '1 week' AND p_date + interval '1 day' ORDER by index, series, a.attach, a.detach, tenor, quotedate desc) SELECT temp.security_id, temp.security_desc, temp.index, temp.series, temp.version, temp.tenor, temp.maturity, temp.notional, temp.fact, trancherunningmid::integer, temp.notional * temp.fact * (case when temp.index = 'HY' then (1.-risk_num.trancheupfrontmid/100) else risk_num.trancheupfrontmid/100 end) * (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END), -temp.notional * temp.fact * trancherunningmid/10000. * days / 360 * (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END), temp.initial_margin, risk_num.theta, risk_num.duration, risk_num.tranchedelta, risk_num.trancheupfrontmid, risk_num.indexrefprice, risk_num.indexrefspread, temp.orig_attach, temp.orig_detach, risk_num.index_duration FROM temp LEFT JOIN risk_num ON temp.series=risk_num.series AND temp.orig_attach = risk_num.attach AND temp.orig_detach = risk_num.detach AND temp.tenor = risk_num.tenor; END $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION list_swaption_positions_and_risks(IN p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(deal_id character varying, security_id character varying, maturity date, notional double precision, option_type option_type, strike double precision, expiration_date date, serenitas_nav double precision, globeop_nav double precision, initial_margin double precision, latest_model_date date, delta double precision, gamma double precision, vega double precision, theta double precision, index index_type, series smallint, tenor tenor, duration double precision, portfolio portfolio, indexfactor float) AS $$ BEGIN RETURN QUERY -- TODO: fix the case of multiple index versions SELECT DISTINCT ON (a.dealid) a.dealid, a.security_id, c.maturity, (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * (a.notional - coalesce(terminated_amount, 0.)), a.option_type, a.strike, a.expiration_date, b.market_value, coalesce(endbooknav, base_market_value), f.ia, b.date, b.delta, b.gamma, b.vega, b.theta, c.index, c.series, c.tenor, d.duration, a.portfolio, c.indexfactor FROM swaptions a LEFT JOIN (SELECT * FROM swaption_marks where date <= p_date) b USING (dealid) LEFT JOIN index_desc c ON a.security_id=redindexcode AND a.maturity=c.maturity LEFT JOIN (SELECT * FROM index_quotes WHERE date=p_date) d USING (index, series, tenor) LEFT JOIN valuation_reports ON NULLIF(substring(invid FROM '\D{3}[0]*(\d*)K'), '')::integer = a.globeop_id LEFT JOIN bowdst_val ON link_ref = a.dealid AND as_of_date=p_date LEFT JOIN (SELECT dealid, SUM(termination_amount) AS terminated_amount FROM terminations WHERE termination_date <= p_date GROUP BY dealid) e USING (dealid) LEFT JOIN external_marks_deriv f ON identifier=cpty_id AND f.date=p_date WHERE a.notional IS DISTINCT FROM terminated_amount AND a.expiration_date > p_date AND trade_date <= p_date AND swap_type = 'CD_INDEX_OPTION' AND (periodenddate IS NULL OR periodenddate <= p_date) AND a.fund = p_fund ORDER BY dealid, date DESC, periodenddate DESC; END $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION 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, nav double precision ) 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, c.base_nav 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) LEFT JOIN (SELECT identifier, base_nav FROM external_marks_deriv WHERE date=p_date) c ON a.cpty_id = c.identifier 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, initial_margin_percentage double precision, cp_code character varying, nav double precision) AS $$ BEGIN RETURN QUERY SELECT a.dealid, folder, (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * (a.notional - coalesce(terminated_amount, 0.)), a.option_type, a.strike/100, a.security_id, a.expiration_date, a.maturity, a.initial_margin_percentage, a.cp_code, c.base_nav FROM swaptions a LEFT JOIN (SELECT dealid, SUM(termination_amount) AS terminated_amount FROM terminations WHERE termination_date <= p_date GROUP BY dealid) b USING (dealid) LEFT JOIN (SELECT identifier, base_nav FROM external_marks_deriv where date = p_date) c on a.cpty_id = c.identifier WHERE a.notional IS DISTINCT FROM terminated_amount AND a.expiration_date > p_date AND a.trade_date <= p_date AND a.swap_type = 'SWAPTION' AND fund=p_fund; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_abscds_marks(p_date date, fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(security_id varchar(12), cusip varchar(9), security_desc varchar(32), maturity date, notional float, factor float, fixed_rate float, clean_nav float, accrued float) AS $$ BEGIN RETURN QUERY WITH temp AS (SELECT a.*, b.price, c.factor, d.start_accrued_date, d.cusip FROM list_abscds_positions(p_date, fund) a LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks WHERE date<=p_date ORDER BY identifier, date desc) b ON a.security_id=b.identifier LEFT JOIN factors_history(p_date) c ON a.security_id=c.identifier LEFT JOIN securities d ON a.security_id=d.identifier) SELECT temp.security_id, temp.cusip, temp.security_desc, temp.maturity, temp.notional, temp.factor, temp.fixed_rate, temp.notional*temp.factor*(100.-temp.price)/100, -yearfrac(temp.start_accrued_date, p_date+1, 'ACT/360')*temp.fixed_rate/100*temp.notional*temp.factor FROM temp; END $$ LANGUAGE plpgsql; CREATE OR REPLACE function imm_date(p_date date) RETURNS date AS $$ from dates import imm_date return imm_date(p_date) $$ LANGUAGE plpython3u; CREATE OR REPLACE function days_accrued(p_date date) RETURNS integer AS $$ from dates import days_accrued return days_accrued(p_date) $$ LANGUAGE plpython3u; CREATE MATERIALIZED VIEW factors_history AS WITH temp AS ( SELECT c.date, c.identifier, c.principal, c.principal_bal, c.interest, lead(c.coupon) OVER w AS coupon, (- c.principal) - c.principal_bal + lag(c.principal_bal) OVER w AS losses FROM cashflow_history c WINDOW w AS (PARTITION BY c.identifier ORDER BY c.date) ) SELECT temp.date AS last_pay_date, temp.date-securities.pay_delay AS prev_cpn_date, temp.identifier, temp.principal_bal / securities.face_amount AS factor, temp.principal / securities.face_amount * 100::float AS principal, temp.interest / securities.face_amount * 100::float AS interest, temp.losses / securities.face_amount * 100::float AS losses, COALESCE(temp.coupon, securities.coupon) AS coupon FROM temp JOIN securities ON temp.identifier=securities.figi; CREATE UNIQUE INDEX factors_history_pkey ON factors_history(prev_cpn_date, identifier); CREATE OR REPLACE function factors_history(p_date date) RETURNS SETOF factors_history AS $$ BEGIN RETURN QUERY SELECT DISTINCT ON (identifier) * FROM factors_history WHERE prev_cpn_date<=p_date ORDER BY identifier, prev_cpn_date desc; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION isleapyear ( D date ) RETURNS boolean AS $$ DECLARE y INTEGER; BEGIN y := extract (year from D); if (y % 4) != 0 then return false; end if; if (y % 400) = 0 then return true; end if; return (( y % 100) != 0); END; $$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION yearfrac(date1 date, date2 date, daycount day_count) RETURNS float AS $$ DECLARE factor float; y1 integer; y2 integer; m1 integer; m2 integer; d1 integer; d2 integer; BEGIN IF daycount='30/360' THEN y1 := extract(YEAR FROM date1); y2 := extract(YEAR FROM date2); m1 := extract(MONTH FROM date1); m2 := extract(MONTH FROM date2); d1 := extract(DAY FROM date1); d2 := extract(DAY FROM date2); IF d2=31 and (d1=30 or d1=31) THEN d2:=30; END IF; IF d1=31 THEN d1:=30; END IF; factor:= (360*(y2-y1) + 30*(m2-m1)+d2-d1)/360.; ELSIF daycount='ACT/365' THEN factor:=(date2-date1)/365.; ELSIF daycount='ACT/360' THEN factor:=(date2-date1)/360.; ELSIF daycount='ACT/ACT' THEN IF isleapyear(date1) THEN factor:=(date2-date1)/366.; ELSE factor:=(date2-date1)/365.; END IF; END IF; RETURN factor; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function tranche_factor(attach smallint, detach smallint, index_factor float, cumulativeloss float) RETURNS float AS $$ -- index_factor and cumulativeloss are in percents, eg: -- tranche_factor(15::smallint, 25::smallint, 98, 0.71) DECLARE newattach float; newdetach float; BEGIN newattach:=LEAST(GREATEST((attach-cumulativeloss)/index_factor, 0), 1); newdetach:=LEAST(GREATEST((detach-cumulativeloss)/index_factor, 0), 1); RETURN (newdetach-newattach)/(detach-attach)*index_factor; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function fx_rate(p_date date) RETURNS TABLE(currency currency, fxrate float) AS $$ BEGIN RETURN QUERY SELECT unnest(Array['USD', 'EUR', 'CAD'])::currency, unnest(Array[1, eurusd, cadusd]) FROM fx WHERE date<=p_date ORDER by date desc LIMIT 3; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function latest_sim(p_date date) RETURNS integer AS $$ DECLARE id integer; BEGIN SELECT model_id_sub into id FROM priced INNER JOIN model_versions USING (model_id_sub) INNER JOIN model_versions_nonagency USING (model_id_sub) INNER JOIN simulations_nonagency USING (simulation_id) WHERE description = 'normal' AND timestamp >= p_date LIMIT 1; RETURN id; End; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION id_translate(VARIADIC id1 varchar(9)[]) RETURNS TABLE(id2 varchar(12)) AS $$ BEGIN RETURN QUERY SELECT figi FROM securities JOIN unnest(id1) WITH ORDINALITY AS t(cusip, id) USING (cusip) ORDER BY id; END; $$ LANGUAGE plpgsql; -- Not sure how to map enums so use text for now CREATE FOREIGN TABLE priced( model_id_sub smallint, cusip varchar(9), model_version smallint, normalization text, timestamp timestamp, pv float, pv_RnW float, pv_FB float, pv_io float, pv_po float, modDur float, modDur_io float, modDur_po float, wal float, wal_width float, wal_io float, wal_po float, delta_hpi float, delta_ir float, delta_ir_io float, delta_ir_po float, delta_mult float, delta_yield float, delta_quantile float, delta_RnW float, tot_gamma_hpi float, tot_gamma_ir float, tot_gamma float) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); -- We want to use INHERITS here, but will only be available on 9.5 CREATE FOREIGN TABLE priced_orig_ntl( model_id_sub smallint, cusip varchar(9), model_version smallint, normalization text, timestamp timestamp, pv float, pv_RnW float, pv_FB float, pv_io float, pv_po float, modDur float, modDur_io float, modDur_po float, wal float, wal_width float, wal_io float, wal_po float, delta_hpi float, delta_ir float, delta_ir_io float, delta_ir_po float, delta_mult float, delta_yield float, delta_quantile float, delta_RnW float, tot_gamma_hpi float, tot_gamma_ir float, tot_gamma float) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); CREATE FOREIGN TABLE priced_percentiles( model_id_sub smallint, cusip varchar(9), model_version smallint, percentile float, normalization text, timestamp timestamp, pv float, pv_io float, pv_po float, modDur float, modDur_io float, modDur_po float, wal float, wal_width float, wal_io float, wal_po float) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); CREATE FOREIGN TABLE priced_percentiles_orig_ntl( model_id_sub smallint, cusip varchar(9), model_version smallint, percentile float, normalization text, timestamp timestamp, pv float, pv_io float, pv_po float, modDur float, modDur_io float, modDur_po float, wal float, wal_width float, wal_io float, wal_po float) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); CREATE TYPE INDEXFAMILY AS ENUM('ITRAXX-Asian', 'LCDXNA', 'MCDXNA', 'ITRAXX-SOVX', 'ITRAXX-SDI', 'ITRAXX-L', 'CDX', 'ITRAXX-European'); CREATE TYPE curr AS ENUM('USD', 'EUR', 'JPY', 'GBP', 'CAD'); IMPORT FOREIGN SCHEMA public LIMIT TO (index_desc, index_version, index_version_markit, index_maturity, index_maturity_markit, index_quotes, markit_tranche_quotes, risk_num_per_quote, rates) FROM SERVER postgresql_server INTO public; CREATE FOREIGN TABLE model_versions( model_id smallint, asset_class text, start_time timestamp, model_id_sub smallint) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); CREATE FOREIGN TABLE model_versions_nonagency( model_id_sub smallint, granularity_agg text, num_LS_mult smallint, d_LS_mult float, simulation_id smallint, remark text) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); CREATE FOREIGN TABLE simulations_nonagency( simulation_id smallint, granularity_simu text, num_hpi_scens smallint, num_ir_scens smallint, macro_timestamp timestamp, data_source text, forecast_date_roll date, forecast_date date, hamp_stepup_credit float, description text) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); -- #bonds that get written down -- update securities set identifier='073879R75_A' where identifier='073879R75'; -- update bonds set identifier='073879R75_A' where identifier='073879R75'; -- refresh materialized view factors_history; CREATE OR REPLACE function list_subprime_data(p_date date, orig_flag bool, VARIADIC p_cusip varchar(9)[]) RETURNS TABLE(v1 float, v2 float, v3 float, duration float, percentile5 float, percentile25 float, percentile50 float, percentile75 float, percentile95 float, yield_delta float, wal float, io_pv float, po_pv float, rnw float, ir_io_delta float, ir_po_delta float, hpi_delta float, delta_rnw float, delta_mult float, v1pv_RnW float, v1_lsdel float, v1_hpidel float, v1_irdel float, pv_FB float) AS $$ DECLARE query text; opt_constraint text; BEGIN IF NOT orig_flag THEN opt_constraint := 'AND normalization =''current_notional'''; ELSE opt_constraint := ''; END IF; query:= 'WITH left_table AS ( WITH temp AS (SELECT cusip, model_version, pv, modDur, delta_yield, wal, pv_io, pv_po, pv_RnW, delta_ir_io, delta_ir_po, delta_hpi, delta_RnW, delta_mult, delta_ir, pv_FB FROM %I WHERE timestamp BETWEEN $1 AND $1 + INTERVAL ''1 day'' AND model_id_sub=$3 ' || opt_constraint || ') SELECT a.cusip, a.pv as v1, b.pv as v2, c.pv AS v3, a.modDur, c.delta_yield, c.wal, c.pv_io, c.pv_po, c.pv_RnW, c.delta_ir_io, c.delta_ir_po, c.delta_hpi, c.delta_RnW, c.delta_mult, a.pv_RnW as v1pv_RnW, a.delta_mult as v1_lsdel, a.delta_hpi as v1_hpidel, a.delta_ir as v1_irdel, c.pv_FB FROM (SELECT * FROM temp WHERE model_version=1) a, (SELECT * FROM temp WHERE model_version=2) b, (SELECT * FROM temp WHERE model_version=3) c WHERE a.cusip = b.cusip AND a.cusip=c.cusip), right_table AS ( WITH temp AS( SELECT cusip, PV, percentile FROM %I WHERE timestamp BETWEEN $1 AND $1 + INTERVAL ''1 day'' AND model_version=3 AND model_id_sub=$3 AND percentile in (5, 25, 50, 75, 95)' || opt_constraint ||') SELECT a.cusip, a.PV AS pv5, b.PV AS pv25, c.PV AS pv50, d.PV AS pv75, e.PV AS pv95 FROM (SELECT cusip, PV FROM temp WHERE percentile=5) a, (SELECT cusip, PV FROM temp WHERE percentile=25) b, (SELECT cusip, PV FROM temp WHERE percentile=50) c, (SELECT cusip, PV FROM temp WHERE percentile=75) d, (SELECT cusip, PV FROM temp WHERE percentile=95) e WHERE a.cusip=b.cusip AND b.cusip=c.cusip and c.cusip=d.cusip and d.cusip=e.cusip and e.cusip=a.cusip) SELECT left_table.v1, left_table.v2, left_table.v3, left_table.moddur, right_table.pv5, right_table.pv25, right_table.pv50, right_table.pv75, right_table.pv95, left_table.delta_yield, left_table.wal, left_table.pv_io, left_table.pv_po, left_table.pv_RnW, left_table.delta_ir_io, left_table.delta_ir_po, left_table.delta_hpi, left_table.delta_RnW, left_table.delta_mult, left_table.v1pv_RnW, left_table.v1_lsdel, left_table.v1_hpidel, left_table.v1_irdel, left_table.pv_FB FROM (SELECT unnest($2) AS cusip) l LEFT JOIN left_table ON left_table.cusip=l.cusip LEFT JOIN right_table ON left_table.cusip=right_table.cusip'; IF orig_flag THEN query := format(query, 'priced_orig_ntl', 'priced_percentiles_orig_ntl'); ELSE query := format(query, 'priced', 'priced_percentiles'); END IF; SET enable_nestloop = off; RETURN QUERY EXECUTE query USING p_date, p_cusip, latest_sim(p_date); SET enable_nestloop = on; END $$ LANGUAGE plpgsql; CREATE TABLE swaption_marks( dealid varchar(28) REFERENCES swaptions(dealid), market_value float, delta float, gamma float, vega float) CREATE TABLE subprime_risk( date date, figi varchar(12) NOT NULL, pv1 float, pv2 float, pv3 float, modDur float, pv5 float, pv25 float, pv50 float, pv75 float, pv95 float, delta_yield float, wal float, pv_io float, pv_po float, pv_RnW float, delta_ir_io float, delta_ir_po float, delta_hpi float, delta_RnW float, delta_mult float, v1pv_RnW float, v1_lsdel float, v1_hpidel float, v1_irdel float, pv_FB float, bond_yield float, hy_equiv float, delta_ir float, PRIMARY KEY (date, figi)) CREATE TABLE beta( date date, asset_class beta_type, beta float, PRIMARY KEY (date, asset_class)) CREATE TYPE portfolio AS ENUM('CASH', 'CLO', 'CURVE', 'GFS_HELPER_BUSINESS_UNIT', 'HEDGE_MAC', 'HY', 'IG', 'LQD_TRANCH', 'MORTGAGES', 'OPTIONS', 'SERCGLLC__SERCGLLC', 'SERCGLTD__SERCGLTD', 'SER_TEST__SER_TEST', 'STRUCTURED', 'IR', 'TRANCHE'); CREATE TYPE fund AS ENUM('SERCGLLC', 'SERCGLTD', 'SERCGMAST', 'SER_TEST', 'BRINKER', 'BOWDST') CREATE TYPE strategy AS ENUM( -- CLO portfolio 'CLOCDSCSH', 'CLO_AAA', 'CLO_BB20', 'CLO_BBB', 'HEDGE_CLO', -- TRANCHE portfolio 'HYEQY', 'HYMEZ', 'HYSNR', 'HYINX', 'IGEQY', 'IGMEZ', 'IGSNR', 'IGINX', 'BSPK', 'TCSH', 'XOEQY', 'XOMEZ', 'XOINX', 'EUEQY', 'EUMEZ', 'EUSNR', 'EUINX', -- CURVE portfolio 'IGCURVE', 'IGCVECSH', 'ITRXCURVE', 'ITRXCVCSH', 'HYCURVE', 'XCURVE', 'CVECSH', -- OPTIONS portfolio 'IGOPTDEL', 'IGPAYER', 'IGREC', 'HYOPTDEL', 'HYPAYER', 'HYREC', 'IGCDSCSH', 'HYCDSCSH', 'COCSH' -- IR portfolio 'IR', 'IRDEVCSH', 'STEEP', 'FLAT', 'DELTAONE', 'DV01', -- STRUCTURED portfolio 'STR_MAV', 'STR_MEZZ', 'HEDGE_CSO', 'CSO_TRANCH', 'CSOCDSCSH', -- MORTGAGES portfolio 'HEDGE_MBS', 'MTG_B4PR', 'MTG_FP', 'MTG_GOOD', 'MTG_IO', 'MTG_LMG', 'MTG_REPO' 'MTG_PR', 'MTG_RW', 'MTG_SD', 'MTG_THRU', 'MBSCDS', 'MBSCDSCSH', 'CRT_LD', 'CRT_SD', 'CRT_LD_JNR', -- IG portfolio (deprecated) 'IGTCDSCSH', 'IGCURVE', 'IGEQY', 'IGINX', 'IGMEZ', 'IGSNR', -- HY portfolio (deprecated) 'HYTCDSCSH', 'HYCURVE', 'HYEQY', 'HYINX', 'HYMEZ', 'HYSNR', -- HEDGE_MAC portfolio 'HEDGE_MAC', 'MACCDSCSH', -- CASH portfolio 'M_CSH_CASH', 'M_CSH_EXP', -- GFS_HELPER_BUSINESS_UNIT portfolio 'GFS_TRANSFER_HELPER', -- LQD_TRANCH (deprecated) 'LQD_TRANCH') CREATE TABLE valuation_reports( custacctname text, endbookcost float, endbookmv float, endbooknav float, endbookunrealfxgl float, endbookunrealincome float, endbookunrealmtm float, endlocalcost float, endlocalmv float, endlocalmarketprice float, endqty float, fund fund Not NULL, gfstranid1 text, invccy currency, invdesc text, invid text, invtype text, knowledgedate timestamp NOT NULL, periodenddate date NOT NULL, port portfolio, strat strategy, row integer NOT NULL, counterparty varchar(12) REFERENCES counterparties(code), PRIMARY KEY(periodenddate, fund, row) ); CREATE INDEX on valuation_reports (periodenddate); CREATE TYPE longshort AS ENUM('L', 'S'); CREATE TABLE pnl_reports( date date NOT NULL, fund fund NOT NULL, port portfolio, strat strategy, longshortindicator longshort, custacctname text, pricelist text, invassettype text, invccy currency, invdesc text, invid text, endqty float, endlocalmarketprice float, dailybookrealmtm float, dailybookunrealmtm float, dailybookrealfxgl float, dailybookunrealfxgl float, dailybookrealincome float, dailybookunrealincome float, dailybookmiscrevexp float, dailytotalbookpl float, mtdbookrealmtm float, mtdbookunrealmtm float, mtdbookrealfxgl float, mtdbookunrealfxgl float, mtdbookrealincome float, mtdbookunrealincome float, mtdbookmiscrevexp float, mtdtotalbookpl float, row integer NOT NULL, PRIMARY KEY(date, fund, row) ); CREATE INDEX on pnl_reports (date); CREATE TYPE clearing_cp AS ENUM('ICE-CREDIT', 'NOT CLEARED'); CREATE TYPE trade_type AS ENUM('CREDIT_DEFAULT_SWAP', 'SWAPTION', 'TRANCHE'); CREATE TYPE transaction_status AS ENUM('Bilateral', 'Cleared'); CREATE TYPE calendar AS ENUM('Payment-GB,US', 'Payment-US,GB', 'Payment-EU,GB', 'Payment-US'); CREATE TYPE clearing_broker AS ENUM('ML', 'SGFCM', 'BOMLCM', 'WELLSFCM'); CREATE TYpe frequency AS ENUM('Monthly', 'Quarterly'); CREATE TABLE cds_reports( date date NOT NULL, row integer NOT NULL, attachment_point float, exhaustion_point float, basis day_count, "buy/sell" protection, ccp clearing_cp, calendar calendar, cash_account text, ccy currency, client_ref_id text, comments text, commission float, contractual_definition isda, counterparty text REFERENCES counterparties(code), cpty_ref_id text, created_date timestamp, description text, effective_date date, external_trade_id text, factor float, fixed_rate float, frequency frequency, fund fund, gtid text, geneva_id text, independent_amount float, independent_perc float, maturity_date date, notional float, original_gtid text, original_notional float, period_end_date last_period_convention, price float, prime_broker clearing_broker, product_sub_type swap_type, red_code text, recovery_rate float, remaining_notional float, roll_convention bus_day_convention, strategy strategy, strike float, swapswire_id text, trade_date date, trade_type trade_type, transaction_status transaction_status, underlying_id text, underlying_name text, upfront_fee float, upfront_fee_date date, executing_broker text, "1st_cpn_date" date, PRIMARY KEY(date, row)); CREATE INDEX on cds_reports (date); CREATE TYPE mark_list AS (date date, identifier text, "BROKER" float, "BVAL" float, "IDC" float, "MANAGER" float, "MARKIT" float, "PB" float, "PRICESERVE" float, "PRICINGDIRECT" float, "REUTERS" float, "S&P" float); CREATE OR REPLACE FUNCTION get_mark_matrix(identifier varchar(9)) RETURNS SETOF mark_list AS $$ import numpy as np import pandas as pd source_list = ["BROKER", "BVAL", "IDC", "MANAGER", "MARKIT", "PB", "PRICESERVE", "PRICINGDIRECT", "REUTERS", "S&P"] sql_string = """SELECT identifier, date, source, mark FROM external_marks_mapped WHERE identifier = $1 ORDER BY date, source""" plan = plpy.prepare(sql_string, ["varchar"]) df = pd.DataFrame.from_records(plpy.cursor(plan, [identifier])) df = df.groupby(['date', 'identifier', 'source']).mean().unstack(-1) df.columns = df.columns.droplevel(level=0) df = df.reindex(columns=source_list).reset_index() for t in (df.itertuples(index=False)): yield [t[0], t[1]] + [None if np.isnan(v) else v for v in t[2:]] $$ LANGUAGE plpython3u; CREATE OR REPLACE VIEW tranche_risk_serenitas AS SELECT tranche_risk.date, tranche_id as trade_id, b.globeop_id, security_desc, index, series, maturity, orig_attach, orig_detach, tranche_risk.notional, admin_notional, clean_nav as serenitas_clean_nav, admin_clean_nav, accrued as serenitas_accrued, admin_accrued, base_nav AS cpty_nav, duration, delta, gamma, theta, theta_amount, tranche_factor, tranche_risk.corr_attach, tranche_risk.corr_detach, tranche_risk.upfront, tranche_risk.running, index_refprice, index_refspread, index_duration, hy_equiv, initial_margin_percentage FROM tranche_risk LEFT JOIN LATERAL (SELECT globeop_id FROM id_mapping WHERE id_mapping.serenitas_id=tranche_id AND date <= tranche_risk.date ORDER by date desc LIMIT 1) b ON true LEFT JOIN cds ON (tranche_id=id) RIGHT JOIN (SELECT invid, periodenddate, sum(endbookunrealincome) AS admin_accrued, sum(endbooknav-endbookunrealincome) AS admin_clean_nav, sum(endqty) AS admin_notional FROM valuation_reports GROUP BY invid, periodenddate) a ON (NULLIF(substring(invid FROM '\D{3}[0]*(\d*)K00SCLMA'), '')::integer=b.globeop_id AND periodenddate=tranche_risk.date) LEFT JOIN index_version ON (security_id=redindexcode) LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.date=tranche_risk.date ORDER BY index, series, orig_attach; -- bony reports version -- CREATE OR REPLACE VIEW tranche_risk_bowdst AS -- SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, maturity, orig_attach, -- orig_detach, tranche_risk.notional, -- admin_notional, -- clean_nav as serenitas_clean_nav, -- admin_clean_nav, -- accrued as serenitas_accrued, -- NULL AS admin_accrued, -- base_nav AS cpty_nav, -- duration, delta, gamma, theta, theta_amount, tranche_factor, -- tranche_risk.corr_attach, tranche_risk.corr_detach, -- tranche_risk.upfront, tranche_risk.running, -- index_refprice, index_refspread, index_duration, hy_equiv, initial_margin_percentage -- FROM tranche_risk -- LEFT JOIN cds ON (tranche_id=id) -- LEFT JOIN (SELECT as_of_date, link_ref, (CASE WHEN coupon_rate=0 THEN current_notional ELSE -current_notional END) AS admin_notional, base_market_value AS admin_clean_nav FROM bowdst_val WHERE security_description_1 LIKE 'TR%NCH%' AND abs(base_market_value) > 1.0) b ON link_ref=format('SCCDS%s',tranche_id) AND as_of_date=tranche_risk.date -- LEFT JOIN index_version ON (security_id=redindexcode) -- LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.date=tranche_risk.date -- WHERE fund='BOWDST' -- ORDER BY index, series, orig_attach; CREATE OR REPLACE VIEW tranche_risk_bowdst AS SELECT tranche_risk.date, tranche_id as trade_id, b.globeop_id, security_desc, index, series, maturity, orig_attach, orig_detach, tranche_risk.notional, admin_notional, clean_nav as serenitas_clean_nav, admin_clean_nav, accrued as serenitas_accrued, admin_accrued, base_nav AS cpty_nav, duration, delta, gamma, theta, theta_amount, tranche_factor, tranche_risk.corr_attach, tranche_risk.corr_detach, tranche_risk.upfront, tranche_risk.running, index_refprice, index_refspread, index_duration, hy_equiv, initial_margin_percentage FROM tranche_risk LEFT JOIN LATERAL (SELECT globeop_id FROM id_mapping WHERE id_mapping.serenitas_id=tranche_id AND date <= tranche_risk.date ORDER by date desc LIMIT 1) b ON true LEFT JOIN cds ON (tranche_id=id) RIGHT JOIN (SELECT invid, periodenddate, sum(endbookunrealincome) AS admin_accrued, sum(endbooknav-endbookunrealincome) AS admin_clean_nav, sum(endqty) AS admin_notional FROM valuation_reports GROUP BY invid, periodenddate) a ON (NULLIF(substring(invid FROM '\D{3}[0]*(\d*)K00HEEAD'), '')::integer=b.globeop_id AND periodenddate=tranche_risk.date) LEFT JOIN index_version ON (security_id=redindexcode) LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.date=tranche_risk.date WHERE fund='BOWDST' ORDER BY index, series, orig_attach; CREATE OR REPLACE VIEW tranche_risk_brinker AS SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, maturity, orig_attach, orig_detach, tranche_risk.notional, admin_notional, clean_nav as serenitas_clean_nav, admin_clean_nav, accrued as serenitas_accrued, NULL AS admin_accrued, base_nav AS cpty_nav, duration, delta, gamma, theta, theta_amount, tranche_factor, tranche_risk.corr_attach, tranche_risk.corr_detach, tranche_risk.upfront, tranche_risk.running, index_refprice, index_refspread, index_duration, hy_equiv, initial_margin_percentage FROM tranche_risk LEFT JOIN cds ON (tranche_id=id) LEFT JOIN (SELECT accounting_date, security_id, quantity * (CASE WHEN long_short_indicator = 'S' THEN 1. ELSE -1. END) AS admin_notional, (local_market_value - quantity) * fx_rate AS admin_clean_nav FROM bbh_val WHERE sub_security_type_code ='CXT' AND interest_rate IS NOT NULL) b ON b.security_id=format('SCCDS%s', tranche_id) AND accounting_date=tranche_risk.date LEFT JOIN index_version ON (cds.security_id=redindexcode) LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.date=tranche_risk.date WHERE fund='BRINKER' ORDER BY index, series, orig_attach; CREATE OR REPLACE FUNCTION tranche_risk_agg(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(date date, security_desc varchar(32), index index_type, series smallint, admin_notional float, admin_clean_nav float, maturity date, serenitas_notional float, tranche_factor float, running float, serenitas_clean_nav float, serenitas_accrued float, initial_margin float, theta float, theta_amount float, duration float, delta float, gamma float, hy_equiv float, upfront float, index_refprice float, index_refspread float, index_duration float, orig_attach smallint, orig_detach smallint) AS $$ DECLARE query text; fund text; BEGIN IF p_fund = 'SERCGMAST' THEN fund := 'serenitas'; ELSE fund := lower(p_fund::text); END IF; query := 'SELECT date, security_desc, index, series, sum(admin_notional), sum(admin_clean_nav), maturity, sum(notional), avg(tranche_factor), avg(running), sum(serenitas_clean_nav), sum(serenitas_accrued), sum(initial_margin_percentage * tranche_factor * abs(notional)/100), avg(theta), sum(theta_amount), avg(duration), avg(delta), avg(gamma), sum(hy_equiv), avg(upfront), avg(index_refprice), avg(index_refspread), avg(index_duration), orig_attach, orig_detach FROM tranche_risk_%I GROUP BY date, index, security_desc, series, orig_attach, orig_detach, maturity HAVING date=$1 ORDER BY index, series'; query := format(query, fund); RETURN QUERY EXECUTE query USING p_date; END $$ LANGUAGE plpgsql; CREATE OR REPLACE VIEW tranche_risk_master AS SELECT tranche_risk.date, tranche_id AS trade_id, fund, security_desc, index, series, maturity, orig_attach, orig_detach, tranche_risk.notional, clean_nav * coalesce(fx, 1.) as serenitas_clean_nav, accrued * coalesce(fx, 1.) as serenitas_accrued, nav AS cpty_nav, duration, delta, gamma, theta, tranche_factor, tranche_risk.corr_attach, tranche_risk.corr_detach, tranche_risk.upfront, tranche_risk.running, index_refprice, index_refspread, index_duration, initial_margin_percentage, ia as cpty_ia, indexfactor FROM tranche_risk LEFT JOIN cds ON (tranche_id=id) LEFT JOIN index_version ON (security_id=redindexcode) LEFT JOIN (SELECT date, 'EUR'::currency AS currency, eurusd AS fx FROM fx) fx USING (date, currency) LEFT JOIN external_marks_deriv on cpty_id=identifier and external_marks_deriv.date=tranche_risk.date ORDER BY date desc, index, series, orig_attach; CREATE TABLE fcm_im( date date NOT NULL, account text NOT NULL, -- REFERENCES accounts(cash_account) currency currency NOT NULL, amount float NOT NULL, PRIMARY KEY(date, account, currency) ); CREATE TABLE fcm_moneyline( date date NOT NULL, account text NOT NULL, currency currency NOT NULL, beginning_balance float NOT NULL, cds_initial_coupon float NOT NULL, cds_reset_to_par float NOT NULL, pai float NOT NULL, clearing_fees float NOT NULL, transaction_fees float NOT NULL, net_dep_withdraw float NOT NULL, ending_balance float NOT NULL, account_value_market float NOT NULL, realized_pnl float NOT NULL, current_im float NOT NULL, current_excess_deficit float NOT NULL, PRIMARY KEY (date, account, currency) ); CREATE TABLE strategy_im( date date NOT NULL, broker text NOT NULL, strategy strategy NOT NULL, amount float NOT NULL, currency currency NOT NULL, fund fund NOT NULL, PRIMARY KEY (date, strategy, broker, fund) ) CREATE TYPE cash_rate AS ENUM('FED_FUND', '1M_LIBOR', '3M_LIBOR'); CREATE OR REPLACE VIEW swaption_trades AS SELECT swaptions.id, dealid, termination_date AS trade_date, fee_payment_date AS settle_date, termination_amount AS notional, security_desc, security_id, CASE WHEN termination_cp=cp_code THEN 'Termination' ELSE 'Assignment' END AS trade_type, folder, portfolio, fund, termination_cp AS cp_code, name, option_type, expiration_date, strike, NOT buysell AS buysell, -termination_fee AS fee FROM terminations RIGHT JOIN swaptions USING (dealid) LEFT JOIN counterparties ON termination_cp = code WHERE termination_date IS NOT NULL UNION ALL ( SELECT id, dealid, trade_date, settle_date, notional, security_desc, security_id, 'New' AS trade_type, folder, portfolio, fund, cp_code, name, option_type, expiration_date, strike, buysell, notional * price / 100 * (2* buysell::integer -1.) * COALESCE(indexfactor / 100, 1.) FROM swaptions JOIN counterparties ON cp_code = code LEFT JOIN index_version ON security_id = redindexcode) ORDER BY trade_date DESC; CREATE OR REPLACE VIEW cds_trades AS SELECT cds.id, dealid, termination_date AS trade_date, termination_amount AS notional, security_desc, security_id, CASE WHEN termination_cp=cp_code THEN 'Termination' ELSE 'Assignment' END AS trade_type, folder, fund, termination_cp AS cp_code, name, orig_attach, orig_detach, attach, detach, CASE WHEN protection = 'Buyer' THEN 'Seller'::protection ELSE 'Buyer'::protection END AS protection, termination_fee AS upfront, terminations.traded_level FROM terminations RIGHT JOIN cds USING (dealid) LEFT JOIN counterparties on termination_cp=code WHERE termination_date is NOT NULL UNION ALL ( SELECT id, dealid, trade_date, notional, security_desc, security_id, 'New', folder, fund, cp_code, name, orig_attach, orig_detach, attach, detach, protection, upfront, traded_level FROM cds JOIN counterparties ON cp_code = code) ORDER BY trade_date DESC, folder; CREATE TABLE curve_risk( date date NOT NULL, strategy cds_strat, "VaR" float, currency currency, fund fund, PRIMARY KEY (date, strategy, fund)) CREATE OR REPLACE VIEW tranche_pnl AS WITH temp AS ( SELECT date, tranche_id, fund, clean_nav + accrued - lag(clean_nav+accrued, -1, -cds.upfront) over (partition by tranche_id ORDER BY date DESC) AS daily_pnl FROM tranche_risk JOIN cds ON tranche_id=id ) SELECT date, fund, tranche_id, daily_pnl, sum(daily_pnl) OVER (PARTITION BY tranche_id, date_trunc('month', date) ORDER BY date) AS mtd_pnl FROM temp; create table id_mapping( date date not null, trade_type text not null, serenitas_id int not null, globeop_id int not null, PRIMARY KEY (date, trade_type, serenitas_id) ); CREATE TABLE tranche_cashflows( date date NOT NULL, tranche_id integer NOT NULL, principal float, accrued float, currency currency, PRIMARY KEY (date, tranche_id) ); CREATE TABLE beta_crt( date date NOT NULL, strategy bond_strat NOT NULL , beta_crt float, PRIMARY KEY (date, strategy) ); CREATE OR REPLACE VIEW public.payment_settlements AS SELECT trades.settle_date, trades.fund, cps.name, trades.cp_code, trades.asset_class, trades.currency, sum(trades.payment_amount)::numeric(10,2) AS payment_amount, array_agg(trades.id) AS ids FROM ( SELECT bond_trades.id, bond_trades.settle_date, bond_trades.fund, bond_trades.cp_code, 'bond'::text AS asset_class, 'USD'::currency AS currency, CASE WHEN bond_trades.buysell THEN - bond_trades.net_amount WHEN NOT bond_trades.buysell THEN bond_trades.net_amount ELSE NULL::double precision END AS payment_amount FROM bond_trades WHERE bond_trades.tradeid IS NOT NULL UNION SELECT cds_trades.id, cds_trades.settle_date, cds_trades.fund, cds_trades.cp_code, 'tranche'::text AS asset_class, cds_trades.currency, cds_trades.upfront AS payment_amount FROM cds_trades WHERE cds_trades.orig_attach IS NOT NULL UNION SELECT swaptions.id, swaptions.settle_date, swaptions.fund, swaptions.cp_code, 'swaption'::text AS asset_class, swaptions.currency, CASE WHEN swaptions.buysell THEN (- swaptions.notional) * swaptions.price / 100::double precision WHEN NOT swaptions.buysell THEN swaptions.notional * swaptions.price / 100::double precision ELSE NULL::double precision END AS payment_amount FROM swaptions UNION SELECT spots.id, spots.settle_date, spots.fund, spots.account_code AS cp_code, 'spot'::text AS asset_class, unnest(ARRAY[spots.buy_currency, spots.sell_currency]) AS currency, unnest(ARRAY[spots.buy_amount, - spots.sell_amount]) AS amount FROM spots) trades LEFT JOIN ( SELECT DISTINCT ON (cp_code.cp_code) cp_code.cp_code, cp_code.name FROM ( SELECT c.code AS cp_code, c.name FROM counterparties c UNION SELECT accounts.code AS cp_code, accounts.name FROM accounts) cp_code) cps USING (cp_code) GROUP BY trades.settle_date, trades.fund, cps.name, trades.cp_code, trades.asset_class, trades.currency ORDER BY trades.settle_date DESC, trades.fund, cps.name, trades.asset_class, trades.currency; CREATE TABLE public.equities ( id serial4 NOT NULL, dealid varchar(28) NULL GENERATED ALWAYS AS ((('EQ'::text || id::text))) STORED, lastupdate timestamp NULL DEFAULT now(), "action" action NOT NULL, folder future_strat NOT NULL, trade_date date NOT NULL, settle_date date NOT NULL, buysell bool NOT NULL, bbg_ticker varchar(32) NOT NULL, quantity float8 NOT NULL, price float8 NOT NULL, commission float8 NULL, security_desc varchar(32) NOT NULL, "currency" currency NOT NULL, exchange varchar(3) NOT NULL, "fund" fund NOT NULL DEFAULT 'SERCGMAST'::fund, account_code text NOT NULL DEFAULT 'IB'::text, CONSTRAINT equities_dealid_key UNIQUE (dealid), CONSTRAINT equities_pkey PRIMARY KEY (id), CONSTRAINT equities_account_code_fkey FOREIGN KEY (account_code) REFERENCES public.accounts(code) ); CREATE TABLE public.equity_options ( id serial4 NOT NULL, dealid varchar(28) NULL GENERATED ALWAYS AS ((('EQOPT'::text || id::text))) STORED, lastupdate timestamp NULL DEFAULT now(), "action" action NOT NULL, folder future_strat NOT NULL, trade_date date NOT NULL, settle_date date NOT NULL, buysell bool NOT NULL, bbg_ticker varchar(32) NOT NULL, quantity float8 NOT NULL, option_type equity_option_type NOT NULL, price float8 NOT NULL, commission float8 NULL, security_desc varchar(32) NOT NULL, "currency" currency NOT NULL, exchange varchar(3) NOT NULL, "fund" fund NOT NULL DEFAULT 'SERCGMAST'::fund, account_code text NOT NULL DEFAULT 'IB'::text, CONSTRAINT equity_options_dealid_key UNIQUE (dealid), CONSTRAINT equity_options_pkey PRIMARY KEY (id), CONSTRAINT equity_options_account_code_fkey FOREIGN KEY (account_code) REFERENCES public.accounts(code) ); CREATE TABLE public.bond_tickets ( bbg_ticket_id text NOT NULL, "Match" text NULL, "Side" text NULL, "Security" text NULL, "Quantity" int8 NULL, "Price (Dec)" float8 NULL, "CP" text NULL, "STP Status" text NULL, "Trade Dt" text NULL, "SetDt" text NULL, "Curncy" text NULL, "Principal" float8 NULL, "Net" float8 NULL, "Acc Int" float8 NULL, "Block Status" text NULL, "Brkr" text NULL, "BrkrName" text NULL, "BrkrName.1" text NULL, "FIGI" text NULL, "Cusip" text NULL, "Curr Face" float8 NULL, CONSTRAINT bond_tickets_pk PRIMARY KEY (bbg_ticket_id) ); CREATE INDEX ix_bond_tickets_index ON public.bond_tickets USING btree (bbg_ticket_id);