1 files changed, 29 insertions, 13 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index 207f6bdf..53746434 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -4890,7 +4890,7 @@ 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 AS invccy,
+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,
@@ -4900,14 +4900,18 @@ AS SELECT cr.period_end_date as periodenddate, cr.fund, cr.trade_ccy AS invccy,
   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;
+
+
 CREATE OR REPLACE VIEW globeop_cash_valuations
 AS SELECT 
     vr.periodenddate,
     vr.fund,
     vr.invid,
     vr.invccy,
-    vr.port,
-    vr.strat,
+    vr.port::text,
+    vr.strat::text,
     vr.custacctname,
     sum(vr.endqty) AS endlocalmv
    FROM valuation_reports vr
@@ -4915,40 +4919,52 @@ AS SELECT
  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,
+AS 
+SELECT 
+    cr.period_end_date AS periodenddate,
     cr.fund,
     cr.symbol AS invid,
-    cr.trade_ccy AS invccy,
+    cr.trade_ccy::currency AS invccy,
     'CASH'::text AS port,
     'CSH_CASH'::text AS strat,
     cr."prime_broker/clearing_broker" AS custacctname,
-    sum(cr."quantity(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"
- HAVING cr.symbol = ANY (ARRAY['EUR Cash Balance'::text, 'USD Cash Balance'::text]) ORDER BY period_end_date DESC, fund;
+    CASE WHEN cr."prime_broker/clearing_broker" = 'BOA_FC'
+         THEN (SELECT sum(cr."quantity(end)")-(account_value_market- ending_balance) FROM fcm_moneyline fm WHERE account='6MZ20K79'AND "currency" = 'EUR' AND date=cr.period_end_date)
+         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"
+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
 FROM (
   SELECT periodenddate, fund, port, sum(endlocalmv) AS forward_valuations
-  FROM globeop_forward_valuations 
+  FROM admin_forward_valuations 
   WHERE invid = 'EURF'
   GROUP BY periodenddate, fund, port
 ) f
 FULL OUTER JOIN (
   SELECT periodenddate, fund, port, sum(endlocalmv) AS otc_valuations
-  FROM globeop_otc_valuations 
+  FROM admin_otc_valuations 
   WHERE invccy = 'EUR'
   GROUP BY periodenddate, fund, port
 ) otc
 USING (periodenddate, fund, port)
 FULL OUTER JOIN (
   SELECT periodenddate, fund, port, sum(endlocalmv) AS cash_valuations
-  FROM globeop_cash_valuations UNION citco_cash_valuations
+  FROM admin_cash_valuations
   WHERE invid = 'EUR'
   GROUP BY periodenddate, fund, port
 ) c
-USING (periodenddate, fund, port);
+USING (periodenddate, fund, port)
+ORDER BY periodenddate DESC, 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;