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