diff options
| -rw-r--r-- | sql/dawn.sql | 8 |
1 files changed, 4 insertions, 4 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index cf651ae6..17a63805 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -3937,8 +3937,8 @@ UNION LEFT JOIN counterparties c ON cp_code=code ; CREATE OR REPLACE function list_orphaned_cash(p_date date, p_fund fund) -RETURNS TABLE(amount float, port portfolio, strat strategy, account text, currency currency) AS $$ +RETURNS TABLE(amount float, port portfolio, strat strategy, account text, currency currency, full_globeop_strat text) AS $$ BEGIN - RETURN Query SELECT 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) HAVING sum(vr.endqty) !=0; END; -$$ LANGUAGE plpgsql;
\ No newline at end of file + RETURN Query SELECT a.*, coalesce(pfm.folder::text, a.strat::text) FROM (SELECT 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) HAVING sum(vr.endqty) !=0) a LEFT JOIN portfolio_folder_mapping pfm on a.strat::text = pfm.clean_folder::text WHERE active; END; +$$ LANGUAGE plpgsql; |