Open-ILS/src/sql/Pg/upgrade/0628.schema.acq_fund_view_repairs.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('0628', :eg_version);
   4
   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.
  10
  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;
  16
  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
  20     FROM acq.fund fund
  21         LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
  22     GROUP BY fund.id;
  23
  24 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
  25     SELECT
  26         fund.id AS fund,
  27         sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount
  28     FROM acq.fund fund
  29         LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
  30     WHERE fund_debit.encumbrance GROUP BY fund.id;
  31
  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
  35     FROM acq.fund fund
  36         LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
  37     WHERE NOT fund_debit.encumbrance
  38     GROUP BY fund.id;
  39
  40 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
  41     SELECT  c.fund,
  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);
  45
  46 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
  47     SELECT  c.fund,
  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);
  51
  52 COMMIT;