1 files changed, 44 insertions, 9 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql
index edf1551b..7460da1e 100644
--- a/sql/et_tables.sql
+++ b/sql/et_tables.sql
@@ -310,7 +310,8 @@ CREATE TABLE clo_universe (
 
 GRANT ALL ON clo_universe TO et_user;
 
-CREATE TABLE cusip_universe (
+CREATE TABLE cusip_universe_old (
+--deprecated
        Cusip varchar(9),
        ISIN varchar(12),
        dealname varchar(10),
@@ -336,6 +337,36 @@ CREATE TABLE cusip_universe (
 
 GRANT ALL ON cusip_universe TO et_user;
 
+CREATE TABLE cusip_ref(
+       Cusip varchar(9) PRIMARY KEY,
+       ISIN varchar(12),
+       bloomberg_ticker text,
+       dealname varchar(10),
+       tranche text,
+       pari-passu_tranches text[],
+       Orig_Balance float,
+       Orig_Moody text,
+       Orig_Attach float,
+       Orig_Detach float,
+       Floater_Index text,
+       Spread float);
+
+CREATE TABLE cusip_update(
+       Cusip varchar(9) REFERENCES cusip_ref,
+       curr_balance float,
+       factor float,
+       coupon float,
+       Curr_Moody text,
+       Curr_Attach float,
+       Curr_Detach float,
+       updatedate date,
+       PRIMARY KEY(cusip, updatedate));
+
+CREATE OR REPLACE VIEW cusip_universe AS
+       SELECT a.*, b.curr_balance, b.coupon, b.curr_moody, b.curr_attach, b.curr_detach, b.subordination,
+       b.thickness, b.updatedate FROM cusip_ref a
+       JOIN cusip_update b USING (cusip);
+
 CREATE OR REPLACE VIEW latest_clo_universe AS
        SELECT b.dealname, "Deal Name", Manager, orig_deal_bal, b."Curr Deal Bal", orig_collat_bal,
        b."Curr Collat Bal", b."Tranche Factor", b."Principal Bal", b."Interest Bal", b."CDO Percentage",
@@ -348,11 +379,13 @@ CREATE OR REPLACE VIEW latest_clo_universe AS
 
 GRANT ALL ON latest_clo_universe TO et_user;
 
-CREATE VIEW latest_cusip_universe AS
-       SELECT b.*
-       FROM (SELECT MAX(updatedate) AS latestdate, cusip FROM cusip_universe GROUP BY cusip) a
-       JOIN cusip_universe b ON a.cusip = b.cusip AND a.latestdate= b.updatedate
-       ORDER by cusip asc;
+CREATE OR REPLACE VIEW latest_cusip_universe AS
+       WITH latest_cusip_update AS
+         (SELECT DISTINCT ON (cusip) * FROM cusip_update
+          ORDER BY cusip, updatedate DESC)
+       SELECT a.*, b.curr_balance, b.coupon, b.curr_moody, b.curr_attach,
+           b.curr_detach, b.updatedate FROM cusip_ref a
+       JOIN latest_cusip_update b USING (cusip);
 
 GRANT ALL ON latest_cusip_universe TO et_user;
 
@@ -394,8 +427,8 @@ CREATE OR REPLACE FUNCTION historical_dealname_universe(p_dealname varchar(10),
        END;
 $$ LANGUAGE plpgsql;
 
-CREATE VIEW dealcusipmapping AS
-       SELECT dealname, cusip from latest_cusip_universe;
+CREATE OR REPLACE VIEW dealcusipmapping AS
+       SELECT dealname, cusip from cusip_ref;
 
 GRANT ALL ON dealcusipmapping TO et_user;
 
@@ -541,12 +574,14 @@ CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[])
        reinvenddate date, maturity date, stale_percentage float,
        cdo_percentage float, wap_basis float, portfolio_spread_5y float,
        portfolio_spread float, subordination float, thickness float,
+       mvoc float, mvcoverage float,
        empty3 unknown, pricingdate date, delta float, duration float, wal float, price float) AS $$
        BEGIN
        RETURN QUERY SELECT a.Orig_Moody, a.Curr_Moody, NULL, b."Deal Name", b.manager, NULL,
        a.Spread/100, a.Coupon/100, b.deal_issue_date, b.reinv_end_date, b.Maturity,
        e.stalepercentage, greatest(b."CDO Percentage"/100, e.cdopercentage), e.wapbasis,
-       e.dealspread5y, e.dealspread, a.curr_attach/100, (a.curr_detach-a.curr_attach)/100, NULL, d.updatedate,
+       e.dealspread5y, e.dealspread, a.curr_attach/100, (a.curr_detach-a.curr_attach)/100, d.mvoc, d.mvcoverage,
+       NULL, d.updatedate,
        d.delta, d.duration, d.wal, d.price
        FROM latest_cusip_universe a LEFT JOIN latest_clo_universe b
        ON a.dealname = b.dealname