BEGIN; SELECT evergreen.upgrade_deps_block_check('0628', :eg_version); -- acq.fund_combined_balance and acq.fund_spent_balance are unchanged, -- however we need to drop them to recreate the other views. -- we need to drop all our views because we change the number of columns -- for example, debit_total does not need an encumberance column when we -- have a sepearate total for that. DROP VIEW acq.fund_spent_balance; DROP VIEW acq.fund_combined_balance; DROP VIEW acq.fund_encumbrance_total; DROP VIEW acq.fund_spent_total; DROP VIEW acq.fund_debit_total; CREATE OR REPLACE VIEW acq.fund_debit_total AS SELECT fund.id AS fund, sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount FROM acq.fund fund LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund GROUP BY fund.id; CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS SELECT fund.id AS fund, sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount FROM acq.fund fund LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund WHERE fund_debit.encumbrance GROUP BY fund.id; CREATE OR REPLACE VIEW acq.fund_spent_total AS SELECT fund.id AS fund, sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount FROM acq.fund fund LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund WHERE NOT fund_debit.encumbrance GROUP BY fund.id; CREATE OR REPLACE VIEW acq.fund_combined_balance AS SELECT c.fund, c.amount - COALESCE(d.amount, 0.0) AS amount FROM acq.fund_allocation_total c LEFT JOIN acq.fund_debit_total d USING (fund); CREATE OR REPLACE VIEW acq.fund_spent_balance AS SELECT c.fund, c.amount - COALESCE(d.amount,0.0) AS amount FROM acq.fund_allocation_total c LEFT JOIN acq.fund_spent_total d USING (fund); COMMIT;