1 files changed, 29 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index ba7164cc..4c224eb6 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -3844,6 +3844,35 @@ AS SELECT tranche_risk.date,
   WHERE cds.fund = 'ISOSEL'::fund
   ORDER BY index_version.index, index_version.series, cds.orig_attach;
 
+CREATE OR REPLACE VIEW tranche_risk_globeop 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, cds.fund
+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)
+FULL 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 IN ('SERCGMAST', 'BOWDST')
+ORDER BY index, series, orig_attach;
+
+
 CREATE OR REPLACE VIEW tranche_risk_citco
 AS SELECT tranche_risk.date,
     tranche_risk.tranche_id AS trade_id,