3 -- Script to eliminate acq.fund_allocation.percent, which has been moved to the
4 -- acq.fund_allocation_percent table.
6 INSERT INTO config.upgrade_log (version) VALUES ('0061'); -- Scott McKellar
8 -- If the following step fails, it's probably because there are still some non-null percent values in
9 -- acq.fund_allocation. They should have all been converted to amounts, and then set to null, by a
10 -- previous upgrade script, 0049.schema.acq_funding_allocation_percent.sql. If there are any non-null
11 -- values, then either that script didn't run, or it didn't work, or some non-null values slipped in
14 -- To convert any remaining percents to amounts: create, run, and then drop the temporary stored
15 -- procedure acq.fund_alloc_percent_val as defined in 0049.schema.acq_funding_allocation_percent.sql.
17 ALTER TABLE acq.fund_allocation
18 ALTER COLUMN amount SET NOT NULL;
20 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
22 SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
23 FROM acq.fund_allocation a
24 JOIN acq.fund f ON (a.fund = f.id)
25 JOIN acq.funding_source s ON (a.funding_source = s.id)
28 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
29 SELECT funding_source,
30 SUM(a.amount)::NUMERIC(100,2) AS amount
31 FROM acq.fund_allocation a
34 ALTER TABLE acq.fund_allocation