1 files changed, 12 insertions, 49 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index c4b6ad94..07d34ced 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -969,59 +969,22 @@ END;
 $$ LANGUAGE plpgsql;
 
 CREATE OR REPLACE function list_cds_marks(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,
+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
-  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, $4';
-   and_clause := 'AND strat = ltrim($4::text, ''SER_'')::strategy';
-ELSE
-   params := '$1';
-   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),
-globeop_marks AS (SELECT security_id, maturity, sum(endqty) AS current_notional,
-                  sum(endbooknav) AS nav
-                  FROM (SELECT *, split_part(invid, ''_'', 2) AS security_id,
-                               split_part(invid, ''_'', 6)::date AS maturity
-                        FROM valuation_reports
-                        WHERE periodenddate=$1 and invid like ''CDS\_%%'' %s) a
-                  GROUP BY security_id, maturity)
-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 $2 ELSE 1 END),
-    -temp.notional * temp.fixed_rate/100. * temp.fact * $3 / 360 *
-    (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END),
-    globeop_marks.nav, globeop_marks.current_notional
-FROM temp
-LEFT JOIN index_price USING (index, series, version, tenor)
-LEFT JOIN globeop_marks USING (security_id, maturity)', params, and_clause);
-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;
+BEGIN
+RETURN QUERY
+SELECT l.*, b.globeop_nav, b.globeop_notional from list_cds_marks_pre(p_date, strat, 'SERCGMAST') l
+JOIN (SELECT a.security_id, a.maturity, sum(endbooknav) AS globeop_nav,
+                  sum(endqty) AS globeop_notional
+                  FROM (SELECT periodenddate as date, endqty, endbooknav, split_part(invid, '_', 2) AS security_id,
+                               split_part(invid, '_', 6)::date AS maturity
+                        FROM valuation_reports where invid like 'CDS\_%') a
+                  GROUP BY date, a.security_id, a.maturity HAVING date=p_date) b using (security_id, maturity);
 END;
 $$ LANGUAGE plpgsql;