3 SELECT evergreen.upgrade_deps_block_check('0730', :eg_version);
5 DROP FUNCTION acq.propagate_funds_by_org_tree (INT, INT, INT);
6 DROP FUNCTION acq.propagate_funds_by_org_unit (INT, INT, INT);
8 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
12 include_desc BOOL DEFAULT TRUE
24 IF old_year IS NULL THEN
25 RAISE EXCEPTION 'Input year argument is NULL';
26 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
27 RAISE EXCEPTION 'Input year is out of range';
30 IF user_id IS NULL THEN
31 RAISE EXCEPTION 'Input user id argument is NULL';
34 IF org_unit_id IS NULL THEN
35 RAISE EXCEPTION 'Org unit id argument is NULL';
37 SELECT TRUE INTO org_found
39 WHERE id = org_unit_id;
41 IF org_found IS NULL THEN
42 RAISE EXCEPTION 'Org unit id is invalid';
46 -- Loop over the applicable funds
48 FOR old_fund in SELECT * FROM acq.fund
52 AND ( ( include_desc AND org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
53 OR (NOT include_desc AND org = org_unit_id ) )
57 INSERT INTO acq.fund (
65 balance_warning_percent,
71 old_fund.currency_type,
75 old_fund.balance_warning_percent,
76 old_fund.balance_stop_percent
78 RETURNING id INTO new_id;
80 WHEN unique_violation THEN
81 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
84 --RAISE NOTICE 'Propagating fund % to fund %',
85 -- old_fund.code, new_id;
90 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit( old_year INTEGER, user_id INTEGER, org_unit_id INTEGER ) RETURNS VOID AS $$
91 SELECT acq.propagate_funds_by_org_tree( $1, $2, $3, FALSE );
95 DROP FUNCTION acq.rollover_funds_by_org_tree (INT, INT, INT);
96 DROP FUNCTION acq.rollover_funds_by_org_unit (INT, INT, INT);
99 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
103 encumb_only BOOL DEFAULT FALSE,
104 include_desc BOOL DEFAULT TRUE
109 new_year INT := old_year + 1;
112 xfer_amount NUMERIC := 0;
121 IF old_year IS NULL THEN
122 RAISE EXCEPTION 'Input year argument is NULL';
123 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
124 RAISE EXCEPTION 'Input year is out of range';
127 IF user_id IS NULL THEN
128 RAISE EXCEPTION 'Input user id argument is NULL';
131 IF org_unit_id IS NULL THEN
132 RAISE EXCEPTION 'Org unit id argument is NULL';
135 -- Validate the org unit
140 WHERE id = org_unit_id;
142 IF org_found IS NULL THEN
143 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
144 ELSIF encumb_only THEN
145 SELECT INTO perm_ous value::BOOL FROM
146 actor.org_unit_ancestor_setting(
147 'acq.fund.allow_rollover_without_money', org_unit_id
149 IF NOT FOUND OR NOT perm_ous THEN
150 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
155 -- Loop over the propagable funds to identify the details
156 -- from the old fund plus the id of the new one, if it exists.
166 newf.id AS new_fund_id
169 LEFT JOIN acq.fund AS newf
170 ON ( oldf.code = newf.code )
174 AND newf.year = new_year
175 AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
176 OR (NOT include_desc AND oldf.org = org_unit_id ) )
178 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
180 IF roll_fund.new_fund_id IS NULL THEN
182 -- The old fund hasn't been propagated yet. Propagate it now.
184 INSERT INTO acq.fund (
192 balance_warning_percent,
198 roll_fund.currency_type,
202 roll_fund.balance_warning_percent,
203 roll_fund.balance_stop_percent
205 RETURNING id INTO new_fund;
207 new_fund = roll_fund.new_fund_id;
210 -- Determine the amount to transfer
214 FROM acq.fund_spent_balance
215 WHERE fund = roll_fund.old_fund;
217 IF xfer_amount <> 0 THEN
218 IF NOT encumb_only AND roll_fund.rollover THEN
220 -- Transfer balance from old fund to new
222 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
224 PERFORM acq.transfer_fund(
234 -- Transfer balance from old fund to the void
236 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
238 PERFORM acq.transfer_fund(
244 'Rollover into the void'
249 IF roll_fund.rollover THEN
251 -- Move any lineitems from the old fund to the new one
252 -- where the associated debit is an encumbrance.
254 -- Any other tables tying expenditure details to funds should
255 -- receive similar treatment. At this writing there are none.
257 UPDATE acq.lineitem_detail
260 fund = roll_fund.old_fund -- this condition may be redundant
266 fund = roll_fund.old_fund
270 -- Move encumbrance debits from the old fund to the new fund
272 UPDATE acq.fund_debit
275 fund = roll_fund.old_fund
279 -- Mark old fund as inactive, now that we've closed it
283 WHERE id = roll_fund.old_fund;
288 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_unit( old_year INTEGER, user_id INTEGER, org_unit_id INTEGER, encumb_only BOOL DEFAULT FALSE ) RETURNS VOID AS $$
289 SELECT acq.rollover_funds_by_org_tree( $1, $2, $3, $4, FALSE );
292 INSERT into config.org_unit_setting_type
293 (name, grp, label, description, datatype)
295 'acq.fund.allow_rollover_without_money',
298 'acq.fund.allow_rollover_without_money',
299 'Allow funds to be rolled over without bringing the money along',
304 'acq.fund.allow_rollover_without_money',
305 'Allow funds to be rolled over without bringing the money along. This makes money left in the old fund disappear, modeling its return to some outside entity.',