aboutsummaryrefslogtreecommitdiffstats
path: root/sql/dawn.sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql/dawn.sql')
-rw-r--r--sql/dawn.sql130
1 files changed, 58 insertions, 72 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index d56f7120..2ed0295e 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -4853,14 +4853,15 @@ CREATE TABLE irs_tickets (
CREATE OR REPLACE VIEW globeop_forward_valuations AS
-SELECT periodenddate, fund, invccy, invid, port::text, strat::text, sum(endlocalmv) as endlocalmv
+SELECT periodenddate, knowledgedate, fund, invccy, invid, port::text, strat::text, sum(endlocalmv) as endlocalmv
FROM valuation_reports vr
-GROUP BY invccy, invid, periodenddate, fund, port, strat
+GROUP BY periodenddate, knowledgedate, fund, invccy, invid, port, strat
HAVING invid IN ('EURF') ORDER BY periodenddate desc, fund;
CREATE OR REPLACE VIEW citco_forward_valuations
AS SELECT
cr.period_end_date AS periodenddate,
+ cr.knowledge_date as knowledgedate,
cr.fund,
"substring"(cr.bloomberg_code, 0, 4)::currency AS invccy,
"substring"(cr.bloomberg_code, 0, 4) || 'F'::text AS invid,
@@ -4868,15 +4869,11 @@ AS SELECT
split_part(cr.strategy, '/'::text, 2) AS strat,
sum(cr."quantity(end)") AS endlocalmv
FROM citco_reports cr
- GROUP BY cr.fund, cr.bloomberg_code, cr.period_end_date, cr.strategy
+ GROUP BY cr.period_end_date, cr.knowledge_date, cr.fund, cr.bloomberg_code, cr.strategy
HAVING cr.bloomberg_code ~~ '%CURNCY'::text
ORDER BY cr.period_end_date DESC, fund;
-CREATE OR REPLACE VIEW admin_forward_valuations
-AS SELECT * FROM globeop_forward_valuations UNION SELECT * FROM citco_forward_valuations ORDER BY periodenddate desc, fund;
-
-
-CREATE OR REPLACE VIEW globeop_otc_valuations AS SELECT periodenddate, fund, invccy, custacctname, port::text, strat::text,
+CREATE OR REPLACE VIEW globeop_otc_valuations AS SELECT periodenddate, knowledgedate, fund, invccy, custacctname, port::text, strat::text,
sum(
CASE
WHEN endbookmv = 0 OR endlocalmv = 0 THEN 0
@@ -4885,28 +4882,28 @@ CREATE OR REPLACE VIEW globeop_otc_valuations AS SELECT periodenddate, fund, inv
) as endlocalmv
FROM valuation_reports vr
WHERE invid NOT IN ('EUR', 'EURF') AND endbooknav IS NOT NULL
-GROUP BY invccy, custacctname, port, strat, periodenddate, fund
+GROUP BY periodenddate, knowledgedate, fund, invccy, custacctname, port, strat
HAVING invccy IN ('EUR', 'USD')
AND custacctname IN (SELECT cash_account FROM accounts2 WHERE account_type = 'Cash' AND active) ORDER BY periodenddate desc, fund;
CREATE OR REPLACE VIEW citco_otc_valuations
-AS SELECT cr.period_end_date as periodenddate, cr.fund, cr.trade_ccy::currency AS invccy,
+AS SELECT cr.period_end_date AS periodenddate,
+ cr.knowledge_date as knowledgedate,
+ cr.fund,
+ cr.trade_ccy::currency AS invccy,
cr."prime_broker/clearing_broker" AS custacctname,
COALESCE(split_part(cr.strategy, '/'::text, 1), 'CASH'::text) AS port,
COALESCE(split_part(cr.strategy, '/'::text, 2), 'CSH_CASH'::text) AS strat,
- sum(cr.issue_market_value) AS endlocalmv
+ sum(cr.issue_nav_contribution) AS endlocalmv
FROM citco_reports cr
WHERE cr."prime_broker/clearing_broker" ~~ '%_IS'::text
- GROUP BY cr.trade_ccy, cr.period_end_date, cr.fund, cr."prime_broker/clearing_broker", (COALESCE(split_part(cr.strategy, '/'::text, 1), 'CASH'::text)), (COALESCE(split_part(cr.strategy, '/'::text, 2), 'CSH_CASH'::text))
- ORDER BY cr.period_end_date DESC, fund;
-
-CREATE OR REPLACE VIEW admin_otc_valuations
-AS SELECT * FROM globeop_otc_valuations UNION SELECT * FROM citco_otc_valuations ORDER BY periodenddate desc, fund;
-
+ GROUP BY cr.period_end_date, cr.knowledge_date, cr.fund, cr.trade_ccy, cr."prime_broker/clearing_broker", cr.strategy
+ ORDER BY cr.period_end_date DESC, cr.fund;
CREATE OR REPLACE VIEW globeop_cash_valuations
AS SELECT
vr.periodenddate,
+ vr.knowledgedate,
vr.fund,
vr.invid,
vr.invccy,
@@ -4915,13 +4912,14 @@ AS SELECT
vr.custacctname,
sum(vr.endqty) AS endlocalmv
FROM valuation_reports vr
- GROUP BY vr.invccy, vr.invid, vr.fund, vr.port, vr.strat, vr.periodenddate, vr.custacctname
+ GROUP BY vr.periodenddate, vr.knowledgedate, vr.fund, vr.invid, vr.invccy, vr.port, vr.strat, vr.custacctname
HAVING vr.invid = ANY (ARRAY['EUR'::text, 'USD'::text]) ORDER BY periodenddate DESC, fund;
CREATE OR REPLACE VIEW citco_cash_valuations
AS
SELECT
cr.period_end_date AS periodenddate,
+ cr.knowledge_date as knowledgedate,
cr.fund,
cr.trade_ccy AS invid,
cr.trade_ccy::currency AS invccy,
@@ -4933,77 +4931,65 @@ SELECT
ELSE sum(cr."quantity(end)")
END AS endlocalmv
FROM citco_reports cr
-GROUP BY cr.period_end_date, cr.fund, cr.symbol, cr.trade_ccy, cr."prime_broker/clearing_broker"
+GROUP BY cr.period_end_date, cr.knowledge_date, cr.fund, cr.trade_ccy, cr."prime_broker/clearing_broker", cr.symbol
HAVING cr.symbol = ANY (ARRAY['EUR Cash Balance'::text, 'USD Cash Balance'::text])
ORDER BY period_end_date DESC, fund;
-
-CREATE OR REPLACE VIEW admin_cash_valuations
-AS SELECT * FROM globeop_cash_valuations UNION SELECT * FROM citco_cash_valuations ORDER BY periodenddate desc, fund;
-
CREATE VIEW eur_fx_valuations AS
-SELECT periodenddate, fund, port, forward_valuations, otc_valuations, cash_valuations
+SELECT periodenddate, knowledgedate, fund, port, forward_valuations, otc_valuations, cash_valuations
FROM (
- SELECT periodenddate, fund, port, sum(endlocalmv) AS forward_valuations
- FROM admin_forward_valuations
+ SELECT periodenddate, knowledgedate, fund, port, sum(endlocalmv) AS forward_valuations
+ FROM (SELECT * FROM globeop_forward_valuations UNION SELECT * FROM citco_forward_valuations) afv
WHERE invid = 'EURF'
- GROUP BY periodenddate, fund, port
+ GROUP BY periodenddate, knowledgedate, fund, port
) f
FULL OUTER JOIN (
- SELECT periodenddate, fund, port, sum(endlocalmv) AS otc_valuations
- FROM admin_otc_valuations
+ SELECT periodenddate, knowledgedate, fund, port, sum(endlocalmv) AS otc_valuations
+ FROM (SELECT * FROM globeop_otc_valuations UNION SELECT * FROM citco_otc_valuations) aov
WHERE invccy = 'EUR'
- GROUP BY periodenddate, fund, port
+ GROUP BY periodenddate, knowledgedate, fund, port
) otc
-USING (periodenddate, fund, port)
+USING (periodenddate, knowledgedate, fund, port)
FULL OUTER JOIN (
- SELECT periodenddate, fund, port, sum(endlocalmv) AS cash_valuations
- FROM admin_cash_valuations
+ SELECT periodenddate, knowledgedate, fund, port, sum(endlocalmv) AS cash_valuations
+ FROM (SELECT * FROM globeop_cash_valuations UNION SELECT * FROM citco_cash_valuations) acv
WHERE invid = 'EUR'
- GROUP BY periodenddate, fund, port
+ GROUP BY periodenddate, knowledgedate, fund, port
) c
-USING (periodenddate, fund, port)
-ORDER BY periodenddate DESC, fund;
-
+USING (periodenddate, knowledgedate, fund, port)
+ORDER BY periodenddate DESC, knowledgedate, fund;
-CREATE VIEW eur_fx_excess AS SELECT fund, periodenddate, sum(COALESCE (forward_valuations,0) + COALESCE (otc_valuations,0) + COALESCE (cash_valuations,0)) AS eur_excess FROM eur_fx_valuations GROUP BY fund, periodenddate ORDER BY periodenddate DESC;
-CREATE TABLE citco_account_mapping (fund fund, citco_code text, account TEXT references accounts2(cash_account), PRIMARY KEY (fund, citco_code));
-
-CREATE OR REPLACE VIEW citco_cash_valuations
-AS
-SELECT
- cr.period_end_date AS periodenddate,
- cr.fund,
- cr.trade_ccy AS invccy,
- cr.trade_ccy AS invid,
- sum(cr."quantity(end)") AS endlocalmv,
- 'CASH' AS port,
- 'CSH_CASH' AS strat,
- cr."prime_broker/clearing_broker" AS custacctname
-FROM citco_reports cr
-GROUP BY
- cr.period_end_date,
- cr.fund,
- cr.symbol,
- cr.trade_ccy,
- cr."prime_broker/clearing_broker"
-HAVING
- cr.symbol IN ('EUR Cash Balance', 'USD Cash Balance');
+CREATE OR REPLACE FUNCTION calculate_eur_excess(p_periodenddate DATE)
+RETURNS TABLE (
+ fund fund,
+ knowledgedate timestamp,
+ periodenddate date,
+ eur_excess numeric(11,2)
+) AS $$
+BEGIN
+ RETURN QUERY
+ SELECT
+ efv.fund,
+ efv.knowledgedate,
+ efv.periodenddate,
+ SUM(COALESCE(efv.forward_valuations, 0) + COALESCE(efv.otc_valuations, 0) + COALESCE(efv.cash_valuations, 0))::numeric(11,2) AS eur_excess
+ FROM
+ eur_fx_valuations efv
+ WHERE
+ efv.periodenddate = p_periodenddate
+ GROUP BY
+ efv.fund,
+ efv.knowledgedate,
+ efv.periodenddate
+ ORDER BY
+ efv.periodenddate DESC,
+ efv.fund;
+END;
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE VIEW citco_otc_valuations
-AS SELECT cr.period_end_date AS periodenddate,
- cr.fund,
- cr.trade_ccy::currency AS invccy,
- cr."prime_broker/clearing_broker" AS custacctname,
- COALESCE(split_part(cr.strategy, '/'::text, 1), 'CASH'::text) AS port,
- COALESCE(split_part(cr.strategy, '/'::text, 2), 'CSH_CASH'::text) AS strat,
- sum(cr.issue_nav_contribution) AS endlocalmv
- FROM citco_reports cr
- WHERE cr."prime_broker/clearing_broker" ~~ '%_IS'::text
- GROUP BY cr.trade_ccy, cr.period_end_date, cr.fund, cr."prime_broker/clearing_broker", (COALESCE(split_part(cr.strategy, '/'::text, 1), 'CASH'::text)), (COALESCE(split_part(cr.strategy, '/'::text, 2), 'CSH_CASH'::text))
- ORDER BY cr.period_end_date DESC, cr.fund;
+CREATE TABLE citco_account_mapping (fund fund, citco_code text, account TEXT references accounts2(cash_account), PRIMARY KEY (fund, citco_code));
CREATE VIEW cds_termination_mapping AS
SELECT t.id AS termination_id, cds.*
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-29 17:56:18 +0000