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