3 INSERT INTO config.upgrade_log (version) VALUES ('0149'); -- Scott McKellar
5 CREATE OR REPLACE FUNCTION acq.attribute_debits() RETURNS VOID AS $$
7 Function to attribute expenditures and encumbrances to funding source credits,
8 and thereby to funding sources.
10 Read the debits in chonological order, attributing each one to one or
11 more funding source credits. Constraints:
13 1. Don't attribute more to a credit than the amount of the credit.
15 2. For a given fund, don't attribute more to a funding source than the
16 source has allocated to that fund.
18 3. Attribute debits to credits with deadlines before attributing them to
19 credits without deadlines. Otherwise attribute to the earliest credits
20 first, based on the deadline date when present, or on the effective date
21 when there is no deadline. Use funding_source_credit.id as a tie-breaker.
22 This ordering is defined by an ORDER BY clause on the view
23 acq.ordered_funding_source_credit.
25 Start by truncating the table acq.debit_attribution. Then insert a row
26 into that table for each attribution. If a debit cannot be fully
27 attributed, insert a row for the unattributable balance, with the
28 funding_source_credit and credit_amount columns NULL.
31 curr_fund_source_bal RECORD;
32 seqno INT; -- sequence num for credits applicable to a fund
33 fund_credit RECORD; -- current row in temp t_fund_credit table
34 fc RECORD; -- used for loading t_fund_credit table
35 sc RECORD; -- used for loading t_fund_credit table
37 -- Used exclusively in the main loop:
39 deb RECORD; -- current row from acq.fund_debit table
40 curr_credit_bal RECORD; -- current row from temp t_credit table
41 debit_balance NUMERIC; -- amount left to attribute for current debit
42 conv_debit_balance NUMERIC; -- debit balance in currency of the fund
43 attr_amount NUMERIC; -- amount being attributed, in currency of debit
44 conv_attr_amount NUMERIC; -- amount being attributed, in currency of source
45 conv_cred_balance NUMERIC; -- credit_balance in the currency of the fund
46 conv_alloc_balance NUMERIC; -- allocated balance in the currency of the fund
47 attrib_count INT; -- populates id of acq.debit_attribution
50 -- Load a temporary table. For each combination of fund and funding source,
51 -- load an entry with the total amount allocated to that fund by that source.
52 -- This sum may reflect transfers as well as original allocations. We will
53 -- reduce this balance whenever we attribute debits to it.
55 CREATE TEMP TABLE t_fund_source_bal
59 funding_source AS source,
60 sum( amount ) AS balance
69 CREATE INDEX t_fund_source_bal_idx
70 ON t_fund_source_bal( fund, source );
71 -------------------------------------------------------------------------------
73 -- Load another temporary table. For each fund, load zero or more
74 -- funding source credits from which that fund can get money.
76 CREATE TEMP TABLE t_fund_credit (
84 FROM acq.fund_allocation
86 LOOP -- Loop over the funds
92 acq.ordered_funding_source_credit AS ofsc
94 ofsc.funding_source IN
97 FROM acq.fund_allocation
104 LOOP -- Add each credit to the list
105 INSERT INTO t_fund_credit (
114 --RAISE NOTICE 'Fund % credit %', fc.fund, sc.id;
116 END LOOP; -- Loop over credits for a given fund
117 END LOOP; -- Loop over funds
119 CREATE INDEX t_fund_credit_idx
120 ON t_fund_credit( fund, seq );
121 -------------------------------------------------------------------------------
123 -- Load yet another temporary table. This one is a list of funding source
124 -- credits, with their balances. We shall reduce those balances as we
125 -- attribute debits to them.
127 CREATE TEMP TABLE t_credit
131 fsc.funding_source AS source,
132 fsc.amount AS balance,
133 fs.currency_type AS currency_type
135 acq.funding_source_credit AS fsc,
136 acq.funding_source fs
138 fsc.funding_source = fs.id
141 CREATE INDEX t_credit_idx
142 ON t_credit( credit );
144 -------------------------------------------------------------------------------
146 -- Now that we have loaded the lookup tables: loop through the debits,
147 -- attributing each one to one or more funding source credits.
149 truncate table acq.debit_attribution;
167 --RAISE NOTICE 'Debit %, fund %', deb.id, deb.fund;
169 debit_balance := deb.amount;
171 -- Loop over the funding source credits that are eligible
172 -- to pay for this debit
184 --RAISE NOTICE ' Examining credit %', fund_credit.credit;
186 -- Look up the balance for this credit. If it's zero, then
187 -- it's not useful, so treat it as if you didn't find it.
188 -- (Actually there shouldn't be any zero balances in the table,
189 -- but we check just to make sure.)
195 credit = fund_credit.credit
198 IF curr_credit_bal IS NULL THEN
200 -- This credit is exhausted; try the next one.
206 -- At this point we have an applicable credit with some money left.
207 -- Now see if the relevant funding_source has any money left.
209 -- Look up the balance of the allocation for this combination of
210 -- fund and source. If you find such an entry, but it has a zero
211 -- balance, then it's not useful, so treat it as unfound.
212 -- (Actually there shouldn't be any zero balances in the table,
213 -- but we check just to make sure.)
216 INTO curr_fund_source_bal
217 FROM t_fund_source_bal
220 AND source = curr_credit_bal.source
223 IF curr_fund_source_bal IS NULL THEN
225 -- This fund/source doesn't exist or is already exhausted,
226 -- so we can't use this credit. Go on to the next one.
231 -- Convert the available balances to the currency of the fund
233 conv_alloc_balance := curr_fund_source_bal.balance * acq.exchange_ratio(
234 curr_credit_bal.currency_type, deb.currency_type );
235 conv_cred_balance := curr_credit_bal.balance * acq.exchange_ratio(
236 curr_credit_bal.currency_type, deb.currency_type );
238 -- Determine how much we can attribute to this credit: the minimum
239 -- of the debit amount, the fund/source balance, and the
242 --RAISE NOTICE ' deb bal %', debit_balance;
243 --RAISE NOTICE ' source % balance %', curr_credit_bal.source, conv_alloc_balance;
244 --RAISE NOTICE ' credit % balance %', curr_credit_bal.credit, conv_cred_balance;
246 conv_attr_amount := NULL;
247 attr_amount := debit_balance;
249 IF attr_amount > conv_alloc_balance THEN
250 attr_amount := conv_alloc_balance;
251 conv_attr_amount := curr_fund_source_bal.balance;
253 IF attr_amount > conv_cred_balance THEN
254 attr_amount := conv_cred_balance;
255 conv_attr_amount := curr_credit_bal.balance;
258 -- If we're attributing all of one of the balances, then that's how
259 -- much we will deduct from the balances, and we already captured
260 -- that amount above. Otherwise we must convert the amount of the
261 -- attribution from the currency of the fund back to the currency of
262 -- the funding source.
264 IF conv_attr_amount IS NULL THEN
265 conv_attr_amount := attr_amount * acq.exchange_ratio(
266 deb.currency_type, curr_credit_bal.currency_type );
269 -- Insert a row to record the attribution
271 attrib_count := attrib_count + 1;
272 INSERT INTO acq.debit_attribution (
276 funding_source_credit,
282 curr_credit_bal.credit,
286 -- Subtract the attributed amount from the various balances
288 debit_balance := debit_balance - attr_amount;
289 curr_fund_source_bal.balance := curr_fund_source_bal.balance - conv_attr_amount;
291 IF curr_fund_source_bal.balance <= 0 THEN
293 -- This allocation is exhausted. Delete it so
294 -- that we don't waste time looking at it again.
296 DELETE FROM t_fund_source_bal
298 fund = curr_fund_source_bal.fund
299 AND source = curr_fund_source_bal.source;
301 UPDATE t_fund_source_bal
302 SET balance = balance - conv_attr_amount
304 fund = curr_fund_source_bal.fund
305 AND source = curr_fund_source_bal.source;
308 IF curr_credit_bal.balance <= 0 THEN
310 -- This funding source credit is exhausted. Delete it
311 -- so that we don't waste time looking at it again.
313 --DELETE FROM t_credit
315 -- credit = curr_credit_bal.credit;
317 DELETE FROM t_fund_credit
319 credit = curr_credit_bal.credit;
322 SET balance = curr_credit_bal.balance
324 credit = curr_credit_bal.credit;
327 -- Are we done with this debit yet?
329 IF debit_balance <= 0 THEN
330 EXIT; -- We've fully attributed this debit; stop looking at credits.
332 END LOOP; -- End loop over credits
334 IF debit_balance <> 0 THEN
336 -- We weren't able to attribute this debit, or at least not
337 -- all of it. Insert a row for the unattributed balance.
339 attrib_count := attrib_count + 1;
340 INSERT INTO acq.debit_attribution (
344 funding_source_credit,
354 END LOOP; -- End of loop over debits
356 $$ LANGUAGE 'plpgsql';