1 files changed, 50 insertions, 1 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index 1aca1cd6..7eb1413d 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -3556,4 +3556,53 @@ CREATE TABLE citco_stax (
 	knowledge_date timestamp NULL,
 	"row" int NULL,
 	CONSTRAINT citco_stax_pk PRIMARY KEY (period_end_date, "row")
-);
-\ No newline at end of file
+);
+
+CREATE OR REPLACE VIEW tranche_risk_selene
+AS SELECT tranche_risk.date,
+    tranche_risk.tranche_id AS trade_id,
+    b.identifier,
+    cds.security_desc,
+    index_version.index,
+    index_version.series,
+    cds.maturity,
+    cds.orig_attach,
+    cds.orig_detach,
+    tranche_risk.notional,
+    a.admin_notional,
+    tranche_risk.clean_nav AS serenitas_clean_nav,
+    a.admin_clean_nav,
+    tranche_risk.accrued AS serenitas_accrued,
+    a.admin_accrued,
+    external_marks_deriv.base_nav AS cpty_nav,
+    tranche_risk.duration,
+    tranche_risk.delta,
+    tranche_risk.gamma,
+    tranche_risk.theta,
+    tranche_risk.theta_amount,
+    tranche_risk.tranche_factor,
+    tranche_risk.corr_attach,
+    tranche_risk.corr_detach,
+    tranche_risk.upfront,
+    tranche_risk.running,
+    tranche_risk.index_refprice,
+    tranche_risk.index_refspread,
+    tranche_risk.index_duration,
+    tranche_risk.hy_equiv,
+    cds.initial_margin_percentage
+   FROM tranche_risk
+     LEFT JOIN LATERAL ( SELECT identifier FROM citco_submission cs WHERE serenitas_id = concat('SCCDS', tranche_risk.tranche_id) ORDER BY submit_date DESC LIMIT 1) b ON true
+     LEFT JOIN cds ON tranche_risk.tranche_id = cds.id
+     FULL JOIN ( SELECT
+     		citco_stax.fix_id ,
+            citco_stax.period_end_date,
+            0 AS admin_accrued,
+            sum(base_market_value) AS admin_clean_nav,
+            sum(quantity) AS admin_notional
+           FROM citco_stax WHERE close_trade_date IS NULL
+          GROUP BY citco_stax.fix_id, citco_stax.period_end_date) a ON NULLIF("substring"(a.fix_id, 'SCCDS(\d*)'::text), ''::text)::integer = tranche_risk.tranche_id AND a.period_end_date = tranche_risk.date
+     LEFT JOIN index_version ON cds.security_id::text = index_version.redindexcode
+     LEFT JOIN external_marks_deriv ON cds.cpty_id = external_marks_deriv.identifier AND external_marks_deriv.date = tranche_risk.date
+  WHERE cds.fund = 'ISOSEL'::fund
+  ORDER BY index_version.index, index_version.series, cds.orig_attach;
+ 
+\ No newline at end of file