From e9e1b573713034a419e85bf68fced0c4e86d1c07 Mon Sep 17 00:00:00 2001 From: scottmk Date: Fri, 29 Jan 2010 19:46:40 +0000 Subject: [PATCH] Add machinery for attributing debits to funding source credits, and thereby to funding sources. 1. New table, acq.debit_attribution. 2. Create three new types, to be used internally by: 3. New function acq.attribute_debits(). M Open-ILS/src/sql/Pg/200.schema.acq.sql M Open-ILS/src/sql/Pg/002.schema.config.sql A Open-ILS/src/sql/Pg/upgrade/0143.schema.debit_attribution.sql M Open-ILS/examples/fm_IDL.xml git-svn-id: svn://svn.open-ils.org/ILS/trunk@15398 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 16 + Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/200.schema.acq.sql | 438 +++++++++++++++++ .../upgrade/0143.schema.debit_attribution.sql | 441 ++++++++++++++++++ 4 files changed, 896 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0143.schema.debit_attribution.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 0a2324f135..79d3af1d75 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -5485,6 +5485,22 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 9772acac56..786729709f 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -51,7 +51,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0142'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0143'); -- Scott McKellar CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index a5c035dc75..48bfcfb988 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -568,6 +568,25 @@ CREATE TABLE acq.edi_account ( -- similar tables can extend remote_account -- We need a UNIQUE constraint here also, to support the FK from acq.provider.edi_default ALTER TABLE acq.edi_account ADD CONSTRAINT acq_edi_account_id_unique UNIQUE (id); +-- Note below that the primary key is NOT a SERIAL type. We will periodically truncate and rebuild +-- the table, assigning ids programmatically instead of using a sequence. +CREATE TABLE acq.debit_attribution ( + id INT NOT NULL PRIMARY KEY, + fund_debit INT NOT NULL + REFERENCES acq.fund_debit + DEFERRABLE INITIALLY DEFERRED, + debit_amount NUMERIC NOT NULL, + funding_source_credit INT REFERENCES acq.funding_source_credit + DEFERRABLE INITIALLY DEFERRED, + credit_amount NUMERIC +); + +CREATE INDEX acq_attribution_debit_idx + ON acq.debit_attribution( fund_debit ); + +CREATE INDEX acq_attribution_credit_idx + ON acq.debit_attribution( funding_source_credit ); + -- Functions CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text); @@ -821,6 +840,425 @@ BEGIN END; $$ LANGUAGE plpgsql; +-- The following three types are intended for internal use +-- by the acq.attribute_debits() function. + +-- For a combination of fund and funding_source: How much that source +-- allocated to that fund, and how much is left. +CREATE TYPE acq.fund_source_balance AS +( + fund INT, -- fund id + source INT, -- funding source id + amount NUMERIC, -- original total allocation + balance NUMERIC -- what's left +); + +-- For a fund: a list of funding_source_credits to which +-- the fund's debits can be attributed. +CREATE TYPE acq.fund_credits AS +( + fund INT, -- fund id + credit_count INT, -- number of entries in the following array + credit INT [] -- funding source credits from which a fund may draw +); + +-- For a funding source credit: the funding source, the currency type +-- of the funding source, and the current balance. +CREATE TYPE acq.funding_source_credit_balance AS +( + credit_id INT, -- if for funding source credit + funding_source INT, -- id of funding source + currency_type TEXT, -- currency type of funding source + amount NUMERIC, -- original amount of credit + balance NUMERIC -- how much is left +); + +CREATE OR REPLACE FUNCTION acq.attribute_debits() RETURNS VOID AS $$ +/* + Function to attribute expenditures and encumbrances to funding source credits, + and thereby to funding sources. + + Read the debits in chonological order, attributing each one to one or + more funding source credits. Constraints: + + 1. Don't attribute more to a credit than the amount of the credit. + + 2. For a given fund, don't attribute more to a funding source than the + source has allocated to that fund. + + 3. Attribute debits to credits with deadlines before attributing them to + credits without deadlines. Otherwise attribute to the earliest credits + first, based on the deadline date when present, or on the effective date + when there is no deadline. Use funding_source_credit.id as a tie-breaker. + This ordering is defined by an ORDER BY clause on the view + acq.ordered_funding_source_credit. + + Start by truncating the table acq.debit_attribution. Then insert a row + into that table for each attribution. If a debit cannot be fully + attributed, insert a row for the unattributable balance, with the + funding_source_credit and credit_amount columns NULL. +*/ +DECLARE + curr_fund_src_bal acq.fund_source_balance; + fund_source_balance acq.fund_source_balance []; + curr_fund_cr_list acq.fund_credits; + fund_credit_list acq.fund_credits []; + curr_cr_bal acq.funding_source_credit_balance; + cr_bal acq.funding_source_credit_balance[]; + crl_max INT; -- Number of entries in fund_credits[] + fcr_max INT; -- Number of entries in a credit list + fsa_max INT; -- Number of entries in fund_source_balance[] + fscr_max INT; -- Number of entries in cr_bal[] + fsa RECORD; + fc RECORD; + sc RECORD; + cr RECORD; + -- + -- Used exclusively in the main loop: + -- + deb RECORD; + debit_balance NUMERIC; -- amount left to attribute for current debit + conv_debit_balance NUMERIC; -- debit balance in currency of the fund + attr_amount NUMERIC; -- amount being attributed, in currency of debit + conv_attr_amount NUMERIC; -- amount being attributed, in currency of source + conv_cred_balance NUMERIC; -- credit_balance in the currency of the fund + conv_alloc_balance NUMERIC; -- allocated balance in the currency of the fund + fund_found BOOL; + credit_found BOOL; + alloc_found BOOL; + curr_cred_x INT; -- index of current credit in cr_bal[] + curr_fund_src_x INT; -- index of current credit in fund_source_balance[] + attrib_count INT; -- populates id of acq.debit_attribution +BEGIN + -- + -- Load an array. For each combination of fund and funding source, load an + -- entry with the total amount allocated to that fund by that source. This + -- sum may reflect transfers as well as original allocations. The balance + -- is initially equal to the original amount. + -- + fsa_max := 0; + FOR fsa IN + SELECT + fund AS fund, + funding_source AS source, + sum( amount ) AS amount + FROM + acq.fund_allocation + GROUP BY + fund, + funding_source + HAVING + sum( amount ) <> 0 + ORDER BY + fund, + funding_source + LOOP + IF fsa.amount > 0 THEN + -- + -- Add this fund/source combination to the list + -- + curr_fund_src_bal.fund := fsa.fund; + curr_fund_src_bal.source := fsa.source; + curr_fund_src_bal.amount := fsa.amount; + curr_fund_src_bal.balance := fsa.amount; + -- + fsa_max := fsa_max + 1; + fund_source_balance[ fsa_max ] := curr_fund_src_bal; + END IF; + -- + END LOOP; + ------------------------------------------------------------------------------- + -- + -- Load another array. For each fund, load a list of funding + -- source credits from which that fund can get money. + -- + crl_max := 0; + FOR fc IN + SELECT DISTINCT fund + FROM acq.fund_allocation + ORDER BY fund + LOOP -- Loop over the funds + -- + -- Initialize the array entry + -- + curr_fund_cr_list.fund := fc.fund; + fcr_max := 0; + curr_fund_cr_list.credit := NULL; + -- + -- Make a list of the funding source credits + -- applicable to this fund + -- + FOR sc IN + SELECT + ofsc.id + FROM + acq.ordered_funding_source_credit AS ofsc + WHERE + ofsc.funding_source IN + ( + SELECT funding_source + FROM acq.fund_allocation + WHERE fund = fc.fund + ) + ORDER BY + ofsc.sort_priority, + ofsc.sort_date, + ofsc.id + LOOP -- Add each credit to the list + fcr_max := fcr_max + 1; + curr_fund_cr_list.credit[ fcr_max ] := sc.id; + -- + END LOOP; + -- + -- If there are any credits applicable to this fund, + -- add the credit list to the list of credit lists. + -- + IF fcr_max > 0 THEN + curr_fund_cr_list.credit_count := fcr_max; + crl_max := crl_max + 1; + fund_credit_list[ crl_max ] := curr_fund_cr_list; + END IF; + -- + END LOOP; + ------------------------------------------------------------------------------- + -- + -- Load yet another array. This one is a list of funding source credits, with + -- their balances. + -- + fscr_max := 0; + FOR cr in + SELECT + ofsc.id, + ofsc.funding_source, + ofsc.amount, + fs.currency_type + FROM + acq.ordered_funding_source_credit AS ofsc, + acq.funding_source fs + WHERE + ofsc.funding_source = fs.id + ORDER BY + ofsc.sort_priority, + ofsc.sort_date, + ofsc.id + LOOP + -- + curr_cr_bal.credit_id := cr.id; + curr_cr_bal.funding_source := cr.funding_source; + curr_cr_bal.amount := cr.amount; + curr_cr_bal.balance := cr.amount; + curr_cr_bal.currency_type := cr.currency_type; + -- + fscr_max := fscr_max + 1; + cr_bal[ fscr_max ] := curr_cr_bal; + END LOOP; + -- + ------------------------------------------------------------------------------- + -- + -- Now that we have loaded the lookup tables: loop through the debits, + -- attributing each one to one or more funding source credits. + -- + truncate table acq.debit_attribution; + -- + attrib_count := 0; + FOR deb in + SELECT + fd.id, + fd.fund, + fd.amount, + f.currency_type, + fd.encumbrance + FROM + acq.fund_debit fd, + acq.fund f + WHERE + fd.fund = f.id + ORDER BY + id + LOOP + debit_balance := deb.amount; + -- + -- Find the list of credits applicable to this fund + -- + fund_found := false; + FOR i in 1 .. crl_max LOOP + IF fund_credit_list[ i ].fund = deb.fund THEN + curr_fund_cr_list := fund_credit_list[ i ]; + fund_found := true; + exit; + END IF; + END LOOP; + -- + -- If we didn't find an entry for this fund, then there are no applicable + -- funding sources for this fund, and the debit is hence unattributable. + -- + -- If we did find an entry for this fund, then we have a list of funding source + -- credits that we can apply to it. Go through that list and attribute the + -- debit accordingly. + -- + IF fund_found THEN + -- + -- For each applicable credit + -- + FOR i in 1 .. curr_fund_cr_list.credit_count LOOP + -- + -- Find the entry in the credit list for this credit. If you find it but + -- it has a zero balance, it's not useful, so treat it as if you didn't + -- find it. + -- + credit_found := false; + FOR j in 1 .. fscr_max LOOP + IF cr_bal[ j ].credit_id = curr_fund_cr_list.credit[i] THEN + curr_cr_bal := cr_bal[ j ]; + IF curr_cr_bal.balance <> 0 THEN + curr_cred_x := j; + credit_found := true; + END IF; + EXIT; + END IF; + END LOOP; + -- + IF NOT credit_found THEN + -- + -- This credit is not usable; try the next one. + -- + CONTINUE; + END IF; + -- + -- At this point we have an applicable credit with some money left. + -- Now see if the relevant funding_source has any money left. + -- + -- Search the fund/source list for an entry with this combination + -- of fund and source. If you find such an entry, but it has a zero + -- balance, then it's not useful, so treat it as unfound. + -- + alloc_found := false; + FOR j in 1 .. fsa_max LOOP + IF fund_source_balance[ j ].fund = deb.fund + AND fund_source_balance[ j ].source = curr_cr_bal.funding_source THEN + curr_fund_src_bal := fund_source_balance[ j ]; + IF curr_fund_src_bal.balance <> 0 THEN + curr_fund_src_x := j; + alloc_found := true; + END IF; + EXIT; + END IF; + END LOOP; + -- + IF NOT alloc_found THEN + -- + -- This fund/source doesn't exist is already exhausted, + -- so we can't use this credit. Go on to the next on. + -- + CONTINUE; + END IF; + -- + -- Convert the available balances to the currency of the fund + -- + conv_alloc_balance := curr_fund_src_bal.balance * acq.exchange_ratio( + curr_cr_bal.currency_type, deb.currency_type ); + conv_cred_balance := curr_cr_bal.balance * acq.exchange_ratio( + curr_cr_bal.currency_type, deb.currency_type ); + -- + -- Determine how much we can attribute to this credit: the minimum + -- of the debit amount, the fund/source balance, and the + -- credit balance + -- + attr_amount := debit_balance; + IF attr_amount > conv_alloc_balance THEN + attr_amount := conv_alloc_balance; + END IF; + IF attr_amount > conv_cred_balance THEN + attr_amount := conv_cred_balance; + END IF; + -- + -- Convert the amount of the attribution to the + -- currency of the funding source. + -- + conv_attr_amount := attr_amount * acq.exchange_ratio( + deb.currency_type, curr_cr_bal.currency_type ); + -- + -- Insert a row to record the attribution + -- + attrib_count := attrib_count + 1; + INSERT INTO acq.debit_attribution ( + id, + fund_debit, + debit_amount, + funding_source_credit, + credit_amount + ) VALUES ( + attrib_count, + deb.id, + attr_amount, + curr_cr_bal.credit_id, + conv_attr_amount + ); + -- + -- Subtract the attributed amount from the various balances + -- + debit_balance := debit_balance - attr_amount; + -- + curr_fund_src_bal.balance := curr_fund_src_bal.balance - conv_attr_amount; + fund_source_balance[ curr_fund_src_x ] := curr_fund_src_bal; + IF curr_fund_src_bal.balance <= 0 THEN + -- + -- This allocation is exhausted. Take it out of the list + -- so that we don't waste time looking at it again. + -- + FOR i IN curr_fund_src_x .. fsa_max - 1 LOOP + fund_source_balance[ i ] := fund_source_balance[ i + 1 ]; + END LOOP; + fund_source_balance[ fsa_max ] := NULL; + fsa_max := fsa_max - 1; + END IF; + -- + curr_cr_bal.balance := curr_cr_bal.balance - conv_attr_amount; + cr_bal[ curr_cred_x ] := curr_cr_bal; + IF curr_cr_bal.balance <= 0 THEN + -- + -- This funding source credit is exhausted. Take it out of + -- the list so that we don't waste time looking at it again. + -- + FOR i IN curr_cred_x .. fscr_max - 1 LOOP + cr_bal[ i ] := cr_bal[ i + 1 ]; + END LOOP; + cr_bal[ fscr_max ] := NULL; + fscr_max := fscr_max - 1; + END IF; + -- + -- Are we done with this debit yet? + -- + IF debit_balance <= 0 THEN + EXIT; -- We've fully attributed this debit; stop looking at credits. + END IF; + END LOOP; -- End of loop over applicable credits + END IF; + -- + IF debit_balance <> 0 THEN + -- + -- We weren't able to attribute this debit, or at least not + -- all of it. Insert a row for the unattributed balance. + -- + attrib_count := attrib_count + 1; + INSERT INTO acq.debit_attribution ( + id, + fund_debit, + debit_amount, + funding_source_credit, + credit_amount + ) VALUES ( + attrib_count, + deb.id, + debit_balance, + NULL, + NULL + ); + END IF; + END LOOP; -- End of loop over debits +END; +$$ LANGUAGE 'plpgsql'; + CREATE OR REPLACE VIEW acq.funding_source_credit_total AS SELECT funding_source, SUM(amount) AS amount diff --git a/Open-ILS/src/sql/Pg/upgrade/0143.schema.debit_attribution.sql b/Open-ILS/src/sql/Pg/upgrade/0143.schema.debit_attribution.sql new file mode 100644 index 0000000000..58ff496325 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0143.schema.debit_attribution.sql @@ -0,0 +1,441 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0143'); -- Scott McKellar + +CREATE TABLE acq.debit_attribution ( + id INT NOT NULL PRIMARY KEY, + fund_debit INT NOT NULL + REFERENCES acq.fund_debit + DEFERRABLE INITIALLY DEFERRED, + debit_amount NUMERIC NOT NULL, + funding_source_credit INT REFERENCES acq.funding_source_credit + DEFERRABLE INITIALLY DEFERRED, + credit_amount NUMERIC +); + +CREATE INDEX acq_attribution_debit_idx + ON acq.debit_attribution( fund_debit ); + +CREATE INDEX acq_attribution_credit_idx + ON acq.debit_attribution( funding_source_credit ); + +-- The following three types are intended for internal use +-- by the acq.attribute_debits() function. + +-- For a combination of fund and funding_source: How much that source +-- allocated to that fund, and how much is left. +CREATE TYPE acq.fund_source_balance AS +( + fund INT, -- fund id + source INT, -- funding source id + amount NUMERIC, -- original total allocation + balance NUMERIC -- what's left +); + +-- For a fund: a list of funding_source_credits to which +-- the fund's debits can be attributed. +CREATE TYPE acq.fund_credits AS +( + fund INT, -- fund id + credit_count INT, -- number of entries in the following array + credit INT [] -- funding source credits from which a fund may draw +); + +-- For a funding source credit: the funding source, the currency type +-- of the funding source, and the current balance. +CREATE TYPE acq.funding_source_credit_balance AS +( + credit_id INT, -- if for funding source credit + funding_source INT, -- id of funding source + currency_type TEXT, -- currency type of funding source + amount NUMERIC, -- original amount of credit + balance NUMERIC -- how much is left +); + +CREATE OR REPLACE FUNCTION acq.attribute_debits() RETURNS VOID AS $$ +/* + Function to attribute expenditures and encumbrances to funding source credits, + and thereby to funding sources. + + Read the debits in chonological order, attributing each one to one or + more funding source credits. Constraints: + + 1. Don't attribute more to a credit than the amount of the credit. + + 2. For a given fund, don't attribute more to a funding source than the + source has allocated to that fund. + + 3. Attribute debits to credits with deadlines before attributing them to + credits without deadlines. Otherwise attribute to the earliest credits + first, based on the deadline date when present, or on the effective date + when there is no deadline. Use funding_source_credit.id as a tie-breaker. + This ordering is defined by an ORDER BY clause on the view + acq.ordered_funding_source_credit. + + Start by truncating the table acq.debit_attribution. Then insert a row + into that table for each attribution. If a debit cannot be fully + attributed, insert a row for the unattributable balance, with the + funding_source_credit and credit_amount columns NULL. +*/ +DECLARE + curr_fund_src_bal acq.fund_source_balance; + fund_source_balance acq.fund_source_balance []; + curr_fund_cr_list acq.fund_credits; + fund_credit_list acq.fund_credits []; + curr_cr_bal acq.funding_source_credit_balance; + cr_bal acq.funding_source_credit_balance[]; + crl_max INT; -- Number of entries in fund_credits[] + fcr_max INT; -- Number of entries in a credit list + fsa_max INT; -- Number of entries in fund_source_balance[] + fscr_max INT; -- Number of entries in cr_bal[] + fsa RECORD; + fc RECORD; + sc RECORD; + cr RECORD; + -- + -- Used exclusively in the main loop: + -- + deb RECORD; + debit_balance NUMERIC; -- amount left to attribute for current debit + conv_debit_balance NUMERIC; -- debit balance in currency of the fund + attr_amount NUMERIC; -- amount being attributed, in currency of debit + conv_attr_amount NUMERIC; -- amount being attributed, in currency of source + conv_cred_balance NUMERIC; -- credit_balance in the currency of the fund + conv_alloc_balance NUMERIC; -- allocated balance in the currency of the fund + fund_found BOOL; + credit_found BOOL; + alloc_found BOOL; + curr_cred_x INT; -- index of current credit in cr_bal[] + curr_fund_src_x INT; -- index of current credit in fund_source_balance[] + attrib_count INT; -- populates id of acq.debit_attribution +BEGIN + -- + -- Load an array. For each combination of fund and funding source, load an + -- entry with the total amount allocated to that fund by that source. This + -- sum may reflect transfers as well as original allocations. The balance + -- is initially equal to the original amount. + -- + fsa_max := 0; + FOR fsa IN + SELECT + fund AS fund, + funding_source AS source, + sum( amount ) AS amount + FROM + acq.fund_allocation + GROUP BY + fund, + funding_source + HAVING + sum( amount ) <> 0 + ORDER BY + fund, + funding_source + LOOP + IF fsa.amount > 0 THEN + -- + -- Add this fund/source combination to the list + -- + curr_fund_src_bal.fund := fsa.fund; + curr_fund_src_bal.source := fsa.source; + curr_fund_src_bal.amount := fsa.amount; + curr_fund_src_bal.balance := fsa.amount; + -- + fsa_max := fsa_max + 1; + fund_source_balance[ fsa_max ] := curr_fund_src_bal; + END IF; + -- + END LOOP; + ------------------------------------------------------------------------------- + -- + -- Load another array. For each fund, load a list of funding + -- source credits from which that fund can get money. + -- + crl_max := 0; + FOR fc IN + SELECT DISTINCT fund + FROM acq.fund_allocation + ORDER BY fund + LOOP -- Loop over the funds + -- + -- Initialize the array entry + -- + curr_fund_cr_list.fund := fc.fund; + fcr_max := 0; + curr_fund_cr_list.credit := NULL; + -- + -- Make a list of the funding source credits + -- applicable to this fund + -- + FOR sc IN + SELECT + ofsc.id + FROM + acq.ordered_funding_source_credit AS ofsc + WHERE + ofsc.funding_source IN + ( + SELECT funding_source + FROM acq.fund_allocation + WHERE fund = fc.fund + ) + ORDER BY + ofsc.sort_priority, + ofsc.sort_date, + ofsc.id + LOOP -- Add each credit to the list + fcr_max := fcr_max + 1; + curr_fund_cr_list.credit[ fcr_max ] := sc.id; + -- + END LOOP; + -- + -- If there are any credits applicable to this fund, + -- add the credit list to the list of credit lists. + -- + IF fcr_max > 0 THEN + curr_fund_cr_list.credit_count := fcr_max; + crl_max := crl_max + 1; + fund_credit_list[ crl_max ] := curr_fund_cr_list; + END IF; + -- + END LOOP; + ------------------------------------------------------------------------------- + -- + -- Load yet another array. This one is a list of funding source credits, with + -- their balances. + -- + fscr_max := 0; + FOR cr in + SELECT + ofsc.id, + ofsc.funding_source, + ofsc.amount, + fs.currency_type + FROM + acq.ordered_funding_source_credit AS ofsc, + acq.funding_source fs + WHERE + ofsc.funding_source = fs.id + ORDER BY + ofsc.sort_priority, + ofsc.sort_date, + ofsc.id + LOOP + -- + curr_cr_bal.credit_id := cr.id; + curr_cr_bal.funding_source := cr.funding_source; + curr_cr_bal.amount := cr.amount; + curr_cr_bal.balance := cr.amount; + curr_cr_bal.currency_type := cr.currency_type; + -- + fscr_max := fscr_max + 1; + cr_bal[ fscr_max ] := curr_cr_bal; + END LOOP; + -- + ------------------------------------------------------------------------------- + -- + -- Now that we have loaded the lookup tables: loop through the debits, + -- attributing each one to one or more funding source credits. + -- + truncate table acq.debit_attribution; + -- + attrib_count := 0; + FOR deb in + SELECT + fd.id, + fd.fund, + fd.amount, + f.currency_type, + fd.encumbrance + FROM + acq.fund_debit fd, + acq.fund f + WHERE + fd.fund = f.id + ORDER BY + id + LOOP + debit_balance := deb.amount; + -- + -- Find the list of credits applicable to this fund + -- + fund_found := false; + FOR i in 1 .. crl_max LOOP + IF fund_credit_list[ i ].fund = deb.fund THEN + curr_fund_cr_list := fund_credit_list[ i ]; + fund_found := true; + exit; + END IF; + END LOOP; + -- + -- If we didn't find an entry for this fund, then there are no applicable + -- funding sources for this fund, and the debit is hence unattributable. + -- + -- If we did find an entry for this fund, then we have a list of funding source + -- credits that we can apply to it. Go through that list and attribute the + -- debit accordingly. + -- + IF fund_found THEN + -- + -- For each applicable credit + -- + FOR i in 1 .. curr_fund_cr_list.credit_count LOOP + -- + -- Find the entry in the credit list for this credit. If you find it but + -- it has a zero balance, it's not useful, so treat it as if you didn't + -- find it. + -- + credit_found := false; + FOR j in 1 .. fscr_max LOOP + IF cr_bal[ j ].credit_id = curr_fund_cr_list.credit[i] THEN + curr_cr_bal := cr_bal[ j ]; + IF curr_cr_bal.balance <> 0 THEN + curr_cred_x := j; + credit_found := true; + END IF; + EXIT; + END IF; + END LOOP; + -- + IF NOT credit_found THEN + -- + -- This credit is not usable; try the next one. + -- + CONTINUE; + END IF; + -- + -- At this point we have an applicable credit with some money left. + -- Now see if the relevant funding_source has any money left. + -- + -- Search the fund/source list for an entry with this combination + -- of fund and source. If you find such an entry, but it has a zero + -- balance, then it's not useful, so treat it as unfound. + -- + alloc_found := false; + FOR j in 1 .. fsa_max LOOP + IF fund_source_balance[ j ].fund = deb.fund + AND fund_source_balance[ j ].source = curr_cr_bal.funding_source THEN + curr_fund_src_bal := fund_source_balance[ j ]; + IF curr_fund_src_bal.balance <> 0 THEN + curr_fund_src_x := j; + alloc_found := true; + END IF; + EXIT; + END IF; + END LOOP; + -- + IF NOT alloc_found THEN + -- + -- This fund/source doesn't exist is already exhausted, + -- so we can't use this credit. Go on to the next on. + -- + CONTINUE; + END IF; + -- + -- Convert the available balances to the currency of the fund + -- + conv_alloc_balance := curr_fund_src_bal.balance * acq.exchange_ratio( + curr_cr_bal.currency_type, deb.currency_type ); + conv_cred_balance := curr_cr_bal.balance * acq.exchange_ratio( + curr_cr_bal.currency_type, deb.currency_type ); + -- + -- Determine how much we can attribute to this credit: the minimum + -- of the debit amount, the fund/source balance, and the + -- credit balance + -- + attr_amount := debit_balance; + IF attr_amount > conv_alloc_balance THEN + attr_amount := conv_alloc_balance; + END IF; + IF attr_amount > conv_cred_balance THEN + attr_amount := conv_cred_balance; + END IF; + -- + -- Convert the amount of the attribution to the + -- currency of the funding source. + -- + conv_attr_amount := attr_amount * acq.exchange_ratio( + deb.currency_type, curr_cr_bal.currency_type ); + -- + -- Insert a row to record the attribution + -- + attrib_count := attrib_count + 1; + INSERT INTO acq.debit_attribution ( + id, + fund_debit, + debit_amount, + funding_source_credit, + credit_amount + ) VALUES ( + attrib_count, + deb.id, + attr_amount, + curr_cr_bal.credit_id, + conv_attr_amount + ); + -- + -- Subtract the attributed amount from the various balances + -- + debit_balance := debit_balance - attr_amount; + -- + curr_fund_src_bal.balance := curr_fund_src_bal.balance - conv_attr_amount; + fund_source_balance[ curr_fund_src_x ] := curr_fund_src_bal; + IF curr_fund_src_bal.balance <= 0 THEN + -- + -- This allocation is exhausted. Take it out of the list + -- so that we don't waste time looking at it again. + -- + FOR i IN curr_fund_src_x .. fsa_max - 1 LOOP + fund_source_balance[ i ] := fund_source_balance[ i + 1 ]; + END LOOP; + fund_source_balance[ fsa_max ] := NULL; + fsa_max := fsa_max - 1; + END IF; + -- + curr_cr_bal.balance := curr_cr_bal.balance - conv_attr_amount; + cr_bal[ curr_cred_x ] := curr_cr_bal; + IF curr_cr_bal.balance <= 0 THEN + -- + -- This funding source credit is exhausted. Take it out of + -- the list so that we don't waste time looking at it again. + -- + FOR i IN curr_cred_x .. fscr_max - 1 LOOP + cr_bal[ i ] := cr_bal[ i + 1 ]; + END LOOP; + cr_bal[ fscr_max ] := NULL; + fscr_max := fscr_max - 1; + END IF; + -- + -- Are we done with this debit yet? + -- + IF debit_balance <= 0 THEN + EXIT; -- We've fully attributed this debit; stop looking at credits. + END IF; + END LOOP; -- End of loop over applicable credits + END IF; + -- + IF debit_balance <> 0 THEN + -- + -- We weren't able to attribute this debit, or at least not + -- all of it. Insert a row for the unattributed balance. + -- + attrib_count := attrib_count + 1; + INSERT INTO acq.debit_attribution ( + id, + fund_debit, + debit_amount, + funding_source_credit, + credit_amount + ) VALUES ( + attrib_count, + deb.id, + debit_balance, + NULL, + NULL + ); + END IF; + END LOOP; -- End of loop over debits +END; +$$ LANGUAGE 'plpgsql'; + +COMMIT; -- 2.43.2