Case 00051104 – AP Payment Batch Stuck — Cancel Checkrun and Release Invoices

AP Payment Batch Stuck — Cancel Checkrun and Release Invoices

Case Number	00051104
Oracle Module	Accounts Payable (AP)
EBS Version	R12
Functional Area	Payment Processing / Manual Payment Batch (11i-style)
Related Patches	None

Symptoms

A manual AP payment batch (checkrun) is stuck and cannot be completed, voided, or removed through the application.
Invoice payment schedules show a checkrun_id that references the stuck batch, preventing the invoices from being selected for a new payment run.
Records persist in AP_INV_SELECTION_CRITERIA_ALL, AP_SELECTED_INVOICES_ALL, and AP_SELECTED_INVOICE_CHECKS_ALL for the stuck checkrun.

Root Cause

The AP payment selection process (11i-style manual checkrun) was initiated but not completed. The checkrun tables retain the selected invoice and check records, and the invoice payment schedules are locked with the checkrun ID. The selection criteria record remains in a non-cancelled state, preventing normal cleanup. The payment batch cannot be cancelled through the normal UI because the concurrent program that created it is no longer running or is in an error state.

Resolution

Step 1 – Create backup tables

Run data_fix.sql. Backups are created for:

AP_INV_SELECTION_CRITERIA_ALL
AP_SELECTED_INVOICES_ALL
AP_SELECTED_INVOICE_CHECKS_ALL
AP_PAYMENT_SCHEDULES_ALL (records locked by the checkrun — identified via subquery on checkrun_id)
AP_CHECK_STOCKS_ALL (check stock associated with the batch)

Step 2 – Clear checkrun_id from payment schedules

Update AP_PAYMENT_SCHEDULES_ALL to set checkrun_id = NULL for all invoices that were selected in the stuck batch. The checkrun_id is resolved dynamically via a subquery against AP_INV_SELECTION_CRITERIA_ALL using the checkrun name. This releases the invoices for re-selection.

Step 3 – Cancel the selection criteria record

Update AP_INV_SELECTION_CRITERIA_ALL to set status = 'CANCELED' for the stuck checkrun name. This marks the checkrun as formally cancelled before the detail records are removed.

Step 4 – Delete stuck checkrun detail records

Delete from the following tables for the stuck checkrun name:

AP_SELECTED_INVOICES_ALL
AP_SELECTED_INVOICE_CHECKS_ALL

Step 5 – Commit and validate

Issue COMMIT. Confirm the invoices are now available for selection in a new payment batch.

Note: This fix differs from a simple delete-and-release approach (see case 00050272) in that it explicitly sets the selection criteria status to CANCELED before removing the detail records, and it resolves the checkrun_id dynamically via subquery rather than a hardcoded value. This fix is applicable to the 11i-style manual checkrun payment flow. For R12 Payment Process Request (PPR) issues, refer to the ap_Rel_Inv_frm_TermPPR_fix.sql approach.

Script Inventory

File	Description
data_fix.sql	Creates backups; clears checkrun_id from AP_PAYMENT_SCHEDULES_ALL (checkrun_id resolved via subquery); sets status = 'CANCELED' on AP_INV_SELECTION_CRITERIA_ALL; deletes stuck records from AP_SELECTED_INVOICES_ALL and AP_SELECTED_INVOICE_CHECKS_ALL.

Key Tables Affected

Table	Action
AP_PAYMENT_SCHEDULES_ALL	Update checkrun_id to NULL
AP_INV_SELECTION_CRITERIA_ALL	Update status to CANCELED
AP_SELECTED_INVOICES_ALL	Delete for stuck checkrun
AP_SELECTED_INVOICE_CHECKS_ALL	Delete for stuck checkrun