3 -- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
5 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
9 encumb_only BOOL DEFAULT FALSE,
10 include_desc BOOL DEFAULT TRUE
15 new_year INT := old_year + 1;
18 xfer_amount NUMERIC := 0;
22 roll_distrib_forms BOOL;
28 IF old_year IS NULL THEN
29 RAISE EXCEPTION 'Input year argument is NULL';
30 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
31 RAISE EXCEPTION 'Input year is out of range';
34 IF user_id IS NULL THEN
35 RAISE EXCEPTION 'Input user id argument is NULL';
38 IF org_unit_id IS NULL THEN
39 RAISE EXCEPTION 'Org unit id argument is NULL';
42 -- Validate the org unit
47 WHERE id = org_unit_id;
49 IF org_found IS NULL THEN
50 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
51 ELSIF encumb_only THEN
52 SELECT INTO perm_ous value::BOOL FROM
53 actor.org_unit_ancestor_setting(
54 'acq.fund.allow_rollover_without_money', org_unit_id
56 IF NOT FOUND OR NOT perm_ous THEN
57 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
62 -- Loop over the propagable funds to identify the details
63 -- from the old fund plus the id of the new one, if it exists.
73 newf.id AS new_fund_id
76 LEFT JOIN acq.fund AS newf
77 ON ( oldf.code = newf.code AND oldf.org = newf.org )
81 AND newf.year = new_year
82 AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
83 OR (NOT include_desc AND oldf.org = org_unit_id ) )
85 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
87 IF roll_fund.new_fund_id IS NULL THEN
89 -- The old fund hasn't been propagated yet. Propagate it now.
91 INSERT INTO acq.fund (
99 balance_warning_percent,
105 roll_fund.currency_type,
109 roll_fund.balance_warning_percent,
110 roll_fund.balance_stop_percent
112 RETURNING id INTO new_fund;
114 PERFORM acq.copy_fund_tags(roll_fund.id,new_fund);
117 new_fund = roll_fund.new_fund_id;
120 -- Determine the amount to transfer
124 FROM acq.fund_spent_balance
125 WHERE fund = roll_fund.old_fund;
127 IF xfer_amount <> 0 THEN
128 IF NOT encumb_only AND roll_fund.rollover THEN
130 -- Transfer balance from old fund to new
132 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
134 PERFORM acq.transfer_fund(
144 -- Transfer balance from old fund to the void
146 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
148 PERFORM acq.transfer_fund(
154 'Rollover into the void'
159 IF roll_fund.rollover THEN
161 -- Move any lineitems from the old fund to the new one
162 -- where the associated debit is an encumbrance.
164 -- Any other tables tying expenditure details to funds should
165 -- receive similar treatment. At this writing there are none.
167 UPDATE acq.lineitem_detail
170 fund = roll_fund.old_fund -- this condition may be redundant
176 fund = roll_fund.old_fund
180 -- Move encumbrance debits from the old fund to the new fund
182 UPDATE acq.fund_debit
185 fund = roll_fund.old_fund
189 -- Rollover distribution formulae funds
190 SELECT INTO roll_distrib_forms value::BOOL FROM
191 actor.org_unit_ancestor_setting(
192 'acq.fund.rollover_distrib_forms', org_unit_id
195 IF roll_distrib_forms THEN
196 UPDATE acq.distribution_formula_entry
197 SET fund = roll_fund.new_fund_id
198 WHERE fund = roll_fund.old_fund;
202 -- Mark old fund as inactive, now that we've closed it
206 WHERE id = roll_fund.old_fund;