Fix encumbrances (especially after a Lotus rollover)

Owned by Mikita Siadykh
Last updated: Apr 17, 2023 by Damien
2 min read

This script fixes several issues in encumbrances related to the fiscal year rollover for FOLIO Lotus, and other issues that are still present in later versions. In particular, after a fiscal year rollover in Lotus, there can be a mismatch between budget encumbrances and purchase order encumbrances.

Do I need to run this script?

If you know one or more of the following things to be true about your system you will want to run this script.

  • The system was rolled over while running the Kiwi or Lotus flower versions
  • Our system contains one or more encumbrance transactions with an amount >0, but an encumbrance status of Released. These will generally be associated with ongoing orders based on how libraries generally use the Rollover function, but this issue could have occurred with any order type.
  • There are encumbrances in the system related to closed orders that have an order status of Open. This status is captured in the encumbrance data but is not shown in the UI so it would be difficult to notice.
  • There is an unreleased encumbrance and a released one for the same po line, fund and expense class.
  • There are unreleased encumbrances for closed orders.
  • There are unreleased open order encumbrances with negative amounts (shown as positive in the UI; this can cause negative encumbered in budgets).

Script logic

See "Options" in the README.

Requirements

  • python3 version 3.8 or later with required packages that can be installed via pip (requests)
  • user with required permissions - see "Required permissions for the user" in the README.
  • The script runs better on Linux. On Windows it might fail for a large number of orders with a "ConnectError" error.

Before running

  1. The script should not be used before a rollover in Lotus (because if it was, encumbrances would not be created for closed orders, and they could not be reopened - see MODORDERS-706).
  2. Make a backup (unless you are planning to use dry-run mode)
  3. Expect the script to take a long time with a large number of orders. (for 100k orders it takes about 1 to 3 hours with a fast connection)
  4. Operations affecting order encumbrances or budgets should be avoided while the budgets are recalculated.
  5. When running the script on Lotus or Morning Glory, disable Optimistic Locking (do not re-enable after running script)
    ALTER TABLE {tenant}_mod_finance_storage.transaction DISABLE TRIGGER set_transaction_ol_version_trigger;
ALTER TABLE {tenant}_mod_finance_storage.budget DISABLE TRIGGER set_budget_ol_version_trigger;
ALTER TABLE {tenant}_mod_finance_storage.budget_expense_class DISABLE TRIGGER set_budget_expense_class_ol_version_trigger;
ALTER TABLE {tenant}_mod_finance_storage.expense_class DISABLE TRIGGER set_expense_class_ol_version_trigger;
ALTER TABLE {tenant}_mod_finance_storage.fiscal_year DISABLE TRIGGER set_fiscal_year_ol_version_trigger;
ALTER TABLE {tenant}_mod_finance_storage.fund DISABLE TRIGGER set_fund_ol_version_trigger;
ALTER TABLE {tenant}_mod_finance_storage.fund_type DISABLE TRIGGER set_fund_type_ol_version_trigger;
ALTER TABLE {tenant}_mod_finance_storage.group_fund_fiscal_year DISABLE TRIGGER set_group_fund_fiscal_year_ol_version_trigger;
ALTER TABLE {tenant}_mod_finance_storage.groups DISABLE TRIGGER set_groups_ol_version_trigger;
ALTER TABLE {tenant}_mod_finance_storage.ledger DISABLE TRIGGER set_ledger_ol_version_trigger;
  6. Delete records from temporary transactions table 
    DELETE FROM {tenant}_mod_finance_storage.temporary_order_transactions;

Script execution

See "Script arguments" and "Execution example" in the README.

Script Source Code

https://github.com/folio-org/mod-finance-storage/tree/master/scripts/fix_encumbrances