aboutsummaryrefslogtreecommitdiffstats
path: root/sql/et_tables.sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql/et_tables.sql')
-rw-r--r--sql/et_tables.sql367
1 files changed, 367 insertions, 0 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql
new file mode 100644
index 00000000..797468cf
--- /dev/null
+++ b/sql/et_tables.sql
@@ -0,0 +1,367 @@
+CREATE TYPE intex_fixedorfloat AS ENUM('FIXED', 'FLOAT');
+
+CREATE TABLE et_collateral (
+ dealname varchar(8) NOT NULL,
+ updatedate date,
+ Name text,
+ IssuerName text,
+ CurrentBalance float,
+ Maturity date,
+ AssetSubtype varchar(10),
+ AssetType varchar(25),
+ GrossCoupon float default NULL,
+ Spread float default NULL,
+ Frequency varchar(1),
+ NextPaydate date,
+ SecondLien boolean,
+ LoanXID varchar(10),
+ ET_LoanXID varchar(10) default NULL,
+ Cusip varchar(9),
+ ET_Cusip varchar(10) default NULL,
+ IntexPrice float,
+ IntexPriceSource text,
+ IntexPriceDate date,
+ UserPrice float default NULL,
+ UserPriceDate date default NULL,
+ FixedOrFloat intex_fixedorfloat,
+ DefaultedFlag boolean,
+ CovLite text,
+ isCDO boolean,
+ PRIMARY KEY (updatedate, Name, dealname)
+);
+CREATE INDEX dealname_idx ON et_collateral(dealname, updatedate);
+
+ALTER TABLE et_collateral OWNER TO et_user;
+
+CREATE TABLE cusip_user_mapping (
+ IssuerName text,
+ Maturity date,
+ GrossCoupon float default NULL,
+ Spread float default NULL,
+ LoanXID varchar(8) default NULL,
+ Cusip varchar(9) default NULL
+);
+
+CREATE TABLE loanx_user_mapping (
+ IssuerName text,
+ Maturity date,
+ Spread float default NULL,
+ LoanxID varchar(8) default NULL
+);
+
+ALTER TABLE cusip_user_mapping OWNER TO et_user;
+ALTER TABLE loanx_user_mapping OWNER TO et_user;
+
+CREATE TABLE markit_prices (
+ LoanXID varchar(8),
+ Issuer text,
+ DealName text,
+ Facility text,
+ Industry text,
+ SP varchar(4),
+ Moodys varchar(4),
+ Amount float,
+ Maturity date,
+ Spread float,
+ Bid float,
+ Offer float,
+ Depth int,
+ STM float,
+ PricingDate date,
+ PRIMARY KEY (LoanXID, PricingDate)
+);
+
+ALTER TABLE markit_prices OWNER TO et_user;
+
+CREATE TABLE markit_facility (
+ LoanXID varchar(8),
+ PMDID float(10),
+ IssuerName varchar(100),
+ dealname varchar(60),
+ facility_type varchar(40),
+ loanx_facility_type varchar(50),
+ industry varchar(50),
+ initial_amount float(15),
+ initial_spread float(7),
+ maturity date,
+ created_time date,
+ modified_time date
+);
+
+
+CREATE TYPE bloomberg_source AS ENUM('TRAC', 'BGN', 'MSG1', 'BVAL', 'EXCH', 'BCMP', 'LCPR', 'BFV');
+
+CREATE TYPE bloomberg_cpntype AS ENUM('FIXED', 'FLOATING', 'EXCHANGED', 'DEFAULTED',
+'PAY-IN-KIND', 'VARIABLE', 'ZERO COUPON', 'STEP CPN', 'FLAT TRADING', 'NONE', 'FUNGED',
+'STEP', 'ZERO');
+
+CREATE TABLE bloomberg_corp (
+ Cusip varchar(9),
+ Price float,
+ PricingDate date,
+ Issuer text,
+ Maturity date,
+ Coupon float,
+ CouponType bloomberg_cpntype,
+ Frequency int,
+ Spread float,
+ Libor_floor float,
+ loan_margin float,
+ covlite boolean,
+ secondlien boolean,
+ defaulted boolean,
+ Source bloomberg_source,
+ PRIMARY KEY (Cusip, PricingDate)
+);
+
+GRANT ALL ON bloomberg_corp TO et_user;
+
+CREATE TABLE bloomberg_mtge (
+ Cusip varchar(9),
+ Issuer text,
+ Maturity date,
+ Coupon float,
+ CouponType bloomberg_cpntype,
+ Frequency int,
+ Spread float,
+ Moody text,
+ InitialMoody text,
+ PRIMARY KEY (CUSIP)
+);
+
+GRANT ALL ON bloomberg_mtge TO et_user;
+
+CREATE TABLE clo_universe (
+ dealname varchar(10),
+ "Deal Name" text,
+ Manager text,
+ "Orig Deal Bal" float,
+ "Curr Deal Bal" float,
+ "Orig Collat Bal" float,
+ "Curr Collat Bal" float,
+ "Tranche Factor" float,
+ "Principal Bal" float,
+ "Interest Bal" float,
+ "CDO Percentage" float,
+ "Defaulted Bal" float,
+ "Curr Coupon" float,
+ "Deal Issue Date" date,
+ Maturity date,
+ "Deal Next Pay Date" date,
+ "Reinv End Date" date,
+ "Latest Update" date,
+ "Deal Cusip List" text,
+ PaidDown date,
+ PRIMARY KEY (dealname, "Latest Update")
+);
+
+GRANT ALL ON clo_universe TO et_user;
+
+CREATE TABLE cusip_universe (
+ Cusip varchar(9),
+ ISIN varchar(12),
+ dealname varchar(10),
+ tranche text,
+ Coupon float,
+ Orig_Balance float,
+ Curr_Balance float,
+ Factor float,
+ Orig_Moody text,
+ Curr_Moody text,
+ Orig_Attach float,
+ Orig_Detach float,
+ Curr_Attach float,
+ Curr_Detach float,
+ Floater_Index text,
+ Spread float,
+ PRIMARY KEY(Cusip)
+);
+
+GRANT ALL ON cusip_universe TO et_user;
+
+CREATE VIEW latest_clo_universe AS
+ SELECT b.*
+ FROM (SELECT MAX("Latest Update") AS latestdate, dealname FROM clo_universe GROUP BY dealname) a
+ JOIN clo_universe b ON a.dealname = b.dealname AND a.latestdate= b."Latest Update"
+ ORDER by dealname asc;
+
+GRANT ALL ON latest_clo_universe TO et_user;
+
+CREATE TABLE dealcusipmapping (
+ dealname varchar(10),
+ Cusip varchar(9),
+ PRIMARY KEY(Cusip)
+);
+
+GRANT ALL ON dealcusipmapping TO et_user;
+
+CREATE VIEW latest_markit_prices AS
+ SELECT b.*
+ FROM (SELECT MAX(pricingdate) AS latestdate, loanxid FROM markit_prices GROUP BY loanxid) a
+ JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate;
+
+GRANT ALL ON latest_markit_prices TO et_user;
+
+CREATE OR REPLACE FUNCTION et_historical_collateral(p_date date)
+ RETURNS TABLE(dealname varchar(8), updatedate date, name text, issuername text,
+ currentbalance float, maturity date, assetsubtype varchar(10), assettype varchar(25),
+ grosscoupon float, spread float, frequency varchar(1), nextpaydate date,
+ secondlien boolean, loanxid varchar(10), et_loanxid varchar(10), cusip varchar(9),
+ et_cusip varchar(10), intexprice float, intexpricesource text, intexpricedate date,
+ userprice float, userparicedate date, fixedorfloat intex_fixedorfloat,
+ defaultedflag boolean, covlite text, iscdo boolean) AS $$
+ DECLARE latestdate date;
+ BEGIN
+ RETURN QUERY SELECT a.* FROM (SELECT max(et_collateral.updatedate) AS latestdate,et_collateral.dealname
+ FROM et_collateral GROUP BY et_collateral.dealname) b
+ JOIN et_collateral a ON a.dealname=b.dealname AND a.updatedate=b.latestdate;
+ END;
+ $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION best_price (price1 float, pricedt1 date, price2 float, pricedt2 date)
+ RETURNS float
+AS $$
+ if price1 is None:
+ return price2
+ if price2 is None:
+ return price1
+ return price1 if pricedt1> pricedt2 else price2
+$$ LANGUAGE plpythonu;
+
+CREATE OR REPLACE FUNCTION et_aggdealinfo (p_dealname varchar(10))
+ RETURNS TABLE(issuername text, currentbalance float, maturity date,
+ fixedorfloat intex_fixedorfloat, assettype text, price float, grosscoupon float, spread float,
+ frequency varchar(1), nextpaydate date, loanxid varchar(10), cusip varchar(9),
+ covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean) AS $$
+ BEGIN
+ RETURN QUERY SELECT a.issuername, sum(a.currentbalance) AS
+ currentbalance, a.maturity, a.fixedorfloat, max(a.assettype),
+ avg(coalesce((b.bid+b.offer)/2, c.price)) AS price,
+ avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS
+ spread, a.frequency, min(a.nextpaydate) AS nextpaydate,
+ a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or
+ bool_or(c.covlite))) AS covlite, (bool_or(a.secondlien) OR
+ bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo,
+ bool_or(a.defaultedflag) AS defaultedflag from et_collateral a
+ left join latest_markit_prices b on a.loanxid=b.loanxid left
+ join bloomberg_corp c on a.cusip=c.cusip where a.dealname = p_dealname
+ group by a.issuername, a.maturity, a.loanxid, a.fixedorfloat,
+ a.frequency, a.cusip ORDER BY issuername;
+ END;
+ $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION et_latestdealinfo (p_dealname varchar(10), p_date date)
+ RETURNS TABLE(dealname varchar(8), updatedate date, name text, issuername text,
+ currentbalance float, maturity date, assetsubtype varchar(10), assettype varchar(25),
+ grosscoupon float, spread float, frequency varchar(1), nextpaydate date,
+ secondlien boolean, loanxid varchar(10), et_loanxid varchar(10), cusip varchar(9),
+ et_cusip varchar(10), intexprice float, intexpricesource text, intexpricedate date,
+ userprice float, userparicedate date, fixedorfloat intex_fixedorfloat,
+ defaultedflag boolean, covlite text, iscdo boolean) AS $$
+ DECLARE latestdate date;
+ BEGIN
+ SELECT max(et_collateral.updatedate) INTO latestdate FROM et_collateral
+ WHERE et_collateral.dealname = p_dealname
+ AND et_collateral.updatedate <= p_date;
+ RETURN QUERY SELECT a.* FROM et_collateral a WHERE a.dealname = p_dealname AND
+ a.updatedate=latestdate ORDER BY lower(a.issuername);
+ END;
+ $$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION et_ReadMapped(p_issuername text, p_maturity date, p_spread float)
+ RETURNS TABLE(loanxid varchar(10), cusip varchar(9)) AS $$
+ BEGIN
+ RETURN QUERY SELECT loanxid, cusip from et_user_mapping a where a.issuername = p_issuername and a.maturity = p_maturity and a.spread = p_spread;
+ END;
+ $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION dealname_from_cusip(p_cusip varchar(9), OUT p_dealname varchar(8)) AS $$
+ BEGIN
+ SELECT dealname INTO p_dealname from dealcusipmapping where cusip=p_cusip;
+ END;
+ $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION historical_markit_prices(p_date date)
+ RETURNS TABLE(loanxid varchar(8), issuer text, dealname text, facility text,
+ industry text, sp varchar(4), moodys varchar(4), amount float, maturity date,
+ spread float, bid float, offer float, depth integer, stm float, pricingdate date) AS $$
+ BEGIN
+ RETURN QUERY SELECT b.loanxid, b.issuer, b.dealname, b.facility, b.industry, b.sp,
+ b.moodys, b.amount, b.maturity, b.spread, b.bid, b.offer, b.depth, b.stm,
+ a.latestdate
+ FROM (SELECT MAX(c.pricingdate) AS latestdate, c.loanxid FROM markit_prices c
+ WHERE c.pricingdate<=p_date GROUP BY c.loanxid) a
+ JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate;
+ END;
+ $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION historical_bloomberg_corp(p_date date)
+ RETURNS TABLE(cusip character varying, price double precision, pricingdate date, issuer text, maturity date, coupon double precision, coupontype bloomberg_cpntype, frequency integer, spread double precision, libor_floor double precision, loan_margin double precision, covlite boolean, secondlien boolean, defaulted boolean, source bloomberg_source) AS
+$BODY$
+ BEGIN
+ RETURN QUERY SELECT b.* FROM
+ (SELECT MAX(c.pricingdate) AS latestdate, c.cusip FROM bloomberg_corp c
+ WHERE c.pricingdate<=p_date GROUP BY c.cusip) a
+ JOIN bloomberg_corp b ON a.cusip = b.cusip AND a.latestdate= b.pricingdate;
+ END;
+ $BODY$
+ LANGUAGE plpgsql;
+
+-- CREATE OR REPLACE FUNCTION historical_bloomberg_mtge(p_date date)
+-- RETURNS TABLE(CUSIP varchar(9), updatedate date, issuer text, maturity date,
+-- coupon float, coupontype bloomberg_cpntype, frequency integer, spread float, Moody text,
+-- InitialMoody text) AS $$
+-- BEGIN
+-- RETURN QUERY SELECT b.* FROM
+-- (SELECT MAX(c.updatedate) AS updatedate, c.cusip FROM bloomberg_mtge c
+-- WHERE c.updatedate<=p_date GROUP BY c.cusip) a
+-- JOIN bloomberg_mtge b ON a.cusip = b.cusip AND a.updatedate= b.updatedate;
+-- END;
+-- $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_date date)
+ RETURNS TABLE(issuername text, currentbalance float, maturity date,
+ fixedorfloat intex_fixedorfloat, assettype text, price float, grosscoupon float, spread float,
+ frequency varchar(1), nextpaydate date, loanxid varchar(10), cusip varchar(9),
+ covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean, et_loanxid varchar(10), et_cusip varchar(9)) AS $$
+ BEGIN
+ RETURN QUERY SELECT a.issuername, sum(a.currentbalance) AS
+ currentbalance, a.maturity, a.fixedorfloat, max(a.assettype),
+ avg(coalesce((b.bid+b.offer)/2, c.price)) AS price,
+ avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS
+ spread, a.frequency, min(a.nextpaydate) AS nextpaydate,
+ a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or
+ bool_or(c.covlite))) AS covlite, (bool_or(a.secondlien) OR
+ bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo,
+ bool_or(a.defaultedflag) AS defaultedflag, a.et_loanxid, a.et_cusip from et_latestdealinfo(p_dealname, p_date) a
+ left join historical_markit_prices(p_date) b on a.loanxid=b.loanxid left
+ join historical_bloomberg_corp(p_date) c on a.cusip=c.cusip where a.dealname = p_dealname
+ group by a.issuername, a.maturity, a.loanxid, a.fixedorfloat,
+ a.frequency, a.cusip, a.et_loanxid, a.et_cusip ORDER BY issuername;
+ END;
+ $$ LANGUAGE plpgsql;
+
+ALTER FUNCTION et_aggdealinfo_historical(varchar(10), date)
+ OWNER TO et_user;
+
+CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[])
+ RETURNS TABLE(orig_moody text, curr_moody text, empty1 unknown, issuer text,
+ manager text, empty2 unknown, spread float, issuedate date,
+ reinvenddate date, maturity date, stale_percentage float,
+ cdo_percentage float, wap_basis float, portfolio_spread float,
+ subordination float, thickness float ) AS $$
+ BEGIN
+ RETURN QUERY SELECT a.Orig_Moody, a.Curr_Moody, NULL, b."Deal Name", b.manager,NULL,
+ a.Spread/100, b."Deal Issue Date", b."Reinv End Date", b.Maturity,
+ CAST(NULL AS float), Cast(NULL AS float), CAST(NULL AS FLOAT), CAST(NULL AS float),
+ a.Curr_Attach/100, (a.Curr_Detach-a.Curr_Attach)/100
+ FROM cusip_universe a LEFT JOIN latest_clo_universe b
+ ON a.dealname = b.dealname
+ JOIN (SELECT unnest($1) AS cusip, generate_subscripts(p_cusips, 1) AS id) c
+ ON a.cusip = c.cusip ORDER BY c.id;
+ END;
+ $$ LANGUAGE plpgsql;
+
+ALTER FUNCTION et_cusip_details(varchar(10)[])
+ OWNER TO et_user;
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-21 19:43:49 +0000