Open-ILS/src/sql/Pg/upgrade/0173.schema.acq.all-fund-views.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0173'); -- Scott McKellar
   4
   5 -- For each fund: the total allocation from all sources, in the
   6 -- currency of the fund (or 0 if there are no allocations)
   7
   8 CREATE VIEW acq.all_fund_allocation_total AS
   9 SELECT
  10     f.id AS fund,
  11     COALESCE( SUM( a.amount * acq.exchange_ratio(
  12         s.currency_type, f.currency_type))::numeric(100,2), 0 )
  13     AS amount
  14 FROM
  15     acq.fund f
  16         LEFT JOIN acq.fund_allocation a
  17             ON a.fund = f.id
  18         LEFT JOIN acq.funding_source s
  19             ON a.funding_source = s.id
  20 GROUP BY
  21     f.id;
  22
  23 -- For every fund: the total encumbrances (or 0 if none),
  24 -- in the currency of the fund.
  25
  26 CREATE VIEW acq.all_fund_encumbrance_total AS
  27 SELECT
  28         f.id AS fund,
  29         COALESCE( encumb.amount, 0 ) AS amount
  30 FROM
  31         acq.fund AS f
  32                 LEFT JOIN (
  33                         SELECT
  34                                 fund,
  35                                 sum( amount ) AS amount
  36                         FROM
  37                                 acq.fund_debit
  38                         WHERE
  39                                 encumbrance
  40                         GROUP BY fund
  41                 ) AS encumb
  42                         ON f.id = encumb.fund;
  43
  44 -- For every fund: the total spent (or 0 if none),
  45 -- in the currency of the fund.
  46
  47 CREATE VIEW acq.all_fund_spent_total AS
  48 SELECT
  49     f.id AS fund,
  50     COALESCE( spent.amount, 0 ) AS amount
  51 FROM
  52     acq.fund AS f
  53         LEFT JOIN (
  54             SELECT
  55                 fund,
  56                 sum( amount ) AS amount
  57             FROM
  58                 acq.fund_debit
  59             WHERE
  60                 NOT encumbrance
  61             GROUP BY fund
  62         ) AS spent
  63             ON f.id = spent.fund;
  64
  65 -- For each fund: the amount not yet spent, in the currency
  66 -- of the fund.  May include encumbrances.
  67
  68 CREATE VIEW acq.all_fund_spent_balance AS
  69 SELECT
  70         c.fund,
  71         c.amount - d.amount AS amount
  72 FROM acq.all_fund_allocation_total c
  73     LEFT JOIN acq.all_fund_spent_total d USING (fund);
  74
  75 -- For each fund: the amount neither spent nor encumbered,
  76 -- in the currency of the fund
  77
  78 CREATE VIEW acq.all_fund_combined_balance AS
  79 SELECT
  80      a.fund,
  81      a.amount - COALESCE( c.amount, 0 ) AS amount
  82 FROM
  83      acq.all_fund_allocation_total a
  84         LEFT OUTER JOIN (
  85             SELECT
  86                 fund,
  87                 SUM( amount ) AS amount
  88             FROM
  89                 acq.fund_debit
  90             GROUP BY
  91                 fund
  92         ) AS c USING ( fund );
  93
  94 COMMIT;