-- -*- 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', 'CASH_BASIS', '*'); 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', 'OIS_SWAP', 'TOTAL_RETURN_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 accounts2 ( id int4 NOT NULL GENERATED ALWAYS AS IDENTITY, "name" text NULL, custodian text NOT NULL, cash_account text NOT NULL, "fund" fund NOT NULL, "account_type" account_type NOT NULL, active bool NOT NULL, CONSTRAINT accounts2_cash_account_key UNIQUE (cash_account), CONSTRAINT accounts2_pkey PRIMARY KEY (id) ); 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 TABLE portfolio_folder_mapping( folder strategy PRIMARY KEY, portfolio portfolio NOT NULL valid boolean NOT NULL); CREATE OR REPLACE TRIGGER portf BEFORE INSERT OR UPDATE OF folder ON bonds FOR EACH ROW EXECUTE PROCEDURE update_portf2(); CREATE OR REPLACE FUNCTION update_portf2() RETURNS TRIGGER AS $$ BEGIN SELECT pfm.portfolio FROM portfolio_folder_mapping pfm INTO NEW.portfolio where folder::text=NEW.folder::text; RETURN NEW; END $$ LANGUAGE plpgsql; CREATE OR REPLACE TRIGGER bond_notify BEFORE INSERT OR UPDATE OF identifier, cusip, isin, faceamount, price, trade_date, settle_date, accrued, asset_class 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, cash_account text NOT NULL REFERENCES accounts2(cash_account), 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 OR REPLACE FUNCTION update_account() RETURNS TRIGGER AS $$ BEGIN SELECT cash_account INTO STRICT NEW.cash_account FROM accounts2 LEFT JOIN account_counterparty ON accounts2.id=account_id WHERE trade_type='ISDA' AND fund=NEW.fund AND account_counterparty.cp_code=NEW.cp_code; RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER cash_account BEFORE INSERT OR UPDATE OF fund, cp_code ON swaptions FOR EACH ROW EXECUTE PROCEDURE update_account(); 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, portfolio portfolio NOT NULL, folder future_strat NOT NULL, cash_account text NOT NULL REFERENCES accounts2(cash_account), 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 TRIGGER portf BEFORE INSERT OR UPDATE OF folder ON futures FOR EACH ROW EXECUTE PROCEDURE update_portf2(); CREATE TABLE wires ( id serial NOT NULL, fund fund NOT NULL, dealid varchar(28) NULL, lastupdate timestamp NULL DEFAULT now(), "action" action NOT NULL, folder cash_strat NOT NULL, portfolio portfolio NOT NULL, cash_account text NOT NULL REFERENCES accounts2(cash_account), amount float8 NOT NULL, currency currency NOT NULL, trade_date date NOT NULL, CONSTRAINT wires_pkey PRIMARY KEY (id), ); ALTER table wires OWNER TO dawn_user; CREATE TRIGGER dealid AFTER INSERT ON wires FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); CREATE TRIGGER portf2 BEFORE INSERT OR UPDATE OF folder ON wires FOR EACH ROW EXECUTE PROCEDURE update_portf2(); 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, portfolio portfolio NOT NULL, folder spot_strat NOT NULL, cash_account text NOT NULL REFERENCES accounts2(cash_account), cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, 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, initial_margin_percentage float8, cpty_id text, globeop_id text, bbg_ticket_id text UNIQUE REFERENCES fx_tickets(bbg_ticket_id), CONSTRAINT spots_pkey PRIMARY KEY (id), ); CREATE TRIGGER fx_portf BEFORE INSERT OR UPDATE OF folder ON wires FOR EACH ROW EXECUTE PROCEDURE update_portf2(); CREATE TABLE fx_swaps ( id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, dealid varchar(28) NOT NULL GENERATED ALWAYS AS ((('FXSWAP_'::text || id::text))) STORED, "fund" fund NOT NULL DEFAULT 'SERCGMAST'::fund, lastupdate timestamp NULL DEFAULT now(), "portfolio" portfolio NOT NULL, folder spot_strat NOT NULL, cash_account text NOT NULL REFERENCES accounts2(cash_account), cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, trade_date date NOT NULL, near_rate float8 NOT NULL, near_settle_date date NOT NULL, near_buy_currency currency NOT NULL, near_buy_amount float8 NOT NULL, near_sell_currency currency NOT NULL, near_sell_amount float8 NOT NULL, far_rate float8 NOT NULL, far_settle_date date NOT NULL, far_buy_currency currency NULL, far_buy_amount float8 NOT NULL, far_sell_currency currency NULL, far_sell_amount float8 NOT NULL, near_cpty_id text NULL, far_cpty_id text NULL, bbg_ticket_id text UNIQUE REFERENCES fx_tickets(bbg_ticket_id), ); CREATE OR REPLACE VIEW forwards AS SELECT UNNEST(ARRAY[dealid || '_near', dealid || '_far']) AS dealid, trade_date, unnest(ARRAY[near_settle_date, far_settle_date]) AS settle_date, fund, portfolio, folder, cp_code, UNNEST(ARRAY[near_buy_currency, far_buy_currency]) AS buy_currency, UNNEST(ARRAY[near_sell_currency, far_sell_currency]) AS sell_currency, UNNEST(ARRAY[near_buy_amount, far_buy_amount]) AS buy_amount, UNNEST(ARRAY[near_sell_amount, far_sell_amount]) AS sell_amount, UNNEST(ARRAY[near_cpty_id, far_cpty_id]) AS cpty_id FROM fx_swaps UNION SELECT dealid, trade_date, settle_date, fund, portfolio, folder, cp_code, buy_currency, sell_currency, buy_amount, sell_amount, cpty_id FROM spots; 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) NOT NULL REFERENCES securities(figi) ON UPDATE CASCADE, date date NOT NULL, 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', 'LIMBO', 'ISOSEL') 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_BASIS', -- 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), invticker varchar(12) NULL, pricelist text NULL, externalid text NULL, 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', 'LCH-LTD'); 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, fund, broker, strategy, currency) ) CREATE TYPE cash_rate AS ENUM('FED_FUND', '1M_LIBOR', '3M_LIBOR', 'SOFR_RATE'); 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.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 UNION SELECT fx_swaps.id, unnest(ARRAY[near_settle_date, near_settle_date, far_settle_date, far_settle_date]) AS settle_date, fx_swaps.fund, fx_swaps.cp_code, 'spot'::text AS asset_class, unnest(ARRAY[near_buy_currency, near_sell_currency, far_buy_currency, far_sell_currency]) AS currency, unnest(ARRAY[near_buy_amount, -near_sell_amount, far_buy_amount, - far_sell_amount]) AS amount FROM fx_swaps ) 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" text 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); CREATE TABLE public.cds_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, "Account" text NOT NULL, "Client FCM" text NOT NULL, "Block Status" text NULL, "Red Code" text NULL, "Cusip" text NULL, "Brkr" text NULL, "Mat Dt" text NULL, "Coupon" int8 NULL, CONSTRAINT cds_tickets_pk PRIMARY KEY (bbg_ticket_id, "Account", "Client FCM") ); CREATE TABLE public.cds_submission ( id int4 NOT NULL GENERATED ALWAYS AS IDENTITY, tradeid int4 NOT NULL, "action" action NOT NULL, submit_date timestamptz NOT NULL DEFAULT now(), "status" status NOT NULL DEFAULT 'Pending'::status, CONSTRAINT cds_submission_pkey PRIMARY KEY (id), CONSTRAINT cds_submission_allocation_id_fkey FOREIGN KEY (tradeid) REFERENCES public.cds(id) ON DELETE CASCADE ); CREATE TABLE public.mtm_submissions ( ticketid text NOT NULL, executed date NULL, dealid text NOT NULL, dealtype text NULL, cp_code text NULL, swaptype text NULL, cpty_ref text NULL, CONSTRAINT mtm_submissions_pk PRIMARY KEY (ticketid, dealid) ); CREATE TABLE public.quantifi_bond_proxy ( product_name text NOT NULL, trade_date date NOT NULL, "asset_class" text NOT NULL, "fund" public."fund" NOT NULL, notional float8 NULL, terminated bool NULL DEFAULT false, CONSTRAINT quantifi_bond_proxy_pk PRIMARY KEY (product_name, trade_date, asset_class, fund) ); CREATE OR REPLACE FUNCTION public.list_compressed_quantifi(start_from date) RETURNS TABLE(index text, tradeid text, fund fund, clean_folder text, portfolio portfolio, security_id varchar(12), notional double precision, fcm text) LANGUAGE plpgsql AS $function$ begin return query SELECT LEFT(split_part(security_desc, ' ', 2),2) AS "index", imm.tradeid, a.fund, pfm.clean_folder, pfm.portfolio, a.security_id, a.notional, a.fcm FROM ( SELECT cds.fund, account_code AS fcm, cds.security_id, max(security_desc) AS security_desc, maturity, folder, SUM(cds.notional * (CASE WHEN protection='Buyer' THEN 1 ELSE -1 END)) AS notional FROM cds WHERE swap_type='CD_INDEX' AND trade_date<=start_from AND maturity > start_from GROUP BY cds.security_id, maturity, cds.folder, cds.fund, account_code) a LEFT JOIN portfolio_folder_mapping pfm ON a.folder::text=pfm.clean_folder LEFT JOIN index_maturity_markit imm ON (a.SECURITY_id, a.maturity) = (imm.redindexcode, imm.maturity) WHERE active AND a.notional !=0; end; $function$ ; CREATE TABLE public.fx_tickets ( bbg_ticket_id text NOT NULL, "Message Type" text NULL, "Deal Type" text NULL, "Side" text NULL, "Product" text NULL, "Trans Type" text NULL, "Revision Version" text NULL, "Trade ID" text NULL, "Batch ID" text NULL, "Trader Deal Code" text NULL, "Trader UUID" text NULL, "Trader Name" text NULL, "Counterparty UUID" text NULL, "Counterparty Trader Name" text NULL, "Date Of Deal" text NULL, "Time Of Deal" text NULL, "Trade Date" text NULL, "Date Confirmed" text NULL, "Time Confirmed" text NULL, "Counterparty Deal Code" text NULL, "Counterparty Name" text NULL, "User Identifier 1" text NULL, "User Identifier 2" text NULL, "User Identifier 3" text NULL, "User Identifier 4" text NULL, "Currency 1" text NULL, "Currency 2" text NULL, "Amount Dealt" text NULL, "Dealt Currency" text NULL, "Counter Amount" text NULL, "Counter Currency" text NULL, "Forward Points Near" text NULL, "Far Amount Dealt" text NULL, "Far Currency Dealt" text NULL, "Far Counter Amount" text NULL, "Far Counter Currency" text NULL, "Forward Points Far" text NULL, "Spot Basis Rate" text NULL, "Deposit Rate" text NULL, "Day Count Type" text NULL, "New or Roll" text NULL, "Volume Of Interest" text NULL, "Exchange Rate Period 1" text NULL, "Value Date Period 1 Currency 1" text NULL, "Tenor Period 1" text NULL, "Fixing Date Period 1" text NULL, "Fixing Source Period 1" text NULL, "Settle Currency" text NULL, "Swap Rate" text NULL, "Exchange Rate Period 2" text NULL, "Value Date Period 2 Currency 1" text NULL, "Tenor Period 2" text NULL, "Fixing Date Period 2" text NULL, "Fixing Source Period 2" text NULL, "Split Tenor Currency 1" text NULL, "Split Value Date Currency 1" text NULL, "Split Tenor Currency 2" text NULL, "Split Value Date Currency 2" text NULL, "Comment Text" text NULL, "Note Name 1" text NULL, "Note Text 1" text NULL, "Note Name 2" text NULL, "Note Text 2" text NULL, "Note Name 3" text NULL, "Note Text 3" text NULL, "Note Name 4" text NULL, "Note Text 4" text NULL, "Note Name 5" text NULL, "Note Text 5" text NULL, "Comp Quote 1" text NULL, "Comp Deal Code 1" text NULL, "Supplementary Cost 1" text NULL, "Comp Quote 2" text NULL, "Comp Deal Code 2" text NULL, "Supplementary Cost 2" text NULL, "Comp Quote 3" text NULL, "Comp Deal Code 3" text NULL, "Supplementary Cost 3" text NULL, "Comp Quote 4" text NULL, "Comp Deal Code 4" text NULL, "Supplementary Cost 4" text NULL, "Comp Quote 5" text NULL, "Comp Deal Code 5" text NULL, "Supplementary Cost 5" text NULL, "Buyer Near Trader UUID" text NULL, "Buyer Near Deal Code" text NULL, "Seller Near Trader UUID" text NULL, "Seller Near Deal Code" text NULL, "Buyer Far Trader UUID" text NULL, "Buyer Far Deal Code" text NULL, "Seller Far Trader UUID" text NULL, "Seller Far Deal Code" text NULL, "Broker Trader UUID" text NULL, "Broker Deal Code" text NULL, "Broker Name" text NULL, "Portfolio" text NULL, "ALOC Account 1" text NULL, "ALOC Amount 1" text NULL, "ALOC Direction 1" text NULL, "ALOC Custodian 1" text NULL, "ALOC Prime Broker 1" text NULL, "ALOC Account 2" text NULL, "ALOC Amount 2" text NULL, "ALOC Direction 2" text NULL, "ALOC Custodian 2" text NULL, "ALOC Prime Broker 2" text NULL, "ALOC Account 3" text NULL, "ALOC Amount 3" text NULL, "ALOC Direction 3" text NULL, "ALOC Custodian 3" text NULL, "ALOC Prime Broker 3" text NULL, "ALOC Account 4" text NULL, "ALOC Amount 4" text NULL, "ALOC Direction 4" text NULL, "ALOC Custodian 4" text NULL, "ALOC Prime Broker 4" text NULL, "ALOC Account 5" text NULL, "ALOC Amount 5" text NULL, "ALOC Direction 5" text NULL, "ALOC Custodian 5" text NULL, "ALOC Prime Broker 5" text NULL, "ALOC Account 6" text NULL, "ALOC Amount 6" text NULL, "ALOC Direction 6" text NULL, "ALOC Custodian 6" text NULL, "ALOC Prime Broker 6" text NULL, "ALOC Account 7" text NULL, "ALOC Amount 7" text NULL, "ALOC Direction 7" text NULL, "ALOC Custodian 7" text NULL, "ALOC Prime Broker 7" text NULL, "ALOC Account 8" text NULL, "ALOC Amount 8" text NULL, "ALOC Direction 8" text NULL, "ALOC Custodian 8" text NULL, "ALOC Prime Broker 8" text NULL, "ALOC Account 9" text NULL, "ALOC Amount 9" text NULL, "ALOC Direction 9" text NULL, "ALOC Custodian 9" text NULL, "ALOC Prime Broker 9" text NULL, "ALOC Account 10" text NULL, "ALOC Amount 10" text NULL, "ALOC Direction 10" text NULL, "ALOC Custodian 10" text NULL, "ALOC Prime Broker 10" text NULL, "Reference Spot Rate" text NULL, "Reference Rate Period 1" text NULL, "Reference Rate Period 2" text NULL, "Pay Currency" text NULL, "Pay SWIFT Code" text NULL, "Pay Account Number" text NULL, "Pay Bank" text NULL, "Pay Branch" text NULL, "Pay Beneficiary" text NULL, "Pay Special Instructions" text NULL, "Receiving Currency" text NULL, "Receiving SWIFT Code" text NULL, "Receiving Account Number" text NULL, "Receiving Bank" text NULL, "Receiving Branch" text NULL, "Receiving Beneficiary" text NULL, "Receiving Special Instructions" text NULL, "Spot Rate Mid" text NULL, "All-in Rate Near Leg Mid" text NULL, "Near Leg Forward Point Mid" text NULL, "All-in Rate Far Leg Mid" text NULL, "Far Leg Forward Point Mid" text NULL, "USI Namespace" text NULL, "USI ID" text NULL, "USI ID Near" text NULL, "USI ID Swap" text NULL, "Delivery Date" text NULL, "Banknote Rate Type" text NULL, "Commission" text NULL, "Order Type Name" text NULL, "Order Strategy Name" text NULL, "Execution Venue MIC" text NULL, "Market Segment ID" text NULL, "Group ID" text NULL, "Group Type" text NULL, "Trade Method" text NULL, "Execution Method" text NULL, "Sub Type" text NULL, "Supplementary Sub Type" text NULL, "External Reference ID" text NULL, "Execution Venue LEI" text NULL, "ISIN Near" text NULL, "ISIN Far" text NULL, "ISIN Swap" text NULL, "Execution within Firm Algo ID" text NULL, "Execution within Firm GPI" text NULL, "Execution within Firm Identifier" text NULL, "Investment Decision within Firm Algo ID" text NULL, "Investment Decision within Firm GPI" text NULL, "Investment Decision within Firm Short Code" text NULL, "Trading Capacity" text NULL, "Package ID" text NULL, "Pre-Trade Transparency Type" text NULL, "Pre-Trade Transparency Reason" text NULL, "Post-Trade Transparency Type" text NULL, "Post-Trade Transparency Reason" text NULL, "Commodities Derivative Indicator" text NULL, "Securities Financing Transaction" text NULL, "Maker Algo ID" text NULL, "Ord Reg Timestamp" text NULL, "Ord Reg Timestamp Type" text NULL, "Trd Reg Timestamp" text NULL, "Trd Reg Timestamp Type" text NULL, "Trade Reporting Indicator" text NULL, "Country of Membership" text NULL, CONSTRAINT fx_tickets_pk PRIMARY KEY (bbg_ticket_id) ); CREATE TABLE irs ( id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, dealid text NULL GENERATED ALWAYS AS ('IRS_'::text || id::text) STORED, "fund" "fund" NOT NULL DEFAULT 'SERCGMAST'::fund, "portfolio" "portfolio" NOT NULL, folder "cds_strat" NOT NULL, cash_account text NOT NULL REFERENCES accounts2(cash_account) ON UPDATE CASCADE, cp_code varchar(12) NOT NULL, trade_date date NOT NULL, effective_date date NOT NULL, maturity_date date NOT NULL, payreceive bool NOT NULL, fixed_rate float8 NOT NULL, fixed_daycount day_count NOT NULL, fixed_payment_freq frequency NOT NULL, fixed_bdc bus_day_convention NOT NULL, notional float8 NOT NULL, float_index cash_rate NOT NULL, float_daycount day_count NOT NULL, float_payment_freq frequency NOT NULL, float_bdc bus_day_convention NOT NULL, float_arrears bool NOT NULL, "currency" "currency" NOT NULL, float_fixing_freq frequency NOT NULL, pay_interest_calc_method interest_calc_method NOT NULL, clearing_facility clearing_cp NOT NULL DEFAULT 'LCH-LTD'::clearing_cp, swap_type swap_type NOT NULL, cleared_trade_id text NULL, ); CREATE TYPE "bbg_code_type" AS ENUM ( 'BOND', 'CDS', 'FX'); CREATE TYPE "interest_calc_method" AS ENUM ( 'Compound', 'Simple'); CREATE TABLE trs ( id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, dealid text NULL GENERATED ALWAYS AS ('TRS'::text || id::text) STORED, "fund" "fund" NOT NULL DEFAULT 'SERCGMAST'::fund, "portfolio" "portfolio" NOT NULL, folder "cds_strat" NOT NULL, cash_account text NOT NULL REFERENCES accounts2(cash_account) ON UPDATE CASCADE, cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, trade_date date NOT NULL, effective_date date NOT NULL, maturity_date date NOT NULL, settle_date date NOT NULL, funding_index cash_rate NOT NULL DEFAULT 'SOFRRATE'::cash_rate, buysell bool NOT NULL, underlying_security varchar(32) NOT NULL, price float8 NOT NULL, accrued float8 NOT NULL, funding_freq frequency NOT NULL, funding_daycount day_count NOT NULL, funding_payment_roll_convention bus_day_convention NOT NULL, funding_arrears bool NOT NULL, asset_freq frequency NOT NULL, asset_daycount day_count NOT NULL, asset_payment_roll_convention bus_day_convention NOT NULL, initial_margin_percentage float8 NOT NULL, notional float8 NOT NULL, "currency" "currency" NOT NULL DEFAULT 'USD'::currency, interest_calc_method interest_calc_method NOT NULL, compound_avg_frequency frequency NOT NULL, fixing_frequency frequency NOT NULL, cpty_id text NULL ); CREATE TABLE public.strat_cash_realloc ( id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, dealid text NULL GENERATED ALWAYS AS ('STRATCSH'::text || id::text) STORED, "portfolio" public."portfolio" NULL, folder public."strategy_dirty" NOT NULL, trade_date date NOT NULL, amount float8 NOT NULL, "currency" public."currency" NOT NULL, "fund" public."fund" NOT NULL DEFAULT 'BOWDST'::fund, cash_account text NOT NULL, CONSTRAINT strat_cash_realloc_un UNIQUE (folder, trade_date, amount, currency, fund, cash_account) ); CREATE TYPE "instrument_type" AS ENUM ( 'IRS', 'CDS', 'BNDO', 'SWPO', 'TRS'); CREATE TYPE "id_source" AS ENUM ( 'RED', 'USERID'); CREATE TYPE "serenitas_product" AS ENUM( 'tranche', 'cdx_swaption', 'ir_swaption', 'irs', 'trs' ) CREATE TABLE public.citco_tranche ( id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY, dealid text NULL GENERATED ALWAYS AS ('CDS_'::text || id::text) STORED, instrument_type public.instrument_type NOT NULL DEFAULT 'CDS'::instrument_type, underlying_id_source public.id_source NOT NULL DEFAULT 'RED'::id_source, underlying_security_id text NOT NULL, birth_date date NULL, death_date date NULL, active bool NULL DEFAULT true, "attach" int8 NOT NULL, "detach" int8 NOT NULL, "committed" bool NOT NULL DEFAULT false, CONSTRAINT citco_tranche_pkey PRIMARY KEY (id), CONSTRAINT citco_tranche_un UNIQUE (underlying_security_id, attach, detach) ); CREATE TABLE public.citco_swaption ( id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY, dealid varchar(28) NULL GENERATED ALWAYS AS (((( CASE WHEN instrument_type = 'SWPO'::instrument_type THEN 'SWPO'::text WHEN instrument_type = 'BNDO'::instrument_type THEN 'BNDO'::text ELSE NULL::text END) || id::text))) STORED, instrument_type public.instrument_type NOT NULL, underlying_id_source public.id_source NOT NULL, underlying_security_id text NOT NULL, birth_date date NOT NULL, death_date date NOT NULL, active bool NOT NULL DEFAULT true, strike float8 NOT NULL, expiration date NOT NULL, callput bool NULL, commited bool NOT NULL DEFAULT false, CONSTRAINT citco_swaption_pkey PRIMARY KEY (id), CONSTRAINT citco_swaption_un UNIQUE (instrument_type, underlying_security_id, strike, expiration, callput) ); CREATE TABLE public.citco_irs ( id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, dealid text NULL GENERATED ALWAYS AS ('IRS'::text || id::text) STORED, instrument_type "instrument_type" NOT NULL default 'IRS', birth_date date NOT NULL, death_date date NOT NULL, active bool NOT NULL default True, payreceive bool NOT NULL, fixed_rate float8 NOT NULL, fixed_daycount public."day_count" NOT NULL, fixed_payment_freq public."frequency" NOT NULL, fixed_bdc public."bus_day_convention" NOT NULL, notional float8 NOT NULL, float_index public."cash_rate" NOT NULL, float_daycount public."day_count" NOT NULL, float_payment_freq public."frequency" NOT NULL, float_bdc public."bus_day_convention" NOT NULL, float_arrears bool NOT NULL, "currency" public."currency" NOT NULL, float_fixing_freq public."frequency" NOT NULL, pay_interest_calc_method public."interest_calc_method" NOT NULL, commited bool NOT NULL default False, ); CREATE TABLE public.citco_trs ( id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, dealid text NULL GENERATED ALWAYS AS ('TRS'::text || id::text) STORED, instrument_type "instrument_type" NOT NULL default 'TRS', birth_date date NOT NULL, death_date date NOT NULL, active bool NOT NULL default True, funding_index public."cash_rate" NOT NULL DEFAULT 'SOFRRATE'::cash_rate, buysell bool NOT NULL, underlying_security varchar(32) NOT NULL, price numeric(9, 5) NOT NULL, accrued float8 NOT NULL, funding_freq public."frequency" NOT NULL, funding_daycount public."day_count" NULL, funding_payment_roll_convention public."bus_day_convention" NOT NULL, funding_arrears bool NOT NULL, asset_freq public."frequency" NOT NULL, asset_daycount public."day_count" NULL, asset_payment_roll_convention public."bus_day_convention" NOT NULL, initial_margin_percentage float8 NOT NULL, notional float8 NOT NULL, "currency" public."currency" NOT NULL DEFAULT 'USD'::currency, "interest_calc_method" public."interest_calc_method" NOT NULL, compound_avg_frequency public."frequency" NOT NULL, fixing_frequency public."frequency" NOT NULL, commited bool NOT NULL default False ); CREATE TABLE public.citco_mapping ( "enum" text NOT NULL, "mapping" text NOT NULL, CONSTRAINT citco_mapping_un UNIQUE (enum, mapping) ); CREATE TABLE public.qf_bespokes ( "date" date NOT NULL, "row" int8 NOT NULL, tradeid text NULL, bookingentity_name text NULL, productdescription text NULL, maturity text NULL, trancheattachment float8 NULL, tranchedetachment float8 NULL, pv float8 NULL, riskyduration float8 NULL, apcorrelation float8 NULL, dpcorrelation float8 NULL, breakevenpremium float8 NULL, basecorrelationleveldelta float8 NULL, CONSTRAINT qf_bespokes_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_bondbetas ( "date" date NOT NULL, "row" int8 NOT NULL, productname text NULL, et_duration float8 NULL, et_beta1 float8 NULL, et_beta2 float8 NULL, CONSTRAINT qf_bondbetas_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_bonds ( "date" date NOT NULL, "row" int8 NOT NULL, productname text NULL, bookingentity_name text NULL, cusip text NULL, "strategy" text NULL, quantity int8 NULL, effectivenotional float8 NULL, bondcoupon float8 NULL, producttype text NULL, pv float8 NULL, price float8 NULL, accrued float8 NULL, duration float8 NULL, beta1 float8 NULL, beta2 float8 NULL, CONSTRAINT qf_bonds_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_cdx ( "date" date NOT NULL, "row" int8 NOT NULL, productname text NULL, productdescription text NULL, bookingentity_name text NULL, "strategy" text NULL, creditindexred text NULL, subfamily text NULL, currentnotional float8 NULL, riskyduration float8 NULL, maturity text NULL, beta float8 NULL, fxratequote float8 NULL, cs01 float8 NULL, cs10 float8 NULL, cs50 float8 NULL, CONSTRAINT qf_cdx_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_cdxoption ( "date" date NOT NULL, "row" int8 NOT NULL, productname text NULL, productdescription text NULL, bookingentity_name text NULL, "strategy" text NULL, currentnotional int8 NULL, cdxoptiontype text NULL, cdxoptionstrike float8 NULL, expiration text NULL, delta float8 NULL, cs01 float8 NULL, vega float8 NULL, pv float8 NULL, priceinbps float8 NULL, subfamily text NULL, tradetag_markit1 text NULL, beta float8 NULL, fxratequote int8 NULL, CONSTRAINT qf_cdxoption_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_cdxqf ( "date" date NOT NULL, "row" int8 NOT NULL, tradeid text NULL, productname text NULL, productdescription text NULL, bookingentity_name text NULL, "strategy" text NULL, creditindexred text NULL, subfamily text NULL, currentnotional float8 NULL, riskyduration float8 NULL, maturity text NULL, beta float8 NULL, fxratequote float8 NULL, cs01 float8 NULL, cs10 float8 NULL, cs50 float8 NULL, tradedate text NULL, substrategy text NULL, broker_longname text NULL, CONSTRAINT qf_cdxqf_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_cdxtranche ( "date" date NOT NULL, "row" int8 NOT NULL, productname text NULL, productdescription text NULL, bookingentity_name text NULL, "strategy" text NULL, currentnotional int8 NULL, effectivenotional float8 NULL, delta float8 NULL, cs01 float8 NULL, pvbaseccy float8 NULL, pvlocalccy float8 NULL, accrued float8 NULL, cleanprice float8 NULL, tradetag_markit1 text NULL, subfamily text NULL, fxratequote float8 NULL, beta float8 NULL, CONSTRAINT qf_cdxtranche_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_fxforwards ( "date" date NOT NULL, "row" int8 NOT NULL, tradeid text NULL, bookingentity_name text NULL, buycurrency text NULL, buyamount float8 NULL, sellcurrency text NULL, sellamount float8 NULL, pv int8 NULL, counterparty_longname text NULL, CONSTRAINT qf_fxforwards_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_hvardata ( "date" date NOT NULL, "row" text NOT NULL, tradeid text NULL, shiftdate text NULL, bookingentity_longname text NULL, "trade simulated loss" text NULL, substrategy text NULL, "strategy" text NULL, CONSTRAINT qf_hvardata_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_hvardatacdxoptions ( "date" date NOT NULL, "row" int8 NOT NULL, tradeid text NULL, shiftdate text NULL, bookingentity_longname text NULL, tradesimulatedloss float8 NULL, substrategy text NULL, "strategy" text NULL, CONSTRAINT qf_hvardatacdxoptions_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_indicatives ( "date" date NOT NULL, "row" int8 NOT NULL, productname text NULL, "strategy" text NULL, productdescription text NULL, quantity int8 NULL, et_beta1 float8 NULL, riskyduration float8 NULL, pv float8 NULL, impliedquotedspr float8 NULL, subfamily text NULL, CONSTRAINT qf_indicatives_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_jtd ( "date" date NOT NULL, "row" int8 NOT NULL, referenceentity_longname text NULL, bookingentity_name text NULL, jtddelta float8 NULL, jtddelta_0 float8 NULL, jtddelta_100 float8 NULL, cdsspreadquote5y float8 NULL, CONSTRAINT qf_jtd_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_pnlexplainbytrade ( "date" date NOT NULL, "row" int8 NOT NULL, tradeid text NULL, bookingentity_name text NULL, "strategy" text NULL, substrategy text NULL, producttype text NULL, pnlusd float8 NULL, newbusiness float8 NULL, termination_effect int8 NULL, basecorrelationhigherorder float8 NULL, basecorrelationdelta float8 NULL, parspreadsdelta float8 NULL, parspreadsgamma float8 NULL, parspreadshigherorder float8 NULL, indexbasisdelta float8 NULL, indexbasishigherorder float8 NULL, creditcorrelationcrosseffect float8 NULL, creditindexvolcrosseffect float8 NULL, recoveryratedelta float8 NULL, recoveryhigherorder float8 NULL, creditdefaulteffect int8 NULL, volatilitydelta float8 NULL, volatilityhigherorder float8 NULL, accrualinterest float8 NULL, timeeffecttotal float8 NULL, irdelta float8 NULL, irgamma float8 NULL, irhigherorder float8 NULL, equityvega int8 NULL, equityspotdelta int8 NULL, equityspotgamma int8 NULL, equitymodeleffectinho float8 NULL, equityspothighorder int8 NULL, modelbasistotal float8 NULL, mtmfxdelta float8 NULL, mtmfxhighorder int8 NULL, cashfxeffect float8 NULL, nonactivetrade_cashrevaleffect float8 NULL, mtmpladjfxreval float8 NULL, unexplainedpl int8 NULL, totalexplainedpl float8 NULL, residualeffects float8 NULL, absresidual float8 NULL, basecorrelationhigherorder1 text NULL, CONSTRAINT qf_pnlexplainbytrade_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_proxies ( "date" date NOT NULL, "row" int8 NOT NULL, tradeid text NULL, bookingentity_name text NULL, productdescription text NULL, "strategy" text NULL, substrategy text NULL, quantity int8 NULL, pv float8 NULL, riskyduration float8 NULL, cs01 float8 NULL, CONSTRAINT qf_proxies_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_ratefutures ( "date" date NOT NULL, "row" text NOT NULL, bookingentity_name text NULL, productdescription text NULL, maturity text NULL, quantity text NULL, futurescontracttype text NULL, price text NULL, pv text NULL, CONSTRAINT qf_ratefutures_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_sensitivities ( "date" date NOT NULL, "row" int8 NOT NULL, tradeid text NULL, bookingentity_name text NULL, "strategy" text NULL, substrategy text NULL, producttype text NULL, subfamily text NULL, cs01 float8 NULL, cs10 float8 NULL, cs20 float8 NULL, cs30 float8 NULL, cs40 float8 NULL, cs50 float8 NULL, cs60 float8 NULL, cs70 float8 NULL, cs80 float8 NULL, cs90 float8 NULL, cs100 float8 NULL, beta float8 NULL, currentnotional float8 NULL, cs300 float8 NULL, cs1000 float8 NULL, CONSTRAINT qf_sensitivities_pk PRIMARY KEY (date, "row") ); CREATE TABLE public.qf_swaptions ( "date" date NOT NULL, "row" int8 NOT NULL, tradeid text NULL, productdescription text NULL, bookingentity_name text NULL, quantity int8 NULL, producttype text NULL, pv float8 NULL, expiration text NULL, dv01 float8 NULL, CONSTRAINT qf_swaptions_pk PRIMARY KEY (date, "row") );