3 INSERT INTO config.upgrade_log (version) VALUES ('0219'); -- Scott McKellar
5 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit(
20 IF old_year IS NULL THEN
21 RAISE EXCEPTION 'Input year argument is NULL';
22 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
23 RAISE EXCEPTION 'Input year is out of range';
26 IF user_id IS NULL THEN
27 RAISE EXCEPTION 'Input user id argument is NULL';
30 IF org_unit_id IS NULL THEN
31 RAISE EXCEPTION 'Org unit id argument is NULL';
33 SELECT TRUE INTO org_found
35 WHERE id = org_unit_id;
37 IF org_found IS NULL THEN
38 RAISE EXCEPTION 'Org unit id is invalid';
42 -- Loop over the applicable funds
44 FOR old_fund in SELECT * FROM acq.fund
51 INSERT INTO acq.fund (
59 balance_warning_percent,
65 old_fund.currency_type,
69 old_fund.balance_warning_percent,
70 old_fund.balance_stop_percent
72 RETURNING id INTO new_id;
74 WHEN unique_violation THEN
75 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
78 --RAISE NOTICE 'Propagating fund % to fund %',
79 -- old_fund.code, new_id;
84 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
99 IF old_year IS NULL THEN
100 RAISE EXCEPTION 'Input year argument is NULL';
101 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
102 RAISE EXCEPTION 'Input year is out of range';
105 IF user_id IS NULL THEN
106 RAISE EXCEPTION 'Input user id argument is NULL';
109 IF org_unit_id IS NULL THEN
110 RAISE EXCEPTION 'Org unit id argument is NULL';
112 SELECT TRUE INTO org_found
114 WHERE id = org_unit_id;
116 IF org_found IS NULL THEN
117 RAISE EXCEPTION 'Org unit id is invalid';
121 -- Loop over the applicable funds
123 FOR old_fund in SELECT * FROM acq.fund
128 SELECT id FROM actor.org_unit_descendants( org_unit_id )
132 INSERT INTO acq.fund (
140 balance_warning_percent,
146 old_fund.currency_type,
150 old_fund.balance_warning_percent,
151 old_fund.balance_stop_percent
153 RETURNING id INTO new_id;
155 WHEN unique_violation THEN
156 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
159 --RAISE NOTICE 'Propagating fund % to fund %',
160 -- old_fund.code, new_id;
165 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_unit(
173 new_year INT := old_year + 1;
184 IF old_year IS NULL THEN
185 RAISE EXCEPTION 'Input year argument is NULL';
186 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
187 RAISE EXCEPTION 'Input year is out of range';
190 IF user_id IS NULL THEN
191 RAISE EXCEPTION 'Input user id argument is NULL';
194 IF org_unit_id IS NULL THEN
195 RAISE EXCEPTION 'Org unit id argument is NULL';
198 -- Validate the org unit
203 WHERE id = org_unit_id;
205 IF org_found IS NULL THEN
206 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
210 -- Loop over the propagable funds to identify the details
211 -- from the old fund plus the id of the new one, if it exists.
221 newf.id AS new_fund_id
224 LEFT JOIN acq.fund AS newf
225 ON ( oldf.code = newf.code )
227 oldf.org = org_unit_id
228 and oldf.year = old_year
230 and newf.year = new_year
232 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
234 IF roll_fund.new_fund_id IS NULL THEN
236 -- The old fund hasn't been propagated yet. Propagate it now.
238 INSERT INTO acq.fund (
246 balance_warning_percent,
252 roll_fund.currency_type,
256 roll_fund.balance_warning_percent,
257 roll_fund.balance_stop_percent
259 RETURNING id INTO new_fund;
261 new_fund = roll_fund.new_fund_id;
264 -- Determine the amount to transfer
268 FROM acq.fund_spent_balance
269 WHERE fund = roll_fund.old_fund;
271 IF xfer_amount <> 0 THEN
272 IF roll_fund.rollover THEN
274 -- Transfer balance from old fund to new
276 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
278 PERFORM acq.transfer_fund(
288 -- Transfer balance from old fund to the void
290 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
292 PERFORM acq.transfer_fund(
303 IF roll_fund.rollover THEN
305 -- Move any lineitems from the old fund to the new one
306 -- where the associated debit is an encumbrance.
308 -- Any other tables tying expenditure details to funds should
309 -- receive similar treatment. At this writing there are none.
311 UPDATE acq.lineitem_detail
314 fund = roll_fund.old_fund -- this condition may be redundant
320 fund = roll_fund.old_fund
324 -- Move encumbrance debits from the old fund to the new fund
326 UPDATE acq.fund_debit
329 fund = roll_fund.old_fund
333 -- Mark old fund as inactive, now that we've closed it
337 WHERE id = roll_fund.old_fund;
342 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
350 new_year INT := old_year + 1;
361 IF old_year IS NULL THEN
362 RAISE EXCEPTION 'Input year argument is NULL';
363 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
364 RAISE EXCEPTION 'Input year is out of range';
367 IF user_id IS NULL THEN
368 RAISE EXCEPTION 'Input user id argument is NULL';
371 IF org_unit_id IS NULL THEN
372 RAISE EXCEPTION 'Org unit id argument is NULL';
375 -- Validate the org unit
380 WHERE id = org_unit_id;
382 IF org_found IS NULL THEN
383 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
387 -- Loop over the propagable funds to identify the details
388 -- from the old fund plus the id of the new one, if it exists.
398 newf.id AS new_fund_id
401 LEFT JOIN acq.fund AS newf
402 ON ( oldf.code = newf.code )
406 AND newf.year = new_year
408 SELECT id FROM actor.org_unit_descendants( org_unit_id )
411 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
413 IF roll_fund.new_fund_id IS NULL THEN
415 -- The old fund hasn't been propagated yet. Propagate it now.
417 INSERT INTO acq.fund (
425 balance_warning_percent,
431 roll_fund.currency_type,
435 roll_fund.balance_warning_percent,
436 roll_fund.balance_stop_percent
438 RETURNING id INTO new_fund;
440 new_fund = roll_fund.new_fund_id;
443 -- Determine the amount to transfer
447 FROM acq.fund_spent_balance
448 WHERE fund = roll_fund.old_fund;
450 IF xfer_amount <> 0 THEN
451 IF roll_fund.rollover THEN
453 -- Transfer balance from old fund to new
455 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
457 PERFORM acq.transfer_fund(
467 -- Transfer balance from old fund to the void
469 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
471 PERFORM acq.transfer_fund(
482 IF roll_fund.rollover THEN
484 -- Move any lineitems from the old fund to the new one
485 -- where the associated debit is an encumbrance.
487 -- Any other tables tying expenditure details to funds should
488 -- receive similar treatment. At this writing there are none.
490 UPDATE acq.lineitem_detail
493 fund = roll_fund.old_fund -- this condition may be redundant
499 fund = roll_fund.old_fund
503 -- Move encumbrance debits from the old fund to the new fund
505 UPDATE acq.fund_debit
508 fund = roll_fund.old_fund
512 -- Mark old fund as inactive, now that we've closed it
516 WHERE id = roll_fund.old_fund;