LP#1204671: Stamping upgrade script for fund tag propagation
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / 0926.fund_tags_persist.sql
1 BEGIN;
2
3 SELECT evergreen.upgrade_deps_block_check('0926', :eg_version);
4
5 CREATE OR REPLACE FUNCTION acq.copy_fund_tags(
6         old_fund_id INTEGER,
7         new_fund_id INTEGER
8 ) RETURNS VOID AS $$
9 DECLARE
10 fund_tag_rec    RECORD;
11 BEGIN
12        
13         FOR fund_tag_rec IN SELECT * FROM acq.fund_tag_map WHERE fund=old_fund_id LOOP
14                 BEGIN
15                      INSERT INTO acq.fund_tag_map(fund, tag) VALUES(new_fund_id, fund_tag_rec.tag);
16                 EXCEPTION
17                         WHEN unique_violation THEN
18                         --    RAISE NOTICE 'Fund tag already propagated', old_fund.id;
19                         CONTINUE;
20                 END;
21         END LOOP;
22         RETURN;
23 END;
24 $$ LANGUAGE plpgsql;
25
26 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
27         old_year INTEGER,
28         user_id INTEGER,
29         org_unit_id INTEGER,
30     encumb_only BOOL DEFAULT FALSE,
31     include_desc BOOL DEFAULT TRUE
32 ) RETURNS VOID AS $$
33 DECLARE
34 --
35 new_fund    INT;
36 new_year    INT := old_year + 1;
37 org_found   BOOL;
38 perm_ous    BOOL;
39 xfer_amount NUMERIC := 0;
40 roll_fund   RECORD;
41 deb         RECORD;
42 detail      RECORD;
43 roll_distrib_forms BOOL;
44 --
45 BEGIN
46         --
47         -- Sanity checks
48         --
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';
53         END IF;
54         --
55         IF user_id IS NULL THEN
56                 RAISE EXCEPTION 'Input user id argument is NULL';
57         END IF;
58         --
59         IF org_unit_id IS NULL THEN
60                 RAISE EXCEPTION 'Org unit id argument is NULL';
61         ELSE
62                 --
63                 -- Validate the org unit
64                 --
65                 SELECT TRUE
66                 INTO org_found
67                 FROM actor.org_unit
68                 WHERE id = org_unit_id;
69                 --
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
76                         );
77                         IF NOT FOUND OR NOT perm_ous THEN
78                                 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
79                         END IF;
80                 END IF;
81         END IF;
82         --
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.
85         --
86         FOR roll_fund in
87         SELECT
88             oldf.id AS old_fund,
89             oldf.org,
90             oldf.name,
91             oldf.currency_type,
92             oldf.code,
93                 oldf.rollover,
94             newf.id AS new_fund_id
95         FROM
96         acq.fund AS oldf
97         LEFT JOIN acq.fund AS newf
98                 ON ( oldf.code = newf.code )
99         WHERE
100                     oldf.year = old_year
101                 AND oldf.propagate
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 ) )
105         LOOP
106                 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
107                 --
108                 IF roll_fund.new_fund_id IS NULL THEN
109                         --
110                         -- The old fund hasn't been propagated yet.  Propagate it now.
111                         --
112                         INSERT INTO acq.fund (
113                                 org,
114                                 name,
115                                 year,
116                                 currency_type,
117                                 code,
118                                 rollover,
119                                 propagate,
120                                 balance_warning_percent,
121                                 balance_stop_percent
122                         ) VALUES (
123                                 roll_fund.org,
124                                 roll_fund.name,
125                                 new_year,
126                                 roll_fund.currency_type,
127                                 roll_fund.code,
128                                 true,
129                                 true,
130                                 roll_fund.balance_warning_percent,
131                                 roll_fund.balance_stop_percent
132                         )
133                         RETURNING id INTO new_fund;
134
135                         PERFORM acq.copy_fund_tags(roll_fund.id,new_fund);
136
137                 ELSE
138                         new_fund = roll_fund.new_fund_id;
139                 END IF;
140                 --
141                 -- Determine the amount to transfer
142                 --
143                 SELECT amount
144                 INTO xfer_amount
145                 FROM acq.fund_spent_balance
146                 WHERE fund = roll_fund.old_fund;
147                 --
148                 IF xfer_amount <> 0 THEN
149                         IF NOT encumb_only AND roll_fund.rollover THEN
150                                 --
151                                 -- Transfer balance from old fund to new
152                                 --
153                                 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
154                                 --
155                                 PERFORM acq.transfer_fund(
156                                         roll_fund.old_fund,
157                                         xfer_amount,
158                                         new_fund,
159                                         xfer_amount,
160                                         user_id,
161                                         'Rollover'
162                                 );
163                         ELSE
164                                 --
165                                 -- Transfer balance from old fund to the void
166                                 --
167                                 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
168                                 --
169                                 PERFORM acq.transfer_fund(
170                                         roll_fund.old_fund,
171                                         xfer_amount,
172                                         NULL,
173                                         NULL,
174                                         user_id,
175                                         'Rollover into the void'
176                                 );
177                         END IF;
178                 END IF;
179                 --
180                 IF roll_fund.rollover THEN
181                         --
182                         -- Move any lineitems from the old fund to the new one
183                         -- where the associated debit is an encumbrance.
184                         --
185                         -- Any other tables tying expenditure details to funds should
186                         -- receive similar treatment.  At this writing there are none.
187                         --
188                         UPDATE acq.lineitem_detail
189                         SET fund = new_fund
190                         WHERE
191                         fund = roll_fund.old_fund -- this condition may be redundant
192                         AND fund_debit in
193                         (
194                                 SELECT id
195                                 FROM acq.fund_debit
196                                 WHERE
197                                 fund = roll_fund.old_fund
198                                 AND encumbrance
199                         );
200                         --
201                         -- Move encumbrance debits from the old fund to the new fund
202                         --
203                         UPDATE acq.fund_debit
204                         SET fund = new_fund
205                         wHERE
206                                 fund = roll_fund.old_fund
207                                 AND encumbrance;
208                 END IF;
209
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
214                         );
215
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;
220                 END IF;
221
222                 --
223                 -- Mark old fund as inactive, now that we've closed it
224                 --
225                 UPDATE acq.fund
226                 SET active = FALSE
227                 WHERE id = roll_fund.old_fund;
228         END LOOP;
229 END;
230 $$ LANGUAGE plpgsql;
231
232 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
233         old_year INTEGER,
234         user_id INTEGER,
235         org_unit_id INTEGER,
236     include_desc BOOL DEFAULT TRUE
237 ) RETURNS VOID AS $$
238 DECLARE
239 --
240 new_id      INT;
241 old_fund    RECORD;
242 org_found   BOOLEAN;
243 --
244 BEGIN
245         --
246         -- Sanity checks
247         --
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';
252         END IF;
253         --
254         IF user_id IS NULL THEN
255                 RAISE EXCEPTION 'Input user id argument is NULL';
256         END IF;
257         --
258         IF org_unit_id IS NULL THEN
259                 RAISE EXCEPTION 'Org unit id argument is NULL';
260         ELSE
261                 SELECT TRUE INTO org_found
262                 FROM actor.org_unit
263                 WHERE id = org_unit_id;
264                 --
265                 IF org_found IS NULL THEN
266                         RAISE EXCEPTION 'Org unit id is invalid';
267                 END IF;
268         END IF;
269         --
270         -- Loop over the applicable funds
271         --
272         FOR old_fund in SELECT * FROM acq.fund
273         WHERE
274                 year = old_year
275                 AND propagate
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 ) )
278     
279         LOOP
280                 BEGIN
281                         INSERT INTO acq.fund (
282                                 org,
283                                 name,
284                                 year,
285                                 currency_type,
286                                 code,
287                                 rollover,
288                                 propagate,
289                                 balance_warning_percent,
290                                 balance_stop_percent
291                         ) VALUES (
292                                 old_fund.org,
293                                 old_fund.name,
294                                 old_year + 1,
295                                 old_fund.currency_type,
296                                 old_fund.code,
297                                 old_fund.rollover,
298                                 true,
299                                 old_fund.balance_warning_percent,
300                                 old_fund.balance_stop_percent
301                         )
302                         RETURNING id INTO new_id;
303                 EXCEPTION
304                         WHEN unique_violation THEN
305                                 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
306                                 CONTINUE;
307                 END;
308
309                 PERFORM acq.copy_fund_tags(old_fund.id,new_id);
310
311                 --RAISE NOTICE 'Propagating fund % to fund %',
312                 --      old_fund.code, new_id;
313         END LOOP;
314 END;
315 $$ LANGUAGE plpgsql;
316
317 COMMIT;
318
319