3 SELECT evergreen.upgrade_deps_block_check('0780', :eg_version);
5 ALTER TABLE acq.distribution_formula_entry
6 ADD COLUMN fund INT REFERENCES acq.fund (id),
7 ADD COLUMN circ_modifier TEXT REFERENCES config.circ_modifier (code),
8 ADD COLUMN collection_code TEXT ;
11 -- support option to roll distribution formula funds
12 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
16 encumb_only BOOL DEFAULT FALSE,
17 include_desc BOOL DEFAULT TRUE
22 new_year INT := old_year + 1;
25 xfer_amount NUMERIC := 0;
29 roll_distrib_forms BOOL;
35 IF old_year IS NULL THEN
36 RAISE EXCEPTION 'Input year argument is NULL';
37 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
38 RAISE EXCEPTION 'Input year is out of range';
41 IF user_id IS NULL THEN
42 RAISE EXCEPTION 'Input user id argument is NULL';
45 IF org_unit_id IS NULL THEN
46 RAISE EXCEPTION 'Org unit id argument is NULL';
49 -- Validate the org unit
54 WHERE id = org_unit_id;
56 IF org_found IS NULL THEN
57 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
58 ELSIF encumb_only THEN
59 SELECT INTO perm_ous value::BOOL FROM
60 actor.org_unit_ancestor_setting(
61 'acq.fund.allow_rollover_without_money', org_unit_id
63 IF NOT FOUND OR NOT perm_ous THEN
64 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
69 -- Loop over the propagable funds to identify the details
70 -- from the old fund plus the id of the new one, if it exists.
80 newf.id AS new_fund_id
83 LEFT JOIN acq.fund AS newf
84 ON ( oldf.code = newf.code )
88 AND newf.year = new_year
89 AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
90 OR (NOT include_desc AND oldf.org = org_unit_id ) )
92 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
94 IF roll_fund.new_fund_id IS NULL THEN
96 -- The old fund hasn't been propagated yet. Propagate it now.
98 INSERT INTO acq.fund (
106 balance_warning_percent,
112 roll_fund.currency_type,
116 roll_fund.balance_warning_percent,
117 roll_fund.balance_stop_percent
119 RETURNING id INTO new_fund;
121 new_fund = roll_fund.new_fund_id;
124 -- Determine the amount to transfer
128 FROM acq.fund_spent_balance
129 WHERE fund = roll_fund.old_fund;
131 IF xfer_amount <> 0 THEN
132 IF NOT encumb_only AND roll_fund.rollover THEN
134 -- Transfer balance from old fund to new
136 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
138 PERFORM acq.transfer_fund(
148 -- Transfer balance from old fund to the void
150 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
152 PERFORM acq.transfer_fund(
158 'Rollover into the void'
163 IF roll_fund.rollover THEN
165 -- Move any lineitems from the old fund to the new one
166 -- where the associated debit is an encumbrance.
168 -- Any other tables tying expenditure details to funds should
169 -- receive similar treatment. At this writing there are none.
171 UPDATE acq.lineitem_detail
174 fund = roll_fund.old_fund -- this condition may be redundant
180 fund = roll_fund.old_fund
184 -- Move encumbrance debits from the old fund to the new fund
186 UPDATE acq.fund_debit
189 fund = roll_fund.old_fund
193 -- Rollover distribution formulae funds
194 SELECT INTO roll_distrib_forms value::BOOL FROM
195 actor.org_unit_ancestor_setting(
196 'acq.fund.rollover_distrib_forms', org_unit_id
199 IF roll_distrib_forms THEN
200 UPDATE acq.distribution_formula_entry
201 SET fund = roll_fund.new_fund_id
202 WHERE fund = roll_fund.old_fund;
206 -- Mark old fund as inactive, now that we've closed it
210 WHERE id = roll_fund.old_fund;