3 INSERT INTO config.upgrade_log (version) VALUES ('0143'); -- Scott McKellar
5 CREATE TABLE acq.debit_attribution (
6 id INT NOT NULL PRIMARY KEY,
7 fund_debit INT NOT NULL
8 REFERENCES acq.fund_debit
9 DEFERRABLE INITIALLY DEFERRED,
10 debit_amount NUMERIC NOT NULL,
11 funding_source_credit INT REFERENCES acq.funding_source_credit
12 DEFERRABLE INITIALLY DEFERRED,
16 CREATE INDEX acq_attribution_debit_idx
17 ON acq.debit_attribution( fund_debit );
19 CREATE INDEX acq_attribution_credit_idx
20 ON acq.debit_attribution( funding_source_credit );
22 -- The following three types are intended for internal use
23 -- by the acq.attribute_debits() function.
25 -- For a combination of fund and funding_source: How much that source
26 -- allocated to that fund, and how much is left.
27 CREATE TYPE acq.fund_source_balance AS
30 source INT, -- funding source id
31 amount NUMERIC, -- original total allocation
32 balance NUMERIC -- what's left
35 -- For a fund: a list of funding_source_credits to which
36 -- the fund's debits can be attributed.
37 CREATE TYPE acq.fund_credits AS
40 credit_count INT, -- number of entries in the following array
41 credit INT [] -- funding source credits from which a fund may draw
44 -- For a funding source credit: the funding source, the currency type
45 -- of the funding source, and the current balance.
46 CREATE TYPE acq.funding_source_credit_balance AS
48 credit_id INT, -- if for funding source credit
49 funding_source INT, -- id of funding source
50 currency_type TEXT, -- currency type of funding source
51 amount NUMERIC, -- original amount of credit
52 balance NUMERIC -- how much is left
55 CREATE OR REPLACE FUNCTION acq.attribute_debits() RETURNS VOID AS $$
57 Function to attribute expenditures and encumbrances to funding source credits,
58 and thereby to funding sources.
60 Read the debits in chonological order, attributing each one to one or
61 more funding source credits. Constraints:
63 1. Don't attribute more to a credit than the amount of the credit.
65 2. For a given fund, don't attribute more to a funding source than the
66 source has allocated to that fund.
68 3. Attribute debits to credits with deadlines before attributing them to
69 credits without deadlines. Otherwise attribute to the earliest credits
70 first, based on the deadline date when present, or on the effective date
71 when there is no deadline. Use funding_source_credit.id as a tie-breaker.
72 This ordering is defined by an ORDER BY clause on the view
73 acq.ordered_funding_source_credit.
75 Start by truncating the table acq.debit_attribution. Then insert a row
76 into that table for each attribution. If a debit cannot be fully
77 attributed, insert a row for the unattributable balance, with the
78 funding_source_credit and credit_amount columns NULL.
81 curr_fund_src_bal acq.fund_source_balance;
82 fund_source_balance acq.fund_source_balance [];
83 curr_fund_cr_list acq.fund_credits;
84 fund_credit_list acq.fund_credits [];
85 curr_cr_bal acq.funding_source_credit_balance;
86 cr_bal acq.funding_source_credit_balance[];
87 crl_max INT; -- Number of entries in fund_credits[]
88 fcr_max INT; -- Number of entries in a credit list
89 fsa_max INT; -- Number of entries in fund_source_balance[]
90 fscr_max INT; -- Number of entries in cr_bal[]
96 -- Used exclusively in the main loop:
99 debit_balance NUMERIC; -- amount left to attribute for current debit
100 conv_debit_balance NUMERIC; -- debit balance in currency of the fund
101 attr_amount NUMERIC; -- amount being attributed, in currency of debit
102 conv_attr_amount NUMERIC; -- amount being attributed, in currency of source
103 conv_cred_balance NUMERIC; -- credit_balance in the currency of the fund
104 conv_alloc_balance NUMERIC; -- allocated balance in the currency of the fund
108 curr_cred_x INT; -- index of current credit in cr_bal[]
109 curr_fund_src_x INT; -- index of current credit in fund_source_balance[]
110 attrib_count INT; -- populates id of acq.debit_attribution
113 -- Load an array. For each combination of fund and funding source, load an
114 -- entry with the total amount allocated to that fund by that source. This
115 -- sum may reflect transfers as well as original allocations. The balance
116 -- is initially equal to the original amount.
122 funding_source AS source,
123 sum( amount ) AS amount
135 IF fsa.amount > 0 THEN
137 -- Add this fund/source combination to the list
139 curr_fund_src_bal.fund := fsa.fund;
140 curr_fund_src_bal.source := fsa.source;
141 curr_fund_src_bal.amount := fsa.amount;
142 curr_fund_src_bal.balance := fsa.amount;
144 fsa_max := fsa_max + 1;
145 fund_source_balance[ fsa_max ] := curr_fund_src_bal;
149 -------------------------------------------------------------------------------
151 -- Load another array. For each fund, load a list of funding
152 -- source credits from which that fund can get money.
157 FROM acq.fund_allocation
159 LOOP -- Loop over the funds
161 -- Initialize the array entry
163 curr_fund_cr_list.fund := fc.fund;
165 curr_fund_cr_list.credit := NULL;
167 -- Make a list of the funding source credits
168 -- applicable to this fund
174 acq.ordered_funding_source_credit AS ofsc
176 ofsc.funding_source IN
178 SELECT funding_source
179 FROM acq.fund_allocation
186 LOOP -- Add each credit to the list
187 fcr_max := fcr_max + 1;
188 curr_fund_cr_list.credit[ fcr_max ] := sc.id;
192 -- If there are any credits applicable to this fund,
193 -- add the credit list to the list of credit lists.
196 curr_fund_cr_list.credit_count := fcr_max;
197 crl_max := crl_max + 1;
198 fund_credit_list[ crl_max ] := curr_fund_cr_list;
202 -------------------------------------------------------------------------------
204 -- Load yet another array. This one is a list of funding source credits, with
215 acq.ordered_funding_source_credit AS ofsc,
216 acq.funding_source fs
218 ofsc.funding_source = fs.id
225 curr_cr_bal.credit_id := cr.id;
226 curr_cr_bal.funding_source := cr.funding_source;
227 curr_cr_bal.amount := cr.amount;
228 curr_cr_bal.balance := cr.amount;
229 curr_cr_bal.currency_type := cr.currency_type;
231 fscr_max := fscr_max + 1;
232 cr_bal[ fscr_max ] := curr_cr_bal;
235 -------------------------------------------------------------------------------
237 -- Now that we have loaded the lookup tables: loop through the debits,
238 -- attributing each one to one or more funding source credits.
240 truncate table acq.debit_attribution;
258 debit_balance := deb.amount;
260 -- Find the list of credits applicable to this fund
263 FOR i in 1 .. crl_max LOOP
264 IF fund_credit_list[ i ].fund = deb.fund THEN
265 curr_fund_cr_list := fund_credit_list[ i ];
271 -- If we didn't find an entry for this fund, then there are no applicable
272 -- funding sources for this fund, and the debit is hence unattributable.
274 -- If we did find an entry for this fund, then we have a list of funding source
275 -- credits that we can apply to it. Go through that list and attribute the
276 -- debit accordingly.
280 -- For each applicable credit
282 FOR i in 1 .. curr_fund_cr_list.credit_count LOOP
284 -- Find the entry in the credit list for this credit. If you find it but
285 -- it has a zero balance, it's not useful, so treat it as if you didn't
288 credit_found := false;
289 FOR j in 1 .. fscr_max LOOP
290 IF cr_bal[ j ].credit_id = curr_fund_cr_list.credit[i] THEN
291 curr_cr_bal := cr_bal[ j ];
292 IF curr_cr_bal.balance <> 0 THEN
294 credit_found := true;
300 IF NOT credit_found THEN
302 -- This credit is not usable; try the next one.
307 -- At this point we have an applicable credit with some money left.
308 -- Now see if the relevant funding_source has any money left.
310 -- Search the fund/source list for an entry with this combination
311 -- of fund and source. If you find such an entry, but it has a zero
312 -- balance, then it's not useful, so treat it as unfound.
314 alloc_found := false;
315 FOR j in 1 .. fsa_max LOOP
316 IF fund_source_balance[ j ].fund = deb.fund
317 AND fund_source_balance[ j ].source = curr_cr_bal.funding_source THEN
318 curr_fund_src_bal := fund_source_balance[ j ];
319 IF curr_fund_src_bal.balance <> 0 THEN
320 curr_fund_src_x := j;
327 IF NOT alloc_found THEN
329 -- This fund/source doesn't exist is already exhausted,
330 -- so we can't use this credit. Go on to the next on.
335 -- Convert the available balances to the currency of the fund
337 conv_alloc_balance := curr_fund_src_bal.balance * acq.exchange_ratio(
338 curr_cr_bal.currency_type, deb.currency_type );
339 conv_cred_balance := curr_cr_bal.balance * acq.exchange_ratio(
340 curr_cr_bal.currency_type, deb.currency_type );
342 -- Determine how much we can attribute to this credit: the minimum
343 -- of the debit amount, the fund/source balance, and the
346 attr_amount := debit_balance;
347 IF attr_amount > conv_alloc_balance THEN
348 attr_amount := conv_alloc_balance;
350 IF attr_amount > conv_cred_balance THEN
351 attr_amount := conv_cred_balance;
354 -- Convert the amount of the attribution to the
355 -- currency of the funding source.
357 conv_attr_amount := attr_amount * acq.exchange_ratio(
358 deb.currency_type, curr_cr_bal.currency_type );
360 -- Insert a row to record the attribution
362 attrib_count := attrib_count + 1;
363 INSERT INTO acq.debit_attribution (
367 funding_source_credit,
373 curr_cr_bal.credit_id,
377 -- Subtract the attributed amount from the various balances
379 debit_balance := debit_balance - attr_amount;
381 curr_fund_src_bal.balance := curr_fund_src_bal.balance - conv_attr_amount;
382 fund_source_balance[ curr_fund_src_x ] := curr_fund_src_bal;
383 IF curr_fund_src_bal.balance <= 0 THEN
385 -- This allocation is exhausted. Take it out of the list
386 -- so that we don't waste time looking at it again.
388 FOR i IN curr_fund_src_x .. fsa_max - 1 LOOP
389 fund_source_balance[ i ] := fund_source_balance[ i + 1 ];
391 fund_source_balance[ fsa_max ] := NULL;
392 fsa_max := fsa_max - 1;
395 curr_cr_bal.balance := curr_cr_bal.balance - conv_attr_amount;
396 cr_bal[ curr_cred_x ] := curr_cr_bal;
397 IF curr_cr_bal.balance <= 0 THEN
399 -- This funding source credit is exhausted. Take it out of
400 -- the list so that we don't waste time looking at it again.
402 FOR i IN curr_cred_x .. fscr_max - 1 LOOP
403 cr_bal[ i ] := cr_bal[ i + 1 ];
405 cr_bal[ fscr_max ] := NULL;
406 fscr_max := fscr_max - 1;
409 -- Are we done with this debit yet?
411 IF debit_balance <= 0 THEN
412 EXIT; -- We've fully attributed this debit; stop looking at credits.
414 END LOOP; -- End of loop over applicable credits
417 IF debit_balance <> 0 THEN
419 -- We weren't able to attribute this debit, or at least not
420 -- all of it. Insert a row for the unattributed balance.
422 attrib_count := attrib_count + 1;
423 INSERT INTO acq.debit_attribution (
427 funding_source_credit,
437 END LOOP; -- End of loop over debits
439 $$ LANGUAGE 'plpgsql';