3 INSERT INTO config.upgrade_log (version) VALUES ('0147'); -- Scott McKellar
5 CREATE OR REPLACE FUNCTION acq.transfer_fund(
7 old_amount IN NUMERIC, -- in currency of old fund
9 new_amount IN NUMERIC, -- in currency of new fund
11 xfer_note IN TEXT -- to be recorded in acq.fund_transfer
12 -- ,funding_source_in IN INT -- if user wants to specify a funding source (see notes)
14 /* -------------------------------------------------------------------------------
16 Function to transfer money from one fund to another.
18 A transfer is represented as a pair of entries in acq.fund_allocation, with a
19 negative amount for the old (losing) fund and a positive amount for the new
20 (gaining) fund. In some cases there may be more than one such pair of entries
21 in order to pull the money from different funding sources, or more specifically
22 from different funding source credits. For each such pair there is also an
23 entry in acq.fund_transfer.
25 Since funding_source is a non-nullable column in acq.fund_allocation, we must
26 choose a funding source for the transferred money to come from. This choice
27 must meet two constraints, so far as possible:
29 1. The amount transferred from a given funding source must not exceed the
30 amount allocated to the old fund by the funding source. To that end we
31 compare the amount being transferred to the amount allocated.
33 2. We shouldn't transfer money that has already been spent or encumbered, as
34 defined by the funding attribution process. We attribute expenses to the
35 oldest funding source credits first. In order to avoid transferring that
36 attributed money, we reverse the priority, transferring from the newest funding
37 source credits first. There can be no guarantee that this approach will
38 avoid overcommitting a fund, but no other approach can do any better.
40 In this context the age of a funding source credit is defined by the
41 deadline_date for credits with deadline_dates, and by the effective_date for
42 credits without deadline_dates, with the proviso that credits with deadline_dates
43 are all considered "older" than those without.
47 In the signature for this function, there is one last parameter commented out,
48 named "funding_source_in". Correspondingly, the WHERE clause for the query
49 driving the main loop has an OR clause commented out, which references the
50 funding_source_in parameter.
52 If these lines are uncommented, this function will allow the user optionally to
53 restrict a fund transfer to a specified funding source. If the source
54 parameter is left NULL, then there will be no such restriction.
56 ------------------------------------------------------------------------------- */
58 same_currency BOOLEAN;
59 currency_ratio NUMERIC;
60 old_fund_currency TEXT;
61 old_remaining NUMERIC; -- in currency of old fund
62 new_fund_currency TEXT;
63 new_fund_active BOOLEAN;
64 new_remaining NUMERIC; -- in currency of new fund
65 curr_old_amt NUMERIC; -- in currency of old fund
66 curr_new_amt NUMERIC; -- in currency of new fund
67 source_addition NUMERIC; -- in currency of funding source
68 source_deduction NUMERIC; -- in currency of funding source
69 orig_allocated_amt NUMERIC; -- in currency of funding source
70 allocated_amt NUMERIC; -- in currency of fund
76 IF old_fund IS NULL THEN
77 RAISE EXCEPTION 'acq.transfer_fund: old fund id is NULL';
80 IF old_amount IS NULL THEN
81 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer is NULL';
84 -- The new fund and its amount must be both NULL or both not NULL.
86 IF new_fund IS NOT NULL AND new_amount IS NULL THEN
87 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer to receiving fund is NULL';
90 IF new_fund IS NULL AND new_amount IS NOT NULL THEN
91 RAISE EXCEPTION 'acq.transfer_fund: receiving fund is NULL, its amount is not NULL';
94 IF user_id IS NULL THEN
95 RAISE EXCEPTION 'acq.transfer_fund: user id is NULL';
98 -- Initialize the amounts to be transferred, each denominated
99 -- in the currency of its respective fund. They will be
100 -- reduced on each iteration of the loop.
102 old_remaining := old_amount;
103 new_remaining := new_amount;
105 -- RAISE NOTICE 'Transferring % in fund % to % in fund %',
106 -- old_amount, old_fund, new_amount, new_fund;
108 -- Get the currency types of the old and new funds.
119 IF old_fund_currency IS NULL THEN
120 RAISE EXCEPTION 'acq.transfer_fund: old fund id % is not defined', old_fund;
123 IF new_fund IS NOT NULL THEN
135 IF new_fund_currency IS NULL THEN
136 RAISE EXCEPTION 'acq.transfer_fund: new fund id % is not defined', new_fund;
137 ELSIF NOT new_fund_active THEN
139 -- No point in putting money into a fund from whence you can't spend it
141 RAISE EXCEPTION 'acq.transfer_fund: new fund id % is inactive', new_fund;
144 IF new_amount = old_amount THEN
145 same_currency := true;
149 -- We'll have to translate currency between funds. We presume that
150 -- the calling code has already applied an appropriate exchange rate,
151 -- so we'll apply the same conversion to each sub-transfer.
153 same_currency := false;
154 currency_ratio := new_amount / old_amount;
158 -- Identify the funding source(s) from which we want to transfer the money.
159 -- The principle is that we want to transfer the newest money first, because
160 -- we spend the oldest money first. The priority for spending is defined
161 -- by a sort of the view acq.ordered_funding_source_credit.
168 ofsc.amount * acq.exchange_ratio( fs.currency_type, old_fund_currency )
172 acq.ordered_funding_source_credit AS ofsc,
173 acq.funding_source fs
175 ofsc.funding_source = fs.id
176 and ofsc.funding_source IN
178 SELECT funding_source
179 FROM acq.fund_allocation
180 WHERE fund = old_fund
184 -- ofsc.funding_source = funding_source_in
185 -- OR funding_source_in IS NULL
188 ofsc.sort_priority desc,
193 -- Determine how much money the old fund got from this funding source,
194 -- denominated in the currency types of the source and of the fund.
195 -- This result may reflect transfers from previous iterations.
198 COALESCE( sum( amount ), 0 ),
199 COALESCE( sum( amount )
200 * acq.exchange_ratio( source.currency_type, old_fund_currency ), 0 )
202 orig_allocated_amt, -- in currency of the source
203 allocated_amt -- in currency of the old fund
208 and funding_source = source.funding_source;
210 -- Determine how much to transfer from this credit, in the currency
211 -- of the fund. Begin with the amount remaining to be attributed:
213 curr_old_amt := old_remaining;
215 -- Can't attribute more than was allocated from the fund:
217 IF curr_old_amt > allocated_amt THEN
218 curr_old_amt := allocated_amt;
221 -- Can't attribute more than the amount of the current credit:
223 IF curr_old_amt > source.converted_amt THEN
224 curr_old_amt := source.converted_amt;
227 curr_old_amt := trunc( curr_old_amt, 2 );
229 old_remaining := old_remaining - curr_old_amt;
231 -- Determine the amount to be deducted, if any,
232 -- from the old allocation.
234 IF old_remaining > 0 THEN
236 -- In this case we're using the whole allocation, so use that
237 -- amount directly instead of applying a currency translation
238 -- and thereby inviting round-off errors.
240 source_deduction := - orig_allocated_amt;
242 source_deduction := trunc(
244 acq.exchange_ratio( old_fund_currency, source.currency_type ),
248 IF source_deduction <> 0 THEN
250 -- Insert negative allocation for old fund in fund_allocation,
251 -- converted into the currency of the funding source
253 INSERT INTO acq.fund_allocation (
260 source.funding_source,
264 'Transfer to fund ' || new_fund
268 IF new_fund IS NOT NULL THEN
270 -- Determine how much to add to the new fund, in
271 -- its currency, and how much remains to be added:
273 IF same_currency THEN
274 curr_new_amt := curr_old_amt;
276 IF old_remaining = 0 THEN
278 -- This is the last iteration, so nothing should be left
280 curr_new_amt := new_remaining;
283 curr_new_amt := trunc( curr_old_amt * currency_ratio, 2 );
284 new_remaining := new_remaining - curr_new_amt;
288 -- Determine how much to add, if any,
289 -- to the new fund's allocation.
291 IF old_remaining > 0 THEN
293 -- In this case we're using the whole allocation, so use that amount
294 -- amount directly instead of applying a currency translation and
295 -- thereby inviting round-off errors.
297 source_addition := orig_allocated_amt;
298 ELSIF source.currency_type = old_fund_currency THEN
300 -- In this case we don't need a round trip currency translation,
301 -- thereby inviting round-off errors:
303 source_addition := curr_old_amt;
305 source_addition := trunc(
307 acq.exchange_ratio( new_fund_currency, source.currency_type ),
311 IF source_addition <> 0 THEN
313 -- Insert positive allocation for new fund in fund_allocation,
314 -- converted to the currency of the founding source
316 INSERT INTO acq.fund_allocation (
323 source.funding_source,
327 'Transfer from fund ' || old_fund
332 IF trunc( curr_old_amt, 2 ) <> 0
333 OR trunc( curr_new_amt, 2 ) <> 0 THEN
335 -- Insert row in fund_transfer, using amounts in the currency of the funds
337 INSERT INTO acq.fund_transfer (
344 funding_source_credit
347 trunc( curr_old_amt, 2 ),
349 trunc( curr_new_amt, 2 ),
356 if old_remaining <= 0 THEN
357 EXIT; -- Nothing more to be transferred