Case 00102896 – AP Invoice PO Match Removed and PO Billed Quantities Reset

AP Invoice PO Match Removed and PO Billed Quantities Reset

Case Number	00102896
Oracle Module	Accounts Payable (AP) / Purchasing (PO) / eBusiness Tax (ZX)
EBS Version	R12
Functional Area	Invoice Processing / PO Matching / Tax Correction
Related Patches	None

Symptoms

An AP invoice is incorrectly matched to a Purchase Order and needs to be unmatched (the PO reference removed from the invoice lines and distributions).
The corresponding PO lines, distributions, and line locations show incorrect billed quantities/amounts and closed statuses that were set by the incorrect invoice match.
ZX tax tables (ZX_LINES, ZX_REC_NREC_DIST, ZX_LINES_DET_FACTORS) contain reference document links (REF_DOC_* columns) back to the PO that must also be cleared.

Root Cause

An AP invoice was matched to a Purchase Order incorrectly. The match populated PO reference columns on the invoice lines and ZX tax tables, and updated billed quantity and closed status fields on the PO itself. The invoice needs to be effectively unmatched by clearing all PO reference columns from the AP and ZX tables, and by reversing the PO billed quantity/closed status updates on the PO side.

Resolution

Step 1 – Create backup tables

Run data_fix.sql. Backup tables are created for:

AP_INVOICE_LINES_ALL
ZX_LINES, ZX_REC_NREC_DIST, ZX_LINES_DET_FACTORS
PO_HEADERS_ALL, PO_DISTRIBUTIONS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL
AP_INVOICE_DISTRIBUTIONS_ALL

Step 2 – Remove PO references from AP invoice lines

Update AP_INVOICE_LINES_ALL to NULL out all PO reference columns: PO_HEADER_ID, PO_LINE_ID, PO_RELEASE_ID, PO_LINE_LOCATION_ID, and PO_DISTRIBUTION_ID.

Step 3 – Remove PO reference document links from ZX tables

Update ZX_LINES, ZX_REC_NREC_DIST, and ZX_LINES_DET_FACTORS to NULL out the REF_DOC_APPLICATION_ID, REF_DOC_ENTITY_CODE, REF_DOC_EVENT_CLASS_CODE, REF_DOC_TRX_ID, REF_DOC_LINE_ID, and (for ZX_REC_NREC_DIST) REF_DOC_DIST_ID columns for the affected invoice's AP_INVOICES entity records.

Step 4 – Reset PO billed quantities and closed status

Reverse the PO-side updates caused by the incorrect match:

PO_HEADERS_ALL — clear CLOSED_DATE and CLOSED_CODE.
PO_DISTRIBUTIONS_ALL — reset QUANTITY_BILLED = 0 and AMOUNT_BILLED = 0.
PO_LINES_ALL — clear CLOSED_CODE, CLOSED_DATE, and CLOSED_BY.
PO_LINE_LOCATIONS_ALL — clear CLOSED_CODE, CLOSED_DATE, CLOSED_BY, CLOSED_FOR_INVOICE_DATE, and reset QUANTITY_BILLED = 0.

Step 5 – Commit and validate

Issue COMMIT. Re-validate the invoice (now treated as a non-PO invoice) and confirm the PO is available for correct matching.

Script Inventory

File	Description
data_fix.sql	Creates backups; removes PO reference columns from AP_INVOICE_LINES_ALL; clears REF_DOC links from ZX_LINES, ZX_REC_NREC_DIST, ZX_LINES_DET_FACTORS; resets PO billed quantities and closed status in PO_DISTRIBUTIONS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, PO_HEADERS_ALL.

Key Tables Affected

Table	Action
AP_INVOICE_LINES_ALL	NULL out PO_HEADER_ID, PO_LINE_ID, PO_RELEASE_ID, PO_LINE_LOCATION_ID, PO_DISTRIBUTION_ID
ZX_LINES	NULL out REF_DOC reference document columns
ZX_REC_NREC_DIST	NULL out REF_DOC reference document columns including REF_DOC_DIST_ID
ZX_LINES_DET_FACTORS	NULL out REF_DOC reference document columns
PO_DISTRIBUTIONS_ALL	Reset QUANTITY_BILLED and AMOUNT_BILLED to 0
PO_LINES_ALL	Clear CLOSED_CODE, CLOSED_DATE, CLOSED_BY
PO_LINE_LOCATIONS_ALL	Clear closed columns; reset QUANTITY_BILLED to 0
PO_HEADERS_ALL	Clear CLOSED_DATE and CLOSED_CODE