LP#800478 repair logic error in acq fund transfer
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / XXXX.schema.acq_fund_transfer.sql
1 BEGIN;
2
3 CREATE OR REPLACE FUNCTION acq.transfer_fund(
4         old_fund   IN INT,
5         old_amount IN NUMERIC,     -- in currency of old fund
6         new_fund   IN INT,
7         new_amount IN NUMERIC,     -- in currency of new fund
8         user_id    IN INT,
9         xfer_note  IN TEXT         -- to be recorded in acq.fund_transfer
10         -- ,funding_source_in IN INT  -- if user wants to specify a funding source (see notes)
11 ) RETURNS VOID AS $$
12 /* -------------------------------------------------------------------------------
13
14 Function to transfer money from one fund to another.
15
16 A transfer is represented as a pair of entries in acq.fund_allocation, with a
17 negative amount for the old (losing) fund and a positive amount for the new
18 (gaining) fund.  In some cases there may be more than one such pair of entries
19 in order to pull the money from different funding sources, or more specifically
20 from different funding source credits.  For each such pair there is also an
21 entry in acq.fund_transfer.
22
23 Since funding_source is a non-nullable column in acq.fund_allocation, we must
24 choose a funding source for the transferred money to come from.  This choice
25 must meet two constraints, so far as possible:
26
27 1. The amount transferred from a given funding source must not exceed the
28 amount allocated to the old fund by the funding source.  To that end we
29 compare the amount being transferred to the amount allocated.
30
31 2. We shouldn't transfer money that has already been spent or encumbered, as
32 defined by the funding attribution process.  We attribute expenses to the
33 oldest funding source credits first.  In order to avoid transferring that
34 attributed money, we reverse the priority, transferring from the newest funding
35 source credits first.  There can be no guarantee that this approach will
36 avoid overcommitting a fund, but no other approach can do any better.
37
38 In this context the age of a funding source credit is defined by the
39 deadline_date for credits with deadline_dates, and by the effective_date for
40 credits without deadline_dates, with the proviso that credits with deadline_dates
41 are all considered "older" than those without.
42
43 ----------
44
45 In the signature for this function, there is one last parameter commented out,
46 named "funding_source_in".  Correspondingly, the WHERE clause for the query
47 driving the main loop has an OR clause commented out, which references the
48 funding_source_in parameter.
49
50 If these lines are uncommented, this function will allow the user optionally to
51 restrict a fund transfer to a specified funding source.  If the source
52 parameter is left NULL, then there will be no such restriction.
53
54 ------------------------------------------------------------------------------- */ 
55 DECLARE
56         same_currency      BOOLEAN;
57         currency_ratio     NUMERIC;
58         old_fund_currency  TEXT;
59         old_remaining      NUMERIC;  -- in currency of old fund
60         new_fund_currency  TEXT;
61         new_fund_active    BOOLEAN;
62         new_remaining      NUMERIC;  -- in currency of new fund
63         curr_old_amt       NUMERIC;  -- in currency of old fund
64         curr_new_amt       NUMERIC;  -- in currency of new fund
65         source_addition    NUMERIC;  -- in currency of funding source
66         source_deduction   NUMERIC;  -- in currency of funding source
67         orig_allocated_amt NUMERIC;  -- in currency of funding source
68         allocated_amt      NUMERIC;  -- in currency of fund
69         source             RECORD;
70 BEGIN
71         --
72         -- Sanity checks
73         --
74         IF old_fund IS NULL THEN
75                 RAISE EXCEPTION 'acq.transfer_fund: old fund id is NULL';
76         END IF;
77         --
78         IF old_amount IS NULL THEN
79                 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer is NULL';
80         END IF;
81         --
82         -- The new fund and its amount must be both NULL or both not NULL.
83         --
84         IF new_fund IS NOT NULL AND new_amount IS NULL THEN
85                 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer to receiving fund is NULL';
86         END IF;
87         --
88         IF new_fund IS NULL AND new_amount IS NOT NULL THEN
89                 RAISE EXCEPTION 'acq.transfer_fund: receiving fund is NULL, its amount is not NULL';
90         END IF;
91         --
92         IF user_id IS NULL THEN
93                 RAISE EXCEPTION 'acq.transfer_fund: user id is NULL';
94         END IF;
95         --
96         -- Initialize the amounts to be transferred, each denominated
97         -- in the currency of its respective fund.  They will be
98         -- reduced on each iteration of the loop.
99         --
100         old_remaining := old_amount;
101         new_remaining := new_amount;
102         --
103         -- RAISE NOTICE 'Transferring % in fund % to % in fund %',
104         --      old_amount, old_fund, new_amount, new_fund;
105         --
106         -- Get the currency types of the old and new funds.
107         --
108         SELECT
109                 currency_type
110         INTO
111                 old_fund_currency
112         FROM
113                 acq.fund
114         WHERE
115                 id = old_fund;
116         --
117         IF old_fund_currency IS NULL THEN
118                 RAISE EXCEPTION 'acq.transfer_fund: old fund id % is not defined', old_fund;
119         END IF;
120         --
121         IF new_fund IS NOT NULL THEN
122                 SELECT
123                         currency_type,
124                         active
125                 INTO
126                         new_fund_currency,
127                         new_fund_active
128                 FROM
129                         acq.fund
130                 WHERE
131                         id = new_fund;
132                 --
133                 IF new_fund_currency IS NULL THEN
134                         RAISE EXCEPTION 'acq.transfer_fund: new fund id % is not defined', new_fund;
135                 ELSIF NOT new_fund_active THEN
136                         --
137                         -- No point in putting money into a fund from whence you can't spend it
138                         --
139                         RAISE EXCEPTION 'acq.transfer_fund: new fund id % is inactive', new_fund;
140                 END IF;
141                 --
142                 IF new_amount = old_amount THEN
143                         same_currency := true;
144                         currency_ratio := 1;
145                 ELSE
146                         --
147                         -- We'll have to translate currency between funds.  We presume that
148                         -- the calling code has already applied an appropriate exchange rate,
149                         -- so we'll apply the same conversion to each sub-transfer.
150                         --
151                         same_currency := false;
152                         currency_ratio := new_amount / old_amount;
153                 END IF;
154         END IF;
155         --
156         -- Identify the funding source(s) from which we want to transfer the money.
157         -- The principle is that we want to transfer the newest money first, because
158         -- we spend the oldest money first.  The priority for spending is defined
159         -- by a sort of the view acq.ordered_funding_source_credit.
160         --
161         FOR source in
162                 SELECT
163                         ofsc.id,
164                         ofsc.funding_source,
165                         ofsc.amount,
166                         ofsc.amount * acq.exchange_ratio( fs.currency_type, old_fund_currency )
167                                 AS converted_amt,
168                         fs.currency_type
169                 FROM
170                         acq.ordered_funding_source_credit AS ofsc,
171                         acq.funding_source fs
172                 WHERE
173                         ofsc.funding_source = fs.id
174                         and ofsc.funding_source IN
175                         (
176                                 SELECT funding_source
177                                 FROM acq.fund_allocation
178                                 WHERE fund = old_fund
179                         )
180                         -- and
181                         -- (
182                         --      ofsc.funding_source = funding_source_in
183                         --      OR funding_source_in IS NULL
184                         -- )
185                 ORDER BY
186                         ofsc.sort_priority desc,
187                         ofsc.sort_date desc,
188                         ofsc.id desc
189         LOOP
190                 --
191                 -- Determine how much money the old fund got from this funding source,
192                 -- denominated in the currency types of the source and of the fund.
193                 -- This result may reflect transfers from previous iterations.
194                 --
195                 SELECT
196                         COALESCE( sum( amount ), 0 ),
197                         COALESCE( sum( amount )
198                                 * acq.exchange_ratio( source.currency_type, old_fund_currency ), 0 )
199                 INTO
200                         orig_allocated_amt,     -- in currency of the source
201                         allocated_amt           -- in currency of the old fund
202                 FROM
203                         acq.fund_allocation
204                 WHERE
205                         fund = old_fund
206                         and funding_source = source.funding_source;
207                 --      
208                 -- Determine how much to transfer from this credit, in the currency
209                 -- of the fund.   Begin with the amount remaining to be attributed:
210                 --
211                 curr_old_amt := old_remaining;
212                 --
213                 -- Can't attribute more than was allocated from the fund:
214                 --
215                 IF curr_old_amt > allocated_amt THEN
216                         curr_old_amt := allocated_amt;
217                 END IF;
218                 --
219                 -- Can't attribute more than the amount of the current credit:
220                 --
221                 IF curr_old_amt > source.converted_amt THEN
222                         curr_old_amt := source.converted_amt;
223                 END IF;
224                 --
225                 curr_old_amt := trunc( curr_old_amt, 2 );
226                 --
227                 old_remaining := old_remaining - curr_old_amt;
228                 --
229                 -- Determine the amount to be deducted, if any,
230                 -- from the old allocation.
231                 --
232                 IF old_remaining > 0 THEN
233                         --
234                         -- In this case we're using the whole allocation, so use that
235                         -- amount directly instead of applying a currency translation
236                         -- and thereby inviting round-off errors.
237                         --
238                         source_deduction := - curr_old_amt;
239                 ELSE 
240                         source_deduction := trunc(
241                                 ( - curr_old_amt ) *
242                                         acq.exchange_ratio( old_fund_currency, source.currency_type ),
243                                 2 );
244                 END IF;
245                 --
246                 IF source_deduction <> 0 THEN
247                         --
248                         -- Insert negative allocation for old fund in fund_allocation,
249                         -- converted into the currency of the funding source
250                         --
251                         INSERT INTO acq.fund_allocation (
252                                 funding_source,
253                                 fund,
254                                 amount,
255                                 allocator,
256                                 note
257                         ) VALUES (
258                                 source.funding_source,
259                                 old_fund,
260                                 source_deduction,
261                                 user_id,
262                                 'Transfer to fund ' || new_fund
263                         );
264                 END IF;
265                 --
266                 IF new_fund IS NOT NULL THEN
267                         --
268                         -- Determine how much to add to the new fund, in
269                         -- its currency, and how much remains to be added:
270                         --
271                         IF same_currency THEN
272                                 curr_new_amt := curr_old_amt;
273                         ELSE
274                                 IF old_remaining = 0 THEN
275                                         --
276                                         -- This is the last iteration, so nothing should be left
277                                         --
278                                         curr_new_amt := new_remaining;
279                                         new_remaining := 0;
280                                 ELSE
281                                         curr_new_amt := trunc( curr_old_amt * currency_ratio, 2 );
282                                         new_remaining := new_remaining - curr_new_amt;
283                                 END IF;
284                         END IF;
285                         --
286                         -- Determine how much to add, if any,
287                         -- to the new fund's allocation.
288                         --
289                         IF old_remaining > 0 THEN
290                                 --
291                                 -- In this case we're using the whole allocation, so use that amount
292                                 -- amount directly instead of applying a currency translation and
293                                 -- thereby inviting round-off errors.
294                                 --
295                                 source_addition := curr_new_amt;
296                         ELSIF source.currency_type = old_fund_currency THEN
297                                 --
298                                 -- In this case we don't need a round trip currency translation,
299                                 -- thereby inviting round-off errors:
300                                 --
301                                 source_addition := curr_old_amt;
302                         ELSE 
303                                 source_addition := trunc(
304                                         curr_new_amt *
305                                                 acq.exchange_ratio( new_fund_currency, source.currency_type ),
306                                         2 );
307                         END IF;
308                         --
309                         IF source_addition <> 0 THEN
310                                 --
311                                 -- Insert positive allocation for new fund in fund_allocation,
312                                 -- converted to the currency of the founding source
313                                 --
314                                 INSERT INTO acq.fund_allocation (
315                                         funding_source,
316                                         fund,
317                                         amount,
318                                         allocator,
319                                         note
320                                 ) VALUES (
321                                         source.funding_source,
322                                         new_fund,
323                                         source_addition,
324                                         user_id,
325                                         'Transfer from fund ' || old_fund
326                                 );
327                         END IF;
328                 END IF;
329                 --
330                 IF trunc( curr_old_amt, 2 ) <> 0
331                 OR trunc( curr_new_amt, 2 ) <> 0 THEN
332                         --
333                         -- Insert row in fund_transfer, using amounts in the currency of the funds
334                         --
335                         INSERT INTO acq.fund_transfer (
336                                 src_fund,
337                                 src_amount,
338                                 dest_fund,
339                                 dest_amount,
340                                 transfer_user,
341                                 note,
342                                 funding_source_credit
343                         ) VALUES (
344                                 old_fund,
345                                 trunc( curr_old_amt, 2 ),
346                                 new_fund,
347                                 trunc( curr_new_amt, 2 ),
348                                 user_id,
349                                 xfer_note,
350                                 source.id
351                         );
352                 END IF;
353                 --
354                 if old_remaining <= 0 THEN
355                         EXIT;                   -- Nothing more to be transferred
356                 END IF;
357         END LOOP;
358 END;
359 $$ LANGUAGE plpgsql;
360
361 COMMIT;