LP 1478712: Fix acq.fund_rollover_funds_by_org_tree DB function.
[Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / XXXX.function.acq.rollover-by-org-tree.sql
1 BEGIN;
2
3 -- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); 
4
5 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
6         old_year INTEGER,
7         user_id INTEGER,
8         org_unit_id INTEGER,
9     encumb_only BOOL DEFAULT FALSE,
10     include_desc BOOL DEFAULT TRUE
11 ) RETURNS VOID AS $$
12 DECLARE
13 --
14 new_fund    INT;
15 new_year    INT := old_year + 1;
16 org_found   BOOL;
17 perm_ous    BOOL;
18 xfer_amount NUMERIC := 0;
19 roll_fund   RECORD;
20 deb         RECORD;
21 detail      RECORD;
22 roll_distrib_forms BOOL;
23 --
24 BEGIN
25         --
26         -- Sanity checks
27         --
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';
32         END IF;
33         --
34         IF user_id IS NULL THEN
35                 RAISE EXCEPTION 'Input user id argument is NULL';
36         END IF;
37         --
38         IF org_unit_id IS NULL THEN
39                 RAISE EXCEPTION 'Org unit id argument is NULL';
40         ELSE
41                 --
42                 -- Validate the org unit
43                 --
44                 SELECT TRUE
45                 INTO org_found
46                 FROM actor.org_unit
47                 WHERE id = org_unit_id;
48                 --
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
55                         );
56                         IF NOT FOUND OR NOT perm_ous THEN
57                                 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
58                         END IF;
59                 END IF;
60         END IF;
61         --
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.
64         --
65         FOR roll_fund in
66         SELECT
67             oldf.id AS old_fund,
68             oldf.org,
69             oldf.name,
70             oldf.currency_type,
71             oldf.code,
72                 oldf.rollover,
73             newf.id AS new_fund_id
74         FROM
75         acq.fund AS oldf
76         LEFT JOIN acq.fund AS newf
77                 ON ( oldf.code = newf.code AND oldf.org = newf.org )
78         WHERE
79                     oldf.year = old_year
80                 AND oldf.propagate
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 ) )
84         LOOP
85                 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
86                 --
87                 IF roll_fund.new_fund_id IS NULL THEN
88                         --
89                         -- The old fund hasn't been propagated yet.  Propagate it now.
90                         --
91                         INSERT INTO acq.fund (
92                                 org,
93                                 name,
94                                 year,
95                                 currency_type,
96                                 code,
97                                 rollover,
98                                 propagate,
99                                 balance_warning_percent,
100                                 balance_stop_percent
101                         ) VALUES (
102                                 roll_fund.org,
103                                 roll_fund.name,
104                                 new_year,
105                                 roll_fund.currency_type,
106                                 roll_fund.code,
107                                 true,
108                                 true,
109                                 roll_fund.balance_warning_percent,
110                                 roll_fund.balance_stop_percent
111                         )
112                         RETURNING id INTO new_fund;
113
114                         PERFORM acq.copy_fund_tags(roll_fund.id,new_fund);
115
116                 ELSE
117                         new_fund = roll_fund.new_fund_id;
118                 END IF;
119                 --
120                 -- Determine the amount to transfer
121                 --
122                 SELECT amount
123                 INTO xfer_amount
124                 FROM acq.fund_spent_balance
125                 WHERE fund = roll_fund.old_fund;
126                 --
127                 IF xfer_amount <> 0 THEN
128                         IF NOT encumb_only AND roll_fund.rollover THEN
129                                 --
130                                 -- Transfer balance from old fund to new
131                                 --
132                                 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
133                                 --
134                                 PERFORM acq.transfer_fund(
135                                         roll_fund.old_fund,
136                                         xfer_amount,
137                                         new_fund,
138                                         xfer_amount,
139                                         user_id,
140                                         'Rollover'
141                                 );
142                         ELSE
143                                 --
144                                 -- Transfer balance from old fund to the void
145                                 --
146                                 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
147                                 --
148                                 PERFORM acq.transfer_fund(
149                                         roll_fund.old_fund,
150                                         xfer_amount,
151                                         NULL,
152                                         NULL,
153                                         user_id,
154                                         'Rollover into the void'
155                                 );
156                         END IF;
157                 END IF;
158                 --
159                 IF roll_fund.rollover THEN
160                         --
161                         -- Move any lineitems from the old fund to the new one
162                         -- where the associated debit is an encumbrance.
163                         --
164                         -- Any other tables tying expenditure details to funds should
165                         -- receive similar treatment.  At this writing there are none.
166                         --
167                         UPDATE acq.lineitem_detail
168                         SET fund = new_fund
169                         WHERE
170                         fund = roll_fund.old_fund -- this condition may be redundant
171                         AND fund_debit in
172                         (
173                                 SELECT id
174                                 FROM acq.fund_debit
175                                 WHERE
176                                 fund = roll_fund.old_fund
177                                 AND encumbrance
178                         );
179                         --
180                         -- Move encumbrance debits from the old fund to the new fund
181                         --
182                         UPDATE acq.fund_debit
183                         SET fund = new_fund
184                         wHERE
185                                 fund = roll_fund.old_fund
186                                 AND encumbrance;
187                 END IF;
188
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
193                         );
194
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;
199                 END IF;
200
201                 --
202                 -- Mark old fund as inactive, now that we've closed it
203                 --
204                 UPDATE acq.fund
205                 SET active = FALSE
206                 WHERE id = roll_fund.old_fund;
207         END LOOP;
208 END;
209 $$ LANGUAGE plpgsql;
210
211 COMMIT;