1 files changed, 40 insertions, 11 deletions

diff --git a/sql/dawn.sql b/sql/dawn.sql
index d72be9b3..f39e01fd 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -644,24 +644,56 @@ BEGIN
   query := 'WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity,
 cds.fixed_rate, cds.currency, %s SUM(cds.notional * (CASE WHEN cds.protection=''Buyer'' THEN 1 ELSE -1 END))
 OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach)  AS notional
-FROM cds WHERE cds.fund=$2 AND (cds.termination_date is NULL OR cds.termination_date[1]> $1)
-               AND cds.trade_date <=$1 %s)
+FROM list_cds($1, $2) cds WHERE %s)
 SELECT DISTINCT ON (tmp.security_id, tmp.maturity %s) * FROM tmp WHERE tmp.notional!=0';
 
 IF p_type = 'tranche' THEN
    RETURN format(query, 'cds.orig_attach,cds.orig_detach,cds.attach,cds.detach,'
                         'cds.initial_margin_percentage,',
-                        'AND cds.orig_attach is NOT NULL', ',tmp.orig_attach');
+                        'cds.orig_attach is NOT NULL', ',tmp.orig_attach');
 ELSIF p_type = 'cds' THEN
-   RETURN format(query, '', 'AND cds.attach is NULL AND cds.folder!=''MBSCDS''', '');
+   RETURN format(query, '', 'cds.attach is NULL AND cds.folder!=''MBSCDS''', '');
 ELSIF p_type = 'abs' THEN
-   RETURN format(query, '', 'AND cds.folder=''MBSCDS''', '');
+   RETURN format(query, '', 'cds.folder=''MBSCDS''', '');
 ELSE
    RETURN format(query, 'cds.attach, cds.detach,', '', ',tmp.attach');
 END IF;
 END;
 $$ LANGUAGE plpgsql;
 
+CREATE TYPE LIST_CDS AS(
+       trade_date date,
+       security_id varchar(12),
+       security_desc varchar(32),
+       maturity date,
+       fixed_rate float,
+       currency currency,
+       folder cds_strat,
+       protection protection,
+       notional float,
+       orig_attach smallint,
+       orig_detach smallint,
+       attach float,
+       detach float,
+       fcm text
+);
+
+CREATE OR REPLACE function list_cds(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund)
+RETURNS SETOF LIST_CDS AS $$
+BEGIN
+RETURN QUERY
+SELECT trade_date, security_id, security_desc, maturity, fixed_rate, currency,
+       folder, protection, notional - coalesce(terminated_amount, 0.) AS notional,
+       orig_attach, orig_detach, attach, detach, account_code
+FROM cds LEFT JOIN (
+     SELECT id, SUM(termination_amount ) AS terminated_amount
+     FROM (SELECT id, unnest(termination_amount) as termination_amount,
+                  unnest(termination_date) as termination_date FROM cds) a
+           WHERE termination_date <= p_date GROUP BY id) b USING (id)
+           WHERE fund=p_fund and notional != 0. and trade_date <= p_date;
+END;
+$$ LANGUAGE plpgsql;
+
 CREATE OR REPLACE function list_cds_positions (p_date date,
                                                strat cds_strat DEFAULT NULL::cds_strat,
                                                fund fund DEFAULT 'SERCGMAST'::fund)
@@ -690,14 +722,13 @@ RETURN QUERY
        cds.currency, cds.folder,
        SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END))
        OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder)  AS notional
-       FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date[1] > p_date) AND
-       cds.trade_date <= p_date AND cds.attach is NULL AND cds.folder != 'MBSCDS' AND cds.fund=p_fund)
+       FROM list_cds(p_date, p_fund) cds WHERE (cds.attach is NULL AND cds.folder != 'MBSCDS'))
   SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0;
 END;
 $$ LANGUAGE plpgsql;
 
 CREATE OR REPLACE function list_cds_positions_by_strat_fcm(p_date date,
-                                                           fcm text)
+                                                           p_fcm text)
 RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date,
               fixed_rate float, currency currency, folder cds_strat, notional float) AS $$
 BEGIN
@@ -706,9 +737,7 @@ RETURN QUERY
        cds.currency, cds.folder,
        SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END))
        OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder, cds.account_code)  AS notional
-       FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date[1] > p_date) AND
-       cds.trade_date <= p_date AND cds.attach is NULL AND cds.folder != 'MBSCDS'
-       AND cds.account_code=fcm)
+       FROM list_cds(p_date) cds WHERE (cds.attach is NULL AND cds.folder != 'MBSCDS' AND cds.fcm=fcm))
   SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0;
 END;
 $$ LANGUAGE plpgsql;