3 CREATE OR REPLACE FUNCTION acq.transfer_fund(
5 old_amount IN NUMERIC, -- in currency of old fund
7 new_amount IN NUMERIC, -- in currency of new fund
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)
12 /* -------------------------------------------------------------------------------
14 Function to transfer money from one fund to another.
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.
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:
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.
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.
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.
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.
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.
54 ------------------------------------------------------------------------------- */
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
74 IF old_fund IS NULL THEN
75 RAISE EXCEPTION 'acq.transfer_fund: old fund id is NULL';
78 IF old_amount IS NULL THEN
79 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer is NULL';
82 -- The new fund and its amount must be both NULL or both not NULL.
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';
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';
92 IF user_id IS NULL THEN
93 RAISE EXCEPTION 'acq.transfer_fund: user id is NULL';
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.
100 old_remaining := old_amount;
101 new_remaining := new_amount;
103 -- RAISE NOTICE 'Transferring % in fund % to % in fund %',
104 -- old_amount, old_fund, new_amount, new_fund;
106 -- Get the currency types of the old and new funds.
117 IF old_fund_currency IS NULL THEN
118 RAISE EXCEPTION 'acq.transfer_fund: old fund id % is not defined', old_fund;
121 IF new_fund IS NOT NULL THEN
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
137 -- No point in putting money into a fund from whence you can't spend it
139 RAISE EXCEPTION 'acq.transfer_fund: new fund id % is inactive', new_fund;
142 IF new_amount = old_amount THEN
143 same_currency := true;
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.
151 same_currency := false;
152 currency_ratio := new_amount / old_amount;
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.
166 ofsc.amount * acq.exchange_ratio( fs.currency_type, old_fund_currency )
170 acq.ordered_funding_source_credit AS ofsc,
171 acq.funding_source fs
173 ofsc.funding_source = fs.id
174 and ofsc.funding_source IN
176 SELECT funding_source
177 FROM acq.fund_allocation
178 WHERE fund = old_fund
182 -- ofsc.funding_source = funding_source_in
183 -- OR funding_source_in IS NULL
186 ofsc.sort_priority desc,
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.
196 COALESCE( sum( amount ), 0 ),
197 COALESCE( sum( amount )
198 * acq.exchange_ratio( source.currency_type, old_fund_currency ), 0 )
200 orig_allocated_amt, -- in currency of the source
201 allocated_amt -- in currency of the old fund
206 and funding_source = source.funding_source;
208 -- Determine how much to transfer from this credit, in the currency
209 -- of the fund. Begin with the amount remaining to be attributed:
211 curr_old_amt := old_remaining;
213 -- Can't attribute more than was allocated from the fund:
215 IF curr_old_amt > allocated_amt THEN
216 curr_old_amt := allocated_amt;
219 -- Can't attribute more than the amount of the current credit:
221 IF curr_old_amt > source.converted_amt THEN
222 curr_old_amt := source.converted_amt;
225 curr_old_amt := trunc( curr_old_amt, 2 );
227 old_remaining := old_remaining - curr_old_amt;
229 -- Determine the amount to be deducted, if any,
230 -- from the old allocation.
232 IF old_remaining > 0 THEN
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.
238 source_deduction := - curr_old_amt;
240 source_deduction := trunc(
242 acq.exchange_ratio( old_fund_currency, source.currency_type ),
246 IF source_deduction <> 0 THEN
248 -- Insert negative allocation for old fund in fund_allocation,
249 -- converted into the currency of the funding source
251 INSERT INTO acq.fund_allocation (
258 source.funding_source,
262 'Transfer to fund ' || new_fund
266 IF new_fund IS NOT NULL THEN
268 -- Determine how much to add to the new fund, in
269 -- its currency, and how much remains to be added:
271 IF same_currency THEN
272 curr_new_amt := curr_old_amt;
274 IF old_remaining = 0 THEN
276 -- This is the last iteration, so nothing should be left
278 curr_new_amt := new_remaining;
281 curr_new_amt := trunc( curr_old_amt * currency_ratio, 2 );
282 new_remaining := new_remaining - curr_new_amt;
286 -- Determine how much to add, if any,
287 -- to the new fund's allocation.
289 IF old_remaining > 0 THEN
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.
295 source_addition := curr_new_amt;
296 ELSIF source.currency_type = old_fund_currency THEN
298 -- In this case we don't need a round trip currency translation,
299 -- thereby inviting round-off errors:
301 source_addition := curr_old_amt;
303 source_addition := trunc(
305 acq.exchange_ratio( new_fund_currency, source.currency_type ),
309 IF source_addition <> 0 THEN
311 -- Insert positive allocation for new fund in fund_allocation,
312 -- converted to the currency of the founding source
314 INSERT INTO acq.fund_allocation (
321 source.funding_source,
325 'Transfer from fund ' || old_fund
330 IF trunc( curr_old_amt, 2 ) <> 0
331 OR trunc( curr_new_amt, 2 ) <> 0 THEN
333 -- Insert row in fund_transfer, using amounts in the currency of the funds
335 INSERT INTO acq.fund_transfer (
342 funding_source_credit
345 trunc( curr_old_amt, 2 ),
347 trunc( curr_new_amt, 2 ),
354 if old_remaining <= 0 THEN
355 EXIT; -- Nothing more to be transferred