3 INSERT INTO config.upgrade_log (version) VALUES ('0148'); -- 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 (
63 old_fund.currency_type,
68 RETURNING id INTO new_id;
70 WHEN unique_violation THEN
71 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
74 --RAISE NOTICE 'Propagating fund % to fund %',
75 -- old_fund.code, new_id;
80 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
95 IF old_year IS NULL THEN
96 RAISE EXCEPTION 'Input year argument is NULL';
97 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
98 RAISE EXCEPTION 'Input year is out of range';
101 IF user_id IS NULL THEN
102 RAISE EXCEPTION 'Input user id argument is NULL';
105 IF org_unit_id IS NULL THEN
106 RAISE EXCEPTION 'Org unit id argument is NULL';
108 SELECT TRUE INTO org_found
110 WHERE id = org_unit_id;
112 IF org_found IS NULL THEN
113 RAISE EXCEPTION 'Org unit id is invalid';
117 -- Loop over the applicable funds
119 FOR old_fund in SELECT * FROM acq.fund
124 SELECT id FROM actor.org_unit_descendants( org_unit_id )
128 INSERT INTO acq.fund (
140 old_fund.currency_type,
145 RETURNING id INTO new_id;
147 WHEN unique_violation THEN
148 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
151 --RAISE NOTICE 'Propagating fund % to fund %',
152 -- old_fund.code, new_id;
157 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_unit(
165 new_year INT := old_year + 1;
176 IF old_year IS NULL THEN
177 RAISE EXCEPTION 'Input year argument is NULL';
178 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
179 RAISE EXCEPTION 'Input year is out of range';
182 IF user_id IS NULL THEN
183 RAISE EXCEPTION 'Input user id argument is NULL';
186 IF org_unit_id IS NULL THEN
187 RAISE EXCEPTION 'Org unit id argument is NULL';
190 -- Validate the org unit
195 WHERE id = org_unit_id;
197 IF org_found IS NULL THEN
198 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
202 -- Loop over the propagable funds to identify the details
203 -- from the old fund plus the id of the new one, if it exists.
213 newf.id AS new_fund_id
216 LEFT JOIN acq.fund AS newf
217 ON ( oldf.code = newf.code )
219 oldf.org = org_unit_id
220 and oldf.year = old_year
222 and newf.year = new_year
224 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
226 IF roll_fund.new_fund_id IS NULL THEN
228 -- The old fund hasn't been propagated yet. Propagate it now.
230 INSERT INTO acq.fund (
242 roll_fund.currency_type,
247 RETURNING id INTO new_fund;
249 new_fund = roll_fund.new_fund_id;
252 -- Determine the amount to transfer
256 FROM acq.fund_spent_balance
257 WHERE fund = roll_fund.old_fund;
259 IF xfer_amount <> 0 THEN
260 IF roll_fund.rollover THEN
262 -- Transfer balance from old fund to new
264 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
266 PERFORM acq.transfer_fund(
276 -- Transfer balance from old fund to the void
278 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
280 PERFORM acq.transfer_fund(
291 IF roll_fund.rollover THEN
293 -- Move any lineitems from the old fund to the new one
294 -- where the associated debit is an encumbrance.
296 -- Any other tables tying expenditure details to funds should
297 -- receive similar treatment. At this writing there are none.
299 UPDATE acq.lineitem_detail
302 fund = roll_fund.old_fund -- this condition may be redundant
308 fund = roll_fund.old_fund
312 -- Move encumbrance debits from the old fund to the new fund
314 UPDATE acq.fund_debit
317 fund = roll_fund.old_fund
321 -- Mark old fund as inactive, now that we've closed it
325 WHERE id = roll_fund.old_fund;
330 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
338 new_year INT := old_year + 1;
349 IF old_year IS NULL THEN
350 RAISE EXCEPTION 'Input year argument is NULL';
351 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
352 RAISE EXCEPTION 'Input year is out of range';
355 IF user_id IS NULL THEN
356 RAISE EXCEPTION 'Input user id argument is NULL';
359 IF org_unit_id IS NULL THEN
360 RAISE EXCEPTION 'Org unit id argument is NULL';
363 -- Validate the org unit
368 WHERE id = org_unit_id;
370 IF org_found IS NULL THEN
371 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
375 -- Loop over the propagable funds to identify the details
376 -- from the old fund plus the id of the new one, if it exists.
386 newf.id AS new_fund_id
389 LEFT JOIN acq.fund AS newf
390 ON ( oldf.code = newf.code )
394 AND newf.year = new_year
396 SELECT id FROM actor.org_unit_descendants( org_unit_id )
399 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
401 IF roll_fund.new_fund_id IS NULL THEN
403 -- The old fund hasn't been propagated yet. Propagate it now.
405 INSERT INTO acq.fund (
417 roll_fund.currency_type,
422 RETURNING id INTO new_fund;
424 new_fund = roll_fund.new_fund_id;
427 -- Determine the amount to transfer
431 FROM acq.fund_spent_balance
432 WHERE fund = roll_fund.old_fund;
434 IF xfer_amount <> 0 THEN
435 IF roll_fund.rollover THEN
437 -- Transfer balance from old fund to new
439 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
441 PERFORM acq.transfer_fund(
451 -- Transfer balance from old fund to the void
453 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
455 PERFORM acq.transfer_fund(
466 IF roll_fund.rollover THEN
468 -- Move any lineitems from the old fund to the new one
469 -- where the associated debit is an encumbrance.
471 -- Any other tables tying expenditure details to funds should
472 -- receive similar treatment. At this writing there are none.
474 UPDATE acq.lineitem_detail
477 fund = roll_fund.old_fund -- this condition may be redundant
483 fund = roll_fund.old_fund
487 -- Move encumbrance debits from the old fund to the new fund
489 UPDATE acq.fund_debit
492 fund = roll_fund.old_fund
496 -- Mark old fund as inactive, now that we've closed it
500 WHERE id = roll_fund.old_fund;