From c25e5a3a9a5de07a6c0614093eaac83d4cc37fc3 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Thu, 26 Jul 2012 09:35:01 -0400 Subject: [PATCH] Allow propagation of encumbrances without funds Some libraries must return all unspent money to some funding agencies at the end of the year. To support this, we give the year-end close-out operation the ability to dump the money from rollover-enabled funds into the void. Funds are still created for the next year, and encumbrances are moved to theses analogous funds. Signed-off-by: Mike Rylander Signed-off-by: Bill Erickson --- .../lib/OpenILS/Application/Acq/Financials.pm | 18 +- Open-ILS/src/sql/Pg/200.schema.acq.sql | 285 ++---------------- .../XXXX.schema.acq-function-dedup.sql | 279 +++++++++++++++++ Open-ILS/src/templates/acq/fund/list.tt2 | 15 + .../js/ui/default/acq/financial/list_funds.js | 5 +- 5 files changed, 325 insertions(+), 277 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-function-dedup.sql diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/Financials.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/Financials.pm index 3fff0c33ba..df0d3bce7b 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/Financials.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/Financials.pm @@ -1221,6 +1221,7 @@ __PACKAGE__->register_method ( {desc => 'Fund Year to roll over', type => 'integer'}, {desc => 'Org unit ID', type => 'integer'}, {desc => 'Include Descendant Orgs (boolean)', type => 'integer'}, + {desc => 'Option hash: limit, offset, encumb_only', type => 'object'}, ], return => {desc => 'Returns a stream of all related funds for the next year including fund summary for each'} } @@ -1277,6 +1278,7 @@ sub process_fiscal_rollover { $options ||= {}; my $combined = ($self->api_name =~ /combined/); + my $encumb_only = $U->is_true($options->{encumb_only}) ? 't' : 'f'; my $org_ids = ($descendants) ? [ @@ -1308,22 +1310,16 @@ sub process_fiscal_rollover { ($descendants) ? 'acq.rollover_funds_by_org_tree' : 'acq.rollover_funds_by_org_unit', - $year, $e->requestor->id, $org_id + $year, $e->requestor->id, $org_id, $encumb_only ] }); } # Fetch all funds for the specified org units for the subsequent year - my $fund_ids = $e->search_acq_fund([ - { - year => int($year) + 1, + my $fund_ids = $e->search_acq_fund( + [{ year => int($year) + 1, org => $org_ids, - propagate => 't' - }, { - limit => $$options{limit} || 20, - offset => $$options{offset} || 0, - } - ], + propagate => 't' }], {idlist => 1} ); @@ -1338,7 +1334,7 @@ sub process_fiscal_rollover { my $sum = $e->json_query({ select => {acqftr => [{column => 'dest_amount', transform => 'sum'}]}, from => 'acqftr', - where => {dest_fund => $fund->id, note => 'Rollover'} + where => {dest_fund => $fund->id, note => { like => 'Rollover%' } } })->[0]; $amount = $sum->{dest_amount} if $sum; diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index f4669b90f6..f0a1bf37b8 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -1929,89 +1929,11 @@ BEGIN END; $$ LANGUAGE 'plpgsql'; -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, - balance_warning_percent, - balance_stop_percent - ) VALUES ( - old_fund.org, - old_fund.name, - old_year + 1, - old_fund.currency_type, - old_fund.code, - old_fund.rollover, - true, - old_fund.balance_warning_percent, - old_fund.balance_stop_percent - ) - 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 + org_unit_id INTEGER, + include_desc BOOL DEFAULT TRUE ) RETURNS VOID AS $$ DECLARE -- @@ -2051,9 +1973,9 @@ BEGIN WHERE year = old_year AND propagate - AND org in ( - SELECT id FROM actor.org_unit_descendants( org_unit_id ) - ) + AND ( ( include_desc AND org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) ) + OR (NOT include_desc AND oldf.org = org_unit_id ) ) + LOOP BEGIN INSERT INTO acq.fund ( @@ -2089,187 +2011,17 @@ BEGIN 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, - balance_warning_percent, - balance_stop_percent - ) VALUES ( - roll_fund.org, - roll_fund.name, - new_year, - roll_fund.currency_type, - roll_fund.code, - true, - true, - roll_fund.balance_warning_percent, - roll_fund.balance_stop_percent - ) - 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.propagate_funds_by_org_unit( old_year INTEGER, user_id INTEGER, org_unit_id INTEGER ) RETURNS VOID AS $$ + SELECT acq.propagate_funds_by_org_tree( $1, $2, $3, FALSE ); +$$ LANGUAGE SQL; + CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree( old_year INTEGER, user_id INTEGER, - org_unit_id INTEGER + org_unit_id INTEGER, + encumb_only BOOL DEFAULT FALSE, + include_desc BOOL DEFAULT TRUE ) RETURNS VOID AS $$ DECLARE -- @@ -2331,9 +2083,8 @@ BEGIN 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 ) - ) + AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) ) + OR (NOT include_desc AND oldf.org = org_unit_id ) ) LOOP --RAISE NOTICE 'Processing fund %', roll_fund.old_fund; -- @@ -2375,7 +2126,7 @@ BEGIN WHERE fund = roll_fund.old_fund; -- IF xfer_amount <> 0 THEN - IF roll_fund.rollover THEN + IF NOT encumb_only AND roll_fund.rollover THEN -- -- Transfer balance from old fund to new -- @@ -2401,7 +2152,7 @@ BEGIN NULL, NULL, user_id, - 'Rollover' + 'Rollover into the void' ); END IF; END IF; @@ -2445,6 +2196,10 @@ BEGIN END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_unit( old_year INTEGER, user_id INTEGER, org_unit_id INTEGER, encumb_only BOOL DEFAULT FALSE ) RETURNS VOID AS $$ + SELECT acq.rollover_funds_by_org_tree( $1, $2, $3, $4, FALSE ); +$$ LANGUAGE SQL; + 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/XXXX.schema.acq-function-dedup.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-function-dedup.sql new file mode 100644 index 0000000000..a7167ce6e1 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-function-dedup.sql @@ -0,0 +1,279 @@ + +DROP FUNCTION acq.propagate_funds_by_org_tree (INT, INT, INT); +DROP FUNCTION acq.propagate_funds_by_org_unit (INT, INT, INT); + +CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree( + old_year INTEGER, + user_id INTEGER, + org_unit_id INTEGER, + include_desc BOOL DEFAULT TRUE +) 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 ( ( include_desc AND org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) ) + OR (NOT include_desc AND oldf.org = org_unit_id ) ) + + LOOP + BEGIN + INSERT INTO acq.fund ( + org, + name, + year, + currency_type, + code, + rollover, + propagate, + balance_warning_percent, + balance_stop_percent + ) VALUES ( + old_fund.org, + old_fund.name, + old_year + 1, + old_fund.currency_type, + old_fund.code, + old_fund.rollover, + true, + old_fund.balance_warning_percent, + old_fund.balance_stop_percent + ) + 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_unit( old_year INTEGER, user_id INTEGER, org_unit_id INTEGER ) RETURNS VOID AS $$ + SELECT acq.propagate_funds_by_org_tree( $1, $2, $3, FALSE ); +$$ LANGUAGE SQL; + + +DROP FUNCTION acq.rollover_funds_by_org_tree (INT, INT, INT); +DROP FUNCTION acq.rollover_funds_by_org_unit (INT, INT, INT); + +CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree( + old_year INTEGER, + user_id INTEGER, + org_unit_id INTEGER, + encumb_only BOOL DEFAULT FALSE, + include_desc BOOL DEFAULT TRUE +) RETURNS VOID AS $$ +DECLARE +-- +new_fund INT; +new_year INT := old_year + 1; +org_found BOOL; +xfer_amount NUMERIC := 0; +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 ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) ) + OR (NOT include_desc AND oldf.org = 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, + balance_warning_percent, + balance_stop_percent + ) VALUES ( + roll_fund.org, + roll_fund.name, + new_year, + roll_fund.currency_type, + roll_fund.code, + true, + true, + roll_fund.balance_warning_percent, + roll_fund.balance_stop_percent + ) + 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 NOT encumb_only AND 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 into the void' + ); + 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_unit( old_year INTEGER, user_id INTEGER, org_unit_id INTEGER, encumb_only BOOL DEFAULT FALSE ) RETURNS VOID AS $$ + SELECT acq.rollover_funds_by_org_tree( $1, $2, $3, $4, FALSE ); +$$ LANGUAGE SQL; + + diff --git a/Open-ILS/src/templates/acq/fund/list.tt2 b/Open-ILS/src/templates/acq/fund/list.tt2 index 95eac5fe45..0173e739c5 100644 --- a/Open-ILS/src/templates/acq/fund/list.tt2 +++ b/Open-ILS/src/templates/acq/fund/list.tt2 @@ -72,6 +72,21 @@ + + + + + + + + +
+ This modifies the above described Close-out Operation, causing funds to be + transfered into the void (that is, entirely removed) instead of being transfered + to the analogous funds in the subsequent fiscal year. +
+ + Context Org Unit: diff --git a/Open-ILS/web/js/ui/default/acq/financial/list_funds.js b/Open-ILS/web/js/ui/default/acq/financial/list_funds.js index 17873d0a3c..4586333a4e 100644 --- a/Open-ILS/web/js/ui/default/acq/financial/list_funds.js +++ b/Open-ILS/web/js/ui/default/acq/financial/list_funds.js @@ -173,6 +173,8 @@ function performRollover(args) { var dryRun = args.dry_run[0] == 'on'; if(dryRun) method += '.dry_run'; + var encumbOnly = args.encumb_only[0] == 'on'; + var count = 0; var amount_rolled = 0; var year = fundFilterYearSelect.attr('value'); // TODO alternate selector? @@ -186,7 +188,8 @@ function performRollover(args) { openils.User.authtoken, year, contextOrg, - (args.child_orgs[0] == 'on') + (args.child_orgs[0] == 'on'), + { encumb_only : encumbOnly } ], onresponse : function(r) { -- 2.43.2