Case 00030090 – AP Invoice Distributions Stuck on PO-Matched Invoice

AP Invoice Distributions Stuck on PO-Matched Invoice

Case Number	00030090
Oracle Module	Accounts Payable (AP) / Purchasing (PO)
EBS Version	R12
Functional Area	Invoice Processing / PO Matching
Related Patches	None

Symptoms

An AP invoice matched to a Purchase Order cannot be fully cancelled or reversed.
The PO shows billed quantities/amounts that cannot be cleared through normal processing.
AP invoice distributions remain in an inconsistent state relative to the PO.
Attempting standard reversal or cancellation results in errors or incomplete processing.

Root Cause

The AP invoice distributions for the affected invoice became orphaned or stuck due to a failed accounting or matching event. The PO distribution tables (PO_DISTRIBUTIONS_ALL, PO_LINE_LOCATIONS_ALL) retained non-zero quantity_billed and amount_billed values even though the corresponding AP distributions were in an inconsistent state. Additionally, charge allocation (AP_CHRG_ALLOCATIONS_ALL) and multi-currency distribution (AP_MC_INVOICE_DISTS) records were orphaned.

Resolution

Step 1 – Identify affected records

Verify the affected invoice ID and PO distribution/line location IDs by querying AP_INVOICE_DISTRIBUTIONS_ALL and PO_DISTRIBUTIONS_ALL.

Step 2 – Create backup tables

Run data_fix.sql — the script begins by creating backup tables for all rows that will be modified or deleted:

rsi_ap_dist_<case> — backup of AP_INVOICE_DISTRIBUTIONS_ALL
rsi_ap_chrg_alloc_<case> — backup of AP_CHRG_ALLOCATIONS_ALL
rsi_ap_mc_inv_<case> — backup of AP_MC_INVOICE_DISTS
rsi_po_dist_<case> — backup of PO_DISTRIBUTIONS_ALL
rsi_po_line_loc_<case> — backup of PO_LINE_LOCATIONS_ALL

Step 3 – Reset PO billed quantities and amounts

Update PO_DISTRIBUTIONS_ALL and PO_LINE_LOCATIONS_ALL to zero out quantity_billed and amount_billed for the affected PO distribution and line location.

Step 4 – Delete AP distributions and related records

Delete records in the following order to avoid foreign key violations:

Delete from AP_MC_INVOICE_DISTS for the affected invoice.
Delete from AP_CHRG_ALLOCATIONS_ALL for charge allocations linked to the affected invoice distributions.
Delete from AP_INVOICE_DISTRIBUTIONS_ALL for the affected invoice.

Step 5 – Commit and validate

Issue COMMIT. Verify that the invoice no longer shows distributions and the PO billed quantities are cleared.

Note: After this fix the invoice can be re-processed through standard AP validation and matching workflows. Ensure the PO is in the correct status before re-matching.

Script Inventory

File	Description
data_fix.sql	Creates backup tables, resets PO billed quantities, deletes orphaned AP charge allocations, multi-currency distributions, and invoice distributions. Issues COMMIT.

Key Tables Affected

Table	Action
PO_DISTRIBUTIONS_ALL	Update quantity_billed, amount_billed to 0
PO_LINE_LOCATIONS_ALL	Update quantity_billed, amount_billed to 0
AP_MC_INVOICE_DISTS	Delete for affected invoice
AP_CHRG_ALLOCATIONS_ALL	Delete for affected invoice distributions
AP_INVOICE_DISTRIBUTIONS_ALL	Delete for affected invoice