Purpose

During order rollover, we remove the links to encumbrances for all closed and pending orders. This is important because encumbrances use budgets in the previous fiscal year, which become inactive.

The problem is that there can be many encumbrance transactions left in the database with no reference to them. We need to remove them to improve performance and data consistency.

Requirements

• During the order rollover, identify encumbrance transactions that can be removed.

  • They should not be linked by orders or invoices.

  • There is currently no way to tell if an open invoice is using an encumbrance just by looking at the encumbrance, so a query using invoice lines will be needed.

  • orderStatus should be Closed or Pending.

  • fiscalYearId should not be the new one for the rollover (we could limit the search to the previous fiscal year, but then old environments would not get cleaned up without additional code).

  • amountAwaitingPayment, amountCredited and amountExpended should be 0.

  • Pay attention to performance - existing indices should be used for queries if possible; otherwise new indices might have to be created.

• Remove the transactions in batch.

Possible Approach

• Retrieve transactions based on transactionType, fiscalYearId, encumbrance.orderStatus, encumbrance.amountAwaitingPayment, encumbrance.amountCredited, encumbrance.amountExpended. Note: there are only indices for transactionType andfiscalYearId.

• Retrieve order lines using encumbrance.sourcePoLineId, and invoice lines with matching poLineId.

• Remove from the list of transactions the ones in the po lines or invoice lines fund distributions.

• Delete the remaining transactions.