From 02aba58d6783cb28e1cd172fb93524a15f896c98 Mon Sep 17 00:00:00 2001 From: scottmk Date: Wed, 3 Feb 2010 18:34:31 +0000 Subject: [PATCH] 1. Back off the function acq.attribute_debits, along with three associated type definitions, for attributing debits to funding source credits. This function used some features not supported by PostgreSQL 8.2. 2. Add some functions for rolling over funds at the end of the year: acq.propagate_funds_by_org_unit() acq.propagate_funds_by_org_tree() acq.rollover_funds_by_org_unit() acq.rollover_funds_by_org_tree() Note that the diffs are misleadingly confusing because of a bunch of spurious matches. 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/0148.schema.acq.rollover-fund.sql git-svn-id: svn://svn.open-ils.org/ILS/trunk@15442 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/200.schema.acq.sql | 829 ++++++++++-------- .../upgrade/0148.schema.acq.rollover-fund.sql | 505 +++++++++++ 3 files changed, 961 insertions(+), 375 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0148.schema.acq.rollover-fund.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index fe9fbb9a1a..a546740fef 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 ('0147'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0148'); -- 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 5fdd40c627..8fd3a5119c 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -1280,424 +1280,505 @@ 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. -*/ +CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit( + old_year INTEGER, + user_id INTEGER, + org_unit_id INTEGER +) RETURNS VOID AS $$ 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; +-- +new_id INT; +old_fund RECORD; +org_found BOOLEAN; +-- +BEGIN -- - -- Used exclusively in the main loop: + -- Sanity checks -- - 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 + IF old_year IS NULL THEN + RAISE EXCEPTION 'Input year argument is NULL'; + ELSIF old_year NOT BETWEEN 2008 and 2200 THEN + RAISE EXCEPTION 'Input year is out of range'; + END IF; + -- + IF user_id IS NULL THEN + RAISE EXCEPTION 'Input user id argument is NULL'; + END IF; -- - -- 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. + IF org_unit_id IS NULL THEN + RAISE EXCEPTION 'Org unit id argument is NULL'; + ELSE + SELECT TRUE INTO org_found + FROM actor.org_unit + WHERE id = org_unit_id; + -- + IF org_found IS NULL THEN + RAISE EXCEPTION 'Org unit id is invalid'; + END IF; + END IF; -- - 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 over the applicable funds + -- + FOR old_fund in SELECT * FROM acq.fund + WHERE + year = old_year + AND propagate + AND org = org_unit_id 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; + BEGIN + INSERT INTO acq.fund ( + org, + name, + year, + currency_type, + code, + rollover, + propagate + ) VALUES ( + old_fund.org, + old_fund.name, + old_year + 1, + old_fund.currency_type, + old_fund.code, + old_fund.rollover, + true + ) + RETURNING id INTO new_id; + EXCEPTION + WHEN unique_violation THEN + --RAISE NOTICE 'Fund % already propagated', old_fund.id; + CONTINUE; + END; + --RAISE NOTICE 'Propagating fund % to fund %', + -- old_fund.code, new_id; + END LOOP; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree( + old_year INTEGER, + user_id INTEGER, + org_unit_id INTEGER +) RETURNS VOID AS $$ +DECLARE +-- +new_id INT; +old_fund RECORD; +org_found BOOLEAN; +-- +BEGIN + -- + -- Sanity checks + -- + IF old_year IS NULL THEN + RAISE EXCEPTION 'Input year argument is NULL'; + ELSIF old_year NOT BETWEEN 2008 and 2200 THEN + RAISE EXCEPTION 'Input year is out of range'; + END IF; + -- + IF user_id IS NULL THEN + RAISE EXCEPTION 'Input user id argument is NULL'; + END IF; + -- + IF org_unit_id IS NULL THEN + RAISE EXCEPTION 'Org unit id argument is NULL'; + ELSE + SELECT TRUE INTO org_found + FROM actor.org_unit + WHERE id = org_unit_id; -- + IF org_found IS NULL THEN + RAISE EXCEPTION 'Org unit id is invalid'; + END IF; + END IF; + -- + -- Loop over the applicable funds + -- + FOR old_fund in SELECT * FROM acq.fund + WHERE + year = old_year + AND propagate + AND org in ( + SELECT id FROM actor.org_unit_descendants( org_unit_id ) + ) + LOOP + BEGIN + INSERT INTO acq.fund ( + org, + name, + year, + currency_type, + code, + rollover, + propagate + ) VALUES ( + old_fund.org, + old_fund.name, + old_year + 1, + old_fund.currency_type, + old_fund.code, + old_fund.rollover, + true + ) + RETURNING id INTO new_id; + EXCEPTION + WHEN unique_violation THEN + --RAISE NOTICE 'Fund % already propagated', old_fund.id; + CONTINUE; + END; + --RAISE NOTICE 'Propagating fund % to fund %', + -- old_fund.code, new_id; END LOOP; - ------------------------------------------------------------------------------- +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_unit( + old_year INTEGER, + user_id INTEGER, + org_unit_id INTEGER +) RETURNS VOID AS $$ +DECLARE +-- +new_fund INT; +new_year INT := old_year + 1; +org_found BOOL; +xfer_amount NUMERIC; +roll_fund RECORD; +deb RECORD; +detail RECORD; +-- +BEGIN + -- + -- Sanity checks -- - -- Load another array. For each fund, load a list of funding - -- source credits from which that fund can get money. + IF old_year IS NULL THEN + RAISE EXCEPTION 'Input year argument is NULL'; + ELSIF old_year NOT BETWEEN 2008 and 2200 THEN + RAISE EXCEPTION 'Input year is out of range'; + END IF; -- - crl_max := 0; - FOR fc IN - SELECT DISTINCT fund - FROM acq.fund_allocation - ORDER BY fund - LOOP -- Loop over the funds + IF user_id IS NULL THEN + RAISE EXCEPTION 'Input user id argument is NULL'; + END IF; + -- + IF org_unit_id IS NULL THEN + RAISE EXCEPTION 'Org unit id argument is NULL'; + ELSE -- - -- Initialize the array entry + -- Validate the org unit -- - curr_fund_cr_list.fund := fc.fund; - fcr_max := 0; - curr_fund_cr_list.credit := NULL; + SELECT TRUE + INTO org_found + FROM actor.org_unit + WHERE id = org_unit_id; -- - -- Make a list of the funding source credits - -- applicable to this fund + IF org_found IS NULL THEN + RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id; + END IF; + END IF; + -- + -- Loop over the propagable funds to identify the details + -- from the old fund plus the id of the new one, if it exists. + -- + FOR roll_fund in + SELECT + oldf.id AS old_fund, + oldf.org, + oldf.name, + oldf.currency_type, + oldf.code, + oldf.rollover, + newf.id AS new_fund_id + FROM + acq.fund AS oldf + LEFT JOIN acq.fund AS newf + ON ( oldf.code = newf.code ) + WHERE + oldf.org = org_unit_id + and oldf.year = old_year + and oldf.propagate + and newf.year = new_year + LOOP + --RAISE NOTICE 'Processing fund %', roll_fund.old_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; + IF roll_fund.new_fund_id IS NULL THEN -- - END LOOP; + -- The old fund hasn't been propagated yet. Propagate it now. + -- + INSERT INTO acq.fund ( + org, + name, + year, + currency_type, + code, + rollover, + propagate + ) VALUES ( + roll_fund.org, + roll_fund.name, + new_year, + roll_fund.currency_type, + roll_fund.code, + true, + true + ) + RETURNING id INTO new_fund; + ELSE + new_fund = roll_fund.new_fund_id; + END IF; -- - -- If there are any credits applicable to this fund, - -- add the credit list to the list of credit lists. + -- Determine the amount to transfer + -- + SELECT amount + INTO xfer_amount + FROM acq.fund_spent_balance + WHERE fund = roll_fund.old_fund; + -- + IF xfer_amount <> 0 THEN + IF roll_fund.rollover THEN + -- + -- Transfer balance from old fund to new + -- + --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund; + -- + PERFORM acq.transfer_fund( + roll_fund.old_fund, + xfer_amount, + new_fund, + xfer_amount, + user_id, + 'Rollover' + ); + ELSE + -- + -- Transfer balance from old fund to the void + -- + -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund; + -- + PERFORM acq.transfer_fund( + roll_fund.old_fund, + xfer_amount, + NULL, + NULL, + user_id, + 'Rollover' + ); + END IF; + END IF; -- - 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; + IF roll_fund.rollover THEN + -- + -- Move any lineitems from the old fund to the new one + -- where the associated debit is an encumbrance. + -- + -- Any other tables tying expenditure details to funds should + -- receive similar treatment. At this writing there are none. + -- + UPDATE acq.lineitem_detail + SET fund = new_fund + WHERE + fund = roll_fund.old_fund -- this condition may be redundant + AND fund_debit in + ( + SELECT id + FROM acq.fund_debit + WHERE + fund = roll_fund.old_fund + AND encumbrance + ); + -- + -- Move encumbrance debits from the old fund to the new fund + -- + UPDATE acq.fund_debit + SET fund = new_fund + wHERE + fund = roll_fund.old_fund + AND encumbrance; END IF; -- + -- Mark old fund as inactive, now that we've closed it + -- + UPDATE acq.fund + SET active = FALSE + WHERE id = roll_fund.old_fund; END LOOP; - ------------------------------------------------------------------------------- +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree( + old_year INTEGER, + user_id INTEGER, + org_unit_id INTEGER +) RETURNS VOID AS $$ +DECLARE +-- +new_fund INT; +new_year INT := old_year + 1; +org_found BOOL; +xfer_amount NUMERIC; +roll_fund RECORD; +deb RECORD; +detail RECORD; +-- +BEGIN + -- + -- Sanity checks -- - -- Load yet another array. This one is a list of funding source credits, with - -- their balances. + IF old_year IS NULL THEN + RAISE EXCEPTION 'Input year argument is NULL'; + ELSIF old_year NOT BETWEEN 2008 and 2200 THEN + RAISE EXCEPTION 'Input year is out of range'; + END IF; -- - 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 + IF user_id IS NULL THEN + RAISE EXCEPTION 'Input user id argument is NULL'; + END IF; + -- + IF org_unit_id IS NULL THEN + RAISE EXCEPTION 'Org unit id argument is NULL'; + ELSE -- - 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; + -- Validate the org unit -- - fscr_max := fscr_max + 1; - cr_bal[ fscr_max ] := curr_cr_bal; - END LOOP; - -- - ------------------------------------------------------------------------------- + SELECT TRUE + INTO org_found + FROM actor.org_unit + WHERE id = org_unit_id; + -- + IF org_found IS NULL THEN + RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id; + END IF; + END IF; -- - -- 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; + -- Loop over the propagable funds to identify the details + -- from the old fund plus the id of the new one, if it exists. -- - 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 + FOR roll_fund in + SELECT + oldf.id AS old_fund, + oldf.org, + oldf.name, + oldf.currency_type, + oldf.code, + oldf.rollover, + newf.id AS new_fund_id + FROM + acq.fund AS oldf + LEFT JOIN acq.fund AS newf + ON ( oldf.code = newf.code ) + WHERE + oldf.year = old_year + AND oldf.propagate + AND newf.year = new_year + AND oldf.org in ( + SELECT id FROM actor.org_unit_descendants( org_unit_id ) + ) LOOP - debit_balance := deb.amount; - -- - -- Find the list of credits applicable to this fund + --RAISE NOTICE 'Processing fund %', roll_fund.old_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 roll_fund.new_fund_id IS NULL THEN + -- + -- The old fund hasn't been propagated yet. Propagate it now. + -- + INSERT INTO acq.fund ( + org, + name, + year, + currency_type, + code, + rollover, + propagate + ) VALUES ( + roll_fund.org, + roll_fund.name, + new_year, + roll_fund.currency_type, + roll_fund.code, + true, + true + ) + RETURNING id INTO new_fund; + ELSE + new_fund = roll_fund.new_fund_id; + END IF; -- - -- 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. + -- Determine the amount to transfer -- - -- 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. + SELECT amount + INTO xfer_amount + FROM acq.fund_spent_balance + WHERE fund = roll_fund.old_fund; -- - 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 xfer_amount <> 0 THEN + IF roll_fund.rollover THEN -- - 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; + -- Transfer balance from old fund to new -- - -- Convert the available balances to the currency of the fund + --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_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 + PERFORM acq.transfer_fund( + roll_fund.old_fund, + xfer_amount, + new_fund, + xfer_amount, + user_id, + 'Rollover' ); + ELSE -- - -- 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; + -- Transfer balance from old fund to the void -- - -- Are we done with this debit yet? + -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund; -- - 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 + PERFORM acq.transfer_fund( + roll_fund.old_fund, + xfer_amount, + NULL, + NULL, + user_id, + 'Rollover' + ); + END IF; END IF; -- - IF debit_balance <> 0 THEN + IF roll_fund.rollover THEN -- - -- We weren't able to attribute this debit, or at least not - -- all of it. Insert a row for the unattributed balance. + -- Move any lineitems from the old fund to the new one + -- where the associated debit is an encumbrance. -- - 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 - ); + -- Any other tables tying expenditure details to funds should + -- receive similar treatment. At this writing there are none. + -- + UPDATE acq.lineitem_detail + SET fund = new_fund + WHERE + fund = roll_fund.old_fund -- this condition may be redundant + AND fund_debit in + ( + SELECT id + FROM acq.fund_debit + WHERE + fund = roll_fund.old_fund + AND encumbrance + ); + -- + -- Move encumbrance debits from the old fund to the new fund + -- + UPDATE acq.fund_debit + SET fund = new_fund + wHERE + fund = roll_fund.old_fund + AND encumbrance; END IF; - END LOOP; -- End of loop over debits + -- + -- Mark old fund as inactive, now that we've closed it + -- + UPDATE acq.fund + SET active = FALSE + WHERE id = roll_fund.old_fund; + END LOOP; END; -$$ LANGUAGE 'plpgsql'; +$$ LANGUAGE plpgsql; CREATE OR REPLACE VIEW acq.funding_source_credit_total AS SELECT funding_source, diff --git a/Open-ILS/src/sql/Pg/upgrade/0148.schema.acq.rollover-fund.sql b/Open-ILS/src/sql/Pg/upgrade/0148.schema.acq.rollover-fund.sql new file mode 100644 index 0000000000..40f7b911f7 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0148.schema.acq.rollover-fund.sql @@ -0,0 +1,505 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0148'); -- Scott McKellar + +CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit( + old_year INTEGER, + user_id INTEGER, + org_unit_id INTEGER +) RETURNS VOID AS $$ +DECLARE +-- +new_id INT; +old_fund RECORD; +org_found BOOLEAN; +-- +BEGIN + -- + -- Sanity checks + -- + IF old_year IS NULL THEN + RAISE EXCEPTION 'Input year argument is NULL'; + ELSIF old_year NOT BETWEEN 2008 and 2200 THEN + RAISE EXCEPTION 'Input year is out of range'; + END IF; + -- + IF user_id IS NULL THEN + RAISE EXCEPTION 'Input user id argument is NULL'; + END IF; + -- + IF org_unit_id IS NULL THEN + RAISE EXCEPTION 'Org unit id argument is NULL'; + ELSE + SELECT TRUE INTO org_found + FROM actor.org_unit + WHERE id = org_unit_id; + -- + IF org_found IS NULL THEN + RAISE EXCEPTION 'Org unit id is invalid'; + END IF; + END IF; + -- + -- Loop over the applicable funds + -- + FOR old_fund in SELECT * FROM acq.fund + WHERE + year = old_year + AND propagate + AND org = org_unit_id + LOOP + BEGIN + INSERT INTO acq.fund ( + org, + name, + year, + currency_type, + code, + rollover, + propagate + ) VALUES ( + old_fund.org, + old_fund.name, + old_year + 1, + old_fund.currency_type, + old_fund.code, + old_fund.rollover, + true + ) + RETURNING id INTO new_id; + EXCEPTION + WHEN unique_violation THEN + --RAISE NOTICE 'Fund % already propagated', old_fund.id; + CONTINUE; + END; + --RAISE NOTICE 'Propagating fund % to fund %', + -- old_fund.code, new_id; + END LOOP; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree( + old_year INTEGER, + user_id INTEGER, + org_unit_id INTEGER +) RETURNS VOID AS $$ +DECLARE +-- +new_id INT; +old_fund RECORD; +org_found BOOLEAN; +-- +BEGIN + -- + -- Sanity checks + -- + IF old_year IS NULL THEN + RAISE EXCEPTION 'Input year argument is NULL'; + ELSIF old_year NOT BETWEEN 2008 and 2200 THEN + RAISE EXCEPTION 'Input year is out of range'; + END IF; + -- + IF user_id IS NULL THEN + RAISE EXCEPTION 'Input user id argument is NULL'; + END IF; + -- + IF org_unit_id IS NULL THEN + RAISE EXCEPTION 'Org unit id argument is NULL'; + ELSE + SELECT TRUE INTO org_found + FROM actor.org_unit + WHERE id = org_unit_id; + -- + IF org_found IS NULL THEN + RAISE EXCEPTION 'Org unit id is invalid'; + END IF; + END IF; + -- + -- Loop over the applicable funds + -- + FOR old_fund in SELECT * FROM acq.fund + WHERE + year = old_year + AND propagate + AND org in ( + SELECT id FROM actor.org_unit_descendants( org_unit_id ) + ) + LOOP + BEGIN + INSERT INTO acq.fund ( + org, + name, + year, + currency_type, + code, + rollover, + propagate + ) VALUES ( + old_fund.org, + old_fund.name, + old_year + 1, + old_fund.currency_type, + old_fund.code, + old_fund.rollover, + true + ) + RETURNING id INTO new_id; + EXCEPTION + WHEN unique_violation THEN + --RAISE NOTICE 'Fund % already propagated', old_fund.id; + CONTINUE; + END; + --RAISE NOTICE 'Propagating fund % to fund %', + -- old_fund.code, new_id; + END LOOP; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_unit( + old_year INTEGER, + user_id INTEGER, + org_unit_id INTEGER +) RETURNS VOID AS $$ +DECLARE +-- +new_fund INT; +new_year INT := old_year + 1; +org_found BOOL; +xfer_amount NUMERIC; +roll_fund RECORD; +deb RECORD; +detail RECORD; +-- +BEGIN + -- + -- Sanity checks + -- + IF old_year IS NULL THEN + RAISE EXCEPTION 'Input year argument is NULL'; + ELSIF old_year NOT BETWEEN 2008 and 2200 THEN + RAISE EXCEPTION 'Input year is out of range'; + END IF; + -- + IF user_id IS NULL THEN + RAISE EXCEPTION 'Input user id argument is NULL'; + END IF; + -- + IF org_unit_id IS NULL THEN + RAISE EXCEPTION 'Org unit id argument is NULL'; + ELSE + -- + -- Validate the org unit + -- + SELECT TRUE + INTO org_found + FROM actor.org_unit + WHERE id = org_unit_id; + -- + IF org_found IS NULL THEN + RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id; + END IF; + END IF; + -- + -- Loop over the propagable funds to identify the details + -- from the old fund plus the id of the new one, if it exists. + -- + FOR roll_fund in + SELECT + oldf.id AS old_fund, + oldf.org, + oldf.name, + oldf.currency_type, + oldf.code, + oldf.rollover, + newf.id AS new_fund_id + FROM + acq.fund AS oldf + LEFT JOIN acq.fund AS newf + ON ( oldf.code = newf.code ) + WHERE + oldf.org = org_unit_id + and oldf.year = old_year + and oldf.propagate + and newf.year = new_year + LOOP + --RAISE NOTICE 'Processing fund %', roll_fund.old_fund; + -- + IF roll_fund.new_fund_id IS NULL THEN + -- + -- The old fund hasn't been propagated yet. Propagate it now. + -- + INSERT INTO acq.fund ( + org, + name, + year, + currency_type, + code, + rollover, + propagate + ) VALUES ( + roll_fund.org, + roll_fund.name, + new_year, + roll_fund.currency_type, + roll_fund.code, + true, + true + ) + RETURNING id INTO new_fund; + ELSE + new_fund = roll_fund.new_fund_id; + END IF; + -- + -- Determine the amount to transfer + -- + SELECT amount + INTO xfer_amount + FROM acq.fund_spent_balance + WHERE fund = roll_fund.old_fund; + -- + IF xfer_amount <> 0 THEN + IF roll_fund.rollover THEN + -- + -- Transfer balance from old fund to new + -- + --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund; + -- + PERFORM acq.transfer_fund( + roll_fund.old_fund, + xfer_amount, + new_fund, + xfer_amount, + user_id, + 'Rollover' + ); + ELSE + -- + -- Transfer balance from old fund to the void + -- + -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund; + -- + PERFORM acq.transfer_fund( + roll_fund.old_fund, + xfer_amount, + NULL, + NULL, + user_id, + 'Rollover' + ); + END IF; + END IF; + -- + IF roll_fund.rollover THEN + -- + -- Move any lineitems from the old fund to the new one + -- where the associated debit is an encumbrance. + -- + -- Any other tables tying expenditure details to funds should + -- receive similar treatment. At this writing there are none. + -- + UPDATE acq.lineitem_detail + SET fund = new_fund + WHERE + fund = roll_fund.old_fund -- this condition may be redundant + AND fund_debit in + ( + SELECT id + FROM acq.fund_debit + WHERE + fund = roll_fund.old_fund + AND encumbrance + ); + -- + -- Move encumbrance debits from the old fund to the new fund + -- + UPDATE acq.fund_debit + SET fund = new_fund + wHERE + fund = roll_fund.old_fund + AND encumbrance; + END IF; + -- + -- Mark old fund as inactive, now that we've closed it + -- + UPDATE acq.fund + SET active = FALSE + WHERE id = roll_fund.old_fund; + END LOOP; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree( + old_year INTEGER, + user_id INTEGER, + org_unit_id INTEGER +) RETURNS VOID AS $$ +DECLARE +-- +new_fund INT; +new_year INT := old_year + 1; +org_found BOOL; +xfer_amount NUMERIC; +roll_fund RECORD; +deb RECORD; +detail RECORD; +-- +BEGIN + -- + -- Sanity checks + -- + IF old_year IS NULL THEN + RAISE EXCEPTION 'Input year argument is NULL'; + ELSIF old_year NOT BETWEEN 2008 and 2200 THEN + RAISE EXCEPTION 'Input year is out of range'; + END IF; + -- + IF user_id IS NULL THEN + RAISE EXCEPTION 'Input user id argument is NULL'; + END IF; + -- + IF org_unit_id IS NULL THEN + RAISE EXCEPTION 'Org unit id argument is NULL'; + ELSE + -- + -- Validate the org unit + -- + SELECT TRUE + INTO org_found + FROM actor.org_unit + WHERE id = org_unit_id; + -- + IF org_found IS NULL THEN + RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id; + END IF; + END IF; + -- + -- Loop over the propagable funds to identify the details + -- from the old fund plus the id of the new one, if it exists. + -- + FOR roll_fund in + SELECT + oldf.id AS old_fund, + oldf.org, + oldf.name, + oldf.currency_type, + oldf.code, + oldf.rollover, + newf.id AS new_fund_id + FROM + acq.fund AS oldf + LEFT JOIN acq.fund AS newf + ON ( oldf.code = newf.code ) + WHERE + oldf.year = old_year + AND oldf.propagate + AND newf.year = new_year + AND oldf.org in ( + SELECT id FROM actor.org_unit_descendants( org_unit_id ) + ) + LOOP + --RAISE NOTICE 'Processing fund %', roll_fund.old_fund; + -- + IF roll_fund.new_fund_id IS NULL THEN + -- + -- The old fund hasn't been propagated yet. Propagate it now. + -- + INSERT INTO acq.fund ( + org, + name, + year, + currency_type, + code, + rollover, + propagate + ) VALUES ( + roll_fund.org, + roll_fund.name, + new_year, + roll_fund.currency_type, + roll_fund.code, + true, + true + ) + RETURNING id INTO new_fund; + ELSE + new_fund = roll_fund.new_fund_id; + END IF; + -- + -- Determine the amount to transfer + -- + SELECT amount + INTO xfer_amount + FROM acq.fund_spent_balance + WHERE fund = roll_fund.old_fund; + -- + IF xfer_amount <> 0 THEN + IF roll_fund.rollover THEN + -- + -- Transfer balance from old fund to new + -- + --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund; + -- + PERFORM acq.transfer_fund( + roll_fund.old_fund, + xfer_amount, + new_fund, + xfer_amount, + user_id, + 'Rollover' + ); + ELSE + -- + -- Transfer balance from old fund to the void + -- + -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund; + -- + PERFORM acq.transfer_fund( + roll_fund.old_fund, + xfer_amount, + NULL, + NULL, + user_id, + 'Rollover' + ); + END IF; + END IF; + -- + IF roll_fund.rollover THEN + -- + -- Move any lineitems from the old fund to the new one + -- where the associated debit is an encumbrance. + -- + -- Any other tables tying expenditure details to funds should + -- receive similar treatment. At this writing there are none. + -- + UPDATE acq.lineitem_detail + SET fund = new_fund + WHERE + fund = roll_fund.old_fund -- this condition may be redundant + AND fund_debit in + ( + SELECT id + FROM acq.fund_debit + WHERE + fund = roll_fund.old_fund + AND encumbrance + ); + -- + -- Move encumbrance debits from the old fund to the new fund + -- + UPDATE acq.fund_debit + SET fund = new_fund + wHERE + fund = roll_fund.old_fund + AND encumbrance; + END IF; + -- + -- Mark old fund as inactive, now that we've closed it + -- + UPDATE acq.fund + SET active = FALSE + WHERE id = roll_fund.old_fund; + END LOOP; +END; +$$ LANGUAGE plpgsql; + +COMMIT; -- 2.43.2