3 SELECT evergreen.upgrade_deps_block_check('0628', :eg_version);
5 -- acq.fund_combined_balance and acq.fund_spent_balance are unchanged,
6 -- however we need to drop them to recreate the other views.
7 -- we need to drop all our views because we change the number of columns
8 -- for example, debit_total does not need an encumberance column when we
9 -- have a sepearate total for that.
11 DROP VIEW acq.fund_spent_balance;
12 DROP VIEW acq.fund_combined_balance;
13 DROP VIEW acq.fund_encumbrance_total;
14 DROP VIEW acq.fund_spent_total;
15 DROP VIEW acq.fund_debit_total;
17 CREATE OR REPLACE VIEW acq.fund_debit_total AS
18 SELECT fund.id AS fund,
19 sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount
21 LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
24 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
27 sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount
29 LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
30 WHERE fund_debit.encumbrance GROUP BY fund.id;
32 CREATE OR REPLACE VIEW acq.fund_spent_total AS
33 SELECT fund.id AS fund,
34 sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount
36 LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
37 WHERE NOT fund_debit.encumbrance
40 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
42 c.amount - COALESCE(d.amount, 0.0) AS amount
43 FROM acq.fund_allocation_total c
44 LEFT JOIN acq.fund_debit_total d USING (fund);
46 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
48 c.amount - COALESCE(d.amount,0.0) AS amount
49 FROM acq.fund_allocation_total c
50 LEFT JOIN acq.fund_spent_total d USING (fund);