3 SELECT evergreen.upgrade_deps_block_check('0926', :eg_version);
5 CREATE OR REPLACE FUNCTION acq.copy_fund_tags(
13 FOR fund_tag_rec IN SELECT * FROM acq.fund_tag_map WHERE fund=old_fund_id LOOP
15 INSERT INTO acq.fund_tag_map(fund, tag) VALUES(new_fund_id, fund_tag_rec.tag);
17 WHEN unique_violation THEN
18 -- RAISE NOTICE 'Fund tag already propagated', old_fund.id;
26 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
30 encumb_only BOOL DEFAULT FALSE,
31 include_desc BOOL DEFAULT TRUE
36 new_year INT := old_year + 1;
39 xfer_amount NUMERIC := 0;
43 roll_distrib_forms BOOL;
49 IF old_year IS NULL THEN
50 RAISE EXCEPTION 'Input year argument is NULL';
51 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
52 RAISE EXCEPTION 'Input year is out of range';
55 IF user_id IS NULL THEN
56 RAISE EXCEPTION 'Input user id argument is NULL';
59 IF org_unit_id IS NULL THEN
60 RAISE EXCEPTION 'Org unit id argument is NULL';
63 -- Validate the org unit
68 WHERE id = org_unit_id;
70 IF org_found IS NULL THEN
71 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
72 ELSIF encumb_only THEN
73 SELECT INTO perm_ous value::BOOL FROM
74 actor.org_unit_ancestor_setting(
75 'acq.fund.allow_rollover_without_money', org_unit_id
77 IF NOT FOUND OR NOT perm_ous THEN
78 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
83 -- Loop over the propagable funds to identify the details
84 -- from the old fund plus the id of the new one, if it exists.
94 newf.id AS new_fund_id
97 LEFT JOIN acq.fund AS newf
98 ON ( oldf.code = newf.code )
102 AND newf.year = new_year
103 AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
104 OR (NOT include_desc AND oldf.org = org_unit_id ) )
106 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
108 IF roll_fund.new_fund_id IS NULL THEN
110 -- The old fund hasn't been propagated yet. Propagate it now.
112 INSERT INTO acq.fund (
120 balance_warning_percent,
126 roll_fund.currency_type,
130 roll_fund.balance_warning_percent,
131 roll_fund.balance_stop_percent
133 RETURNING id INTO new_fund;
135 PERFORM acq.copy_fund_tags(roll_fund.id,new_fund);
138 new_fund = roll_fund.new_fund_id;
141 -- Determine the amount to transfer
145 FROM acq.fund_spent_balance
146 WHERE fund = roll_fund.old_fund;
148 IF xfer_amount <> 0 THEN
149 IF NOT encumb_only AND roll_fund.rollover THEN
151 -- Transfer balance from old fund to new
153 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
155 PERFORM acq.transfer_fund(
165 -- Transfer balance from old fund to the void
167 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
169 PERFORM acq.transfer_fund(
175 'Rollover into the void'
180 IF roll_fund.rollover THEN
182 -- Move any lineitems from the old fund to the new one
183 -- where the associated debit is an encumbrance.
185 -- Any other tables tying expenditure details to funds should
186 -- receive similar treatment. At this writing there are none.
188 UPDATE acq.lineitem_detail
191 fund = roll_fund.old_fund -- this condition may be redundant
197 fund = roll_fund.old_fund
201 -- Move encumbrance debits from the old fund to the new fund
203 UPDATE acq.fund_debit
206 fund = roll_fund.old_fund
210 -- Rollover distribution formulae funds
211 SELECT INTO roll_distrib_forms value::BOOL FROM
212 actor.org_unit_ancestor_setting(
213 'acq.fund.rollover_distrib_forms', org_unit_id
216 IF roll_distrib_forms THEN
217 UPDATE acq.distribution_formula_entry
218 SET fund = roll_fund.new_fund_id
219 WHERE fund = roll_fund.old_fund;
223 -- Mark old fund as inactive, now that we've closed it
227 WHERE id = roll_fund.old_fund;
232 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
236 include_desc BOOL DEFAULT TRUE
248 IF old_year IS NULL THEN
249 RAISE EXCEPTION 'Input year argument is NULL';
250 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
251 RAISE EXCEPTION 'Input year is out of range';
254 IF user_id IS NULL THEN
255 RAISE EXCEPTION 'Input user id argument is NULL';
258 IF org_unit_id IS NULL THEN
259 RAISE EXCEPTION 'Org unit id argument is NULL';
261 SELECT TRUE INTO org_found
263 WHERE id = org_unit_id;
265 IF org_found IS NULL THEN
266 RAISE EXCEPTION 'Org unit id is invalid';
270 -- Loop over the applicable funds
272 FOR old_fund in SELECT * FROM acq.fund
276 AND ( ( include_desc AND org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
277 OR (NOT include_desc AND org = org_unit_id ) )
281 INSERT INTO acq.fund (
289 balance_warning_percent,
295 old_fund.currency_type,
299 old_fund.balance_warning_percent,
300 old_fund.balance_stop_percent
302 RETURNING id INTO new_id;
304 WHEN unique_violation THEN
305 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
309 PERFORM acq.copy_fund_tags(old_fund.id,new_id);
311 --RAISE NOTICE 'Propagating fund % to fund %',
312 -- old_fund.code, new_id;