1 files changed, 14 insertions, 5 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index 6993c389..d5ad3ea2 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -3921,10 +3921,19 @@ UNION
     spots.spot_rate
    FROM spots) a
    LEFT JOIN counterparties c ON cp_code=code ;
-  
- CREATE OR REPLACE function list_orphaned_cash(p_date date, p_fund fund)
-RETURNS TABLE(fund fund, trade_date date,amount float, portfolio portfolio, clean_strat strategy, cash_account text, currency currency, folder TEXT) AS $$
+
+CREATE OR REPLACE function list_orphaned_cash(p_date date, p_fund fund)
+RETURNS TABLE(fund fund, trade_date date,amount float, portfolio portfolio, cash_account text, currency currency, folder TEXT, custodian text, cp_code varchar(10)) AS $$
 BEGIN
-  RETURN Query SELECT a.*, coalesce(pfm.folder::text, a.strat::text)  FROM (SELECT vr.fund, vr.periodenddate, -sum(vr.endqty) AS amount, vr.port, vr.strat, vr.custacctname AS account, vr.invid::currency AS currency FROM valuation_reports vr 
-  WHERE vr.periodenddate =p_date AND vr.fund=p_fund AND vr.invid IN ('USD', 'EUR') AND vr.port NOT IN ('GFS_HELPER_BUSINESS_UNIT', 'CASH') GROUP BY (vr.port, vr.strat, vr.custacctname, vr.invid, vr.fund, vr.periodenddate) HAVING sum(vr.endqty) !=0) a LEFT JOIN portfolio_folder_mapping pfm on a.strat::text = pfm.clean_folder::text WHERE active; END;
+  RETURN QUERY
+  SELECT a.*, coalesce(pfm.folder::text, a.strat::text), accounts2.custodian, accounts2.cp_code FROM (
+  SELECT vr.fund, vr.periodenddate, -sum(vr.endqty) AS amount, vr.port, vr.custacctname AS cash_account, vr.invid::currency AS currency
+  FROM valuation_reports vr
+  GROUP BY (vr.periodenddate, vr.fund, vr.port, vr.strat, vr.custacctname, vr.invid)
+  HAVING periodenddate=p_date AND vr.fund=p_fund AND vr.invid in ('USD', 'EUR') and port NOT IN ('GFS_HELPER_BUSINESS_UNIT', 'CASH') AND sum(vr.endqty) !=0
+  ) a
+  LEFT JOIN portfolio_folder_mapping pfm ON a.strat::text = pfm.clean_folder::text
+  LEFT JOIN accounts2 USING (cash_account)
+  WHERE pfm.active;
+END
 $$ LANGUAGE plpgsql;