3 INSERT INTO config.upgrade_log (version) VALUES ('0173'); -- Scott McKellar
5 -- For each fund: the total allocation from all sources, in the
6 -- currency of the fund (or 0 if there are no allocations)
8 CREATE VIEW acq.all_fund_allocation_total AS
11 COALESCE( SUM( a.amount * acq.exchange_ratio(
12 s.currency_type, f.currency_type))::numeric(100,2), 0 )
16 LEFT JOIN acq.fund_allocation a
18 LEFT JOIN acq.funding_source s
19 ON a.funding_source = s.id
23 -- For every fund: the total encumbrances (or 0 if none),
24 -- in the currency of the fund.
26 CREATE VIEW acq.all_fund_encumbrance_total AS
29 COALESCE( encumb.amount, 0 ) AS amount
35 sum( amount ) AS amount
42 ON f.id = encumb.fund;
44 -- For every fund: the total spent (or 0 if none),
45 -- in the currency of the fund.
47 CREATE VIEW acq.all_fund_spent_total AS
50 COALESCE( spent.amount, 0 ) AS amount
56 sum( amount ) AS amount
65 -- For each fund: the amount not yet spent, in the currency
66 -- of the fund. May include encumbrances.
68 CREATE VIEW acq.all_fund_spent_balance AS
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);
75 -- For each fund: the amount neither spent nor encumbered,
76 -- in the currency of the fund
78 CREATE VIEW acq.all_fund_combined_balance AS
81 a.amount - COALESCE( c.amount, 0 ) AS amount
83 acq.all_fund_allocation_total a
87 SUM( amount ) AS amount
92 ) AS c USING ( fund );