From d959739a1f613e55d0f7cccd33daa86486d609af Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Fri, 8 Mar 2013 13:23:06 -0500 Subject: [PATCH] Distribution formula fund rollover setting Adds an org unit setting (acq.fund.rollover_distrib_forms) which controls whether the fund value on distribution formulae is updated to use the newly created fund during fiscal year rollover. Signed-off-by: Bill Erickson Signed-off-by: Lebbeous Fogle-Weekley --- Open-ILS/src/sql/Pg/200.schema.acq.sql | 17 +- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 20 ++ ...ema.acq.distribution_formula.expansion.sql | 206 ++++++++++++++++++ ...ata.acq.distribution_formula.expansion.sql | 25 +++ 4 files changed, 267 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.data.acq.distribution_formula.expansion.sql diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index 1095513bc7..d32b519560 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -2032,7 +2032,6 @@ CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit( old_year INTEGER, us 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, @@ -2050,6 +2049,7 @@ xfer_amount NUMERIC := 0; roll_fund RECORD; deb RECORD; detail RECORD; +roll_distrib_forms BOOL; -- BEGIN -- @@ -2212,6 +2212,19 @@ BEGIN fund = roll_fund.old_fund AND encumbrance; END IF; + + -- Rollover distribution formulae funds + SELECT INTO roll_distrib_forms value::BOOL FROM + actor.org_unit_ancestor_setting( + 'acq.fund.rollover_distrib_forms', org_unit_id + ); + + IF roll_distrib_forms THEN + UPDATE acq.distribution_formula_entry + SET fund = roll_fund.new_fund_id + WHERE fund = roll_fund.old_fund; + END IF; + -- -- Mark old fund as inactive, now that we've closed it -- @@ -2222,6 +2235,8 @@ 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; diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index b3469561da..d693e6ce37 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -12583,3 +12583,23 @@ INSERT INTO action_trigger.validator ( 'Tests if a patron is currently not marked as barred' ); +INSERT INTO config.org_unit_setting_type + (name, label, description, grp, datatype) +VALUES ( + 'acq.fund.rollover_distrib_forms', + oils_i18n_gettext( + 'acq.fund.rollover_distrib_forms', + 'Rollover Distribution Formulae Funds', + 'coust', + 'label' + ), + oils_i18n_gettext( + 'acq.fund.rollover_distrib_forms', + 'During fiscal rollover, update distribution formalae to use new funds', + 'coust', + 'description' + ), + 'acq', + 'bool' +); + diff --git a/Open-ILS/src/sql/Pg/upgrade/0779.schema.acq.distribution_formula.expansion.sql b/Open-ILS/src/sql/Pg/upgrade/0779.schema.acq.distribution_formula.expansion.sql index 148677e4a5..dc6ccec877 100644 --- a/Open-ILS/src/sql/Pg/upgrade/0779.schema.acq.distribution_formula.expansion.sql +++ b/Open-ILS/src/sql/Pg/upgrade/0779.schema.acq.distribution_formula.expansion.sql @@ -7,4 +7,210 @@ ALTER TABLE acq.distribution_formula_entry ADD COLUMN circ_modifier TEXT REFERENCES config.circ_modifier (code), ADD COLUMN collection_code TEXT ; + +-- support option to roll distribution formula funds +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; +perm_ous BOOL; +xfer_amount NUMERIC := 0; +roll_fund RECORD; +deb RECORD; +detail RECORD; +roll_distrib_forms BOOL; +-- +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; + ELSIF encumb_only THEN + SELECT INTO perm_ous value::BOOL FROM + actor.org_unit_ancestor_setting( + 'acq.fund.allow_rollover_without_money', org_unit_id + ); + IF NOT FOUND OR NOT perm_ous THEN + RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id; + END IF; + 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; + + -- Rollover distribution formulae funds + SELECT INTO roll_distrib_forms value::BOOL FROM + actor.org_unit_ancestor_setting( + 'acq.fund.rollover_distrib_forms', org_unit_id + ); + + IF roll_distrib_forms THEN + UPDATE acq.distribution_formula_entry + SET fund = roll_fund.new_fund_id + WHERE fund = roll_fund.old_fund; + 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; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.data.acq.distribution_formula.expansion.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.acq.distribution_formula.expansion.sql new file mode 100644 index 0000000000..9db24d3fcf --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.acq.distribution_formula.expansion.sql @@ -0,0 +1,25 @@ +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +INSERT INTO config.org_unit_setting_type + (name, label, description, grp, datatype) +VALUES ( + 'acq.fund.rollover_distrib_forms', + oils_i18n_gettext( + 'acq.fund.rollover_distrib_forms', + 'Rollover Distribution Formulae Funds', + 'coust', + 'label' + ), + oils_i18n_gettext( + 'acq.fund.rollover_distrib_forms', + 'During fiscal rollover, update distribution formalae to use new funds', + 'coust', + 'description' + ), + 'acq', + 'bool' +); + +COMMIT; -- 2.43.2