From 6fe3bbe788e62ea85dda14047932f3d630ee940e Mon Sep 17 00:00:00 2001 From: Dan Pearl Date: Fri, 9 Jan 2015 14:23:58 -0500 Subject: [PATCH] LP#1204671 Allow fund tags to remain attached to new fund during end-of-year propagation or rollower. Signed-off-by: Dan Pearl dpearl@cwmars.org Signed-off-by: Kathy Lussier Signed-off-by: Ben Shum --- Open-ILS/src/sql/Pg/200.schema.acq.sql | 28 ++ .../sql/Pg/upgrade/XXXX.fund_tags_persist.sql | 317 ++++++++++++++++++ .../Acquisitions/Fund_Tag_Preservation.txt | 4 + 3 files changed, 349 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.fund_tags_persist.sql create mode 100644 docs/RELEASE_NOTES_NEXT/Acquisitions/Fund_Tag_Preservation.txt diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index 2741ebce08..c909f89b6a 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -1933,6 +1933,27 @@ BEGIN END; $$ LANGUAGE 'plpgsql'; +CREATE OR REPLACE FUNCTION acq.copy_fund_tags( + old_fund_id INTEGER, + new_fund_id INTEGER +) RETURNS VOID AS $$ +DECLARE +fund_tag_rec RECORD; +BEGIN + + FOR fund_tag_rec IN SELECT * FROM acq.fund_tag_map WHERE fund=old_fund_id LOOP + BEGIN + INSERT INTO acq.fund_tag_map(fund, tag) VALUES(new_fund_id, fund_tag_rec.tag); + EXCEPTION + WHEN unique_violation THEN + -- RAISE NOTICE 'Fund tag already propagated', old_fund.id; + CONTINUE; + END; + END LOOP; + RETURN; +END; +$$ LANGUAGE plpgsql; + CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree( old_year INTEGER, user_id INTEGER, @@ -2009,6 +2030,9 @@ BEGIN --RAISE NOTICE 'Fund % already propagated', old_fund.id; CONTINUE; END; + + PERFORM acq.copy_fund_tags(old_fund.id,new_id); + --RAISE NOTICE 'Propagating fund % to fund %', -- old_fund.code, new_id; END LOOP; @@ -2127,6 +2151,9 @@ BEGIN roll_fund.balance_stop_percent ) RETURNING id INTO new_fund; + + PERFORM acq.copy_fund_tags(roll_fund.id,new_fund); + ELSE new_fund = roll_fund.new_fund_id; END IF; @@ -2224,6 +2251,7 @@ $$ 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/upgrade/XXXX.fund_tags_persist.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.fund_tags_persist.sql new file mode 100644 index 0000000000..68e141ad45 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.fund_tags_persist.sql @@ -0,0 +1,317 @@ +BEGIN; + +CREATE OR REPLACE FUNCTION acq.copy_fund_tags( + old_fund_id INTEGER, + new_fund_id INTEGER +) RETURNS VOID AS $$ +DECLARE +fund_tag_rec RECORD; +BEGIN + + FOR fund_tag_rec IN SELECT * FROM acq.fund_tag_map WHERE fund=old_fund_id LOOP + BEGIN + INSERT INTO acq.fund_tag_map(fund, tag) VALUES(new_fund_id, fund_tag_rec.tag); + EXCEPTION + WHEN unique_violation THEN + -- RAISE NOTICE 'Fund tag already propagated', old_fund.id; + CONTINUE; + END; + END LOOP; + RETURN; +END; +$$ LANGUAGE plpgsql; + +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; + + PERFORM acq.copy_fund_tags(roll_fund.id,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; + +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 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; + + PERFORM acq.copy_fund_tags(old_fund.id,new_id); + + --RAISE NOTICE 'Propagating fund % to fund %', + -- old_fund.code, new_id; + END LOOP; +END; +$$ LANGUAGE plpgsql; + +COMMIT; + + diff --git a/docs/RELEASE_NOTES_NEXT/Acquisitions/Fund_Tag_Preservation.txt b/docs/RELEASE_NOTES_NEXT/Acquisitions/Fund_Tag_Preservation.txt new file mode 100644 index 0000000000..408af932c8 --- /dev/null +++ b/docs/RELEASE_NOTES_NEXT/Acquisitions/Fund_Tag_Preservation.txt @@ -0,0 +1,4 @@ +Fund Tag Preservation +^^^^^^^^^^^^^^^^^^^^^ +For convenience when propagating or rolling over a fund for a new fiscal year, fund tags will be copied from the current fund to +the new year's fund. -- 2.43.2