Case 00030902 – AP Invoice Tax Distributions Out of Sync with ZX Tables

AP Invoice Tax Distributions Out of Sync with ZX Tables

Case Number	00030902
Oracle Module	Accounts Payable (AP) / eBusiness Tax (ZX)
EBS Version	R12
Functional Area	Invoice Validation / Tax Processing
Related Patches	None

Symptoms

An AP invoice fails validation or cannot be accounted due to inconsistent tax distribution data.
Tax line records exist in AP_INVOICE_DISTRIBUTIONS_ALL but the corresponding rows in ZX_REC_NREC_DIST, ZX_LINES, and ZX_LINES_SUMMARY are missing or mismatched.
The invoice shows holds and cannot be posted.
Revalidation attempts fail because orphaned XLA event records reference lines that no longer match the invoice state.

Root Cause

A partial or failed accounting event left certain invoice lines and their associated eBusiness Tax (ZX) records in an inconsistent state. Specifically, an XLA event was created and linked to some distributions, but the corresponding ZX tax calculation tables (ZX_REC_NREC_DIST, ZX_LINES, ZX_LINES_SUMMARY, ZX_LINES_DET_FACTORS) retained stale or duplicate rows. The fix removes the orphaned XLA event and ZX records, cleans up the affected invoice lines, then corrects the remaining distribution amounts.

Resolution

Step 1 – Create backup tables

Run data_fix.sql. The script creates backup tables for all affected rows across AP and ZX tables before any modifications:

Backups of AP_INVOICE_DISTRIBUTIONS_ALL, AP_HOLDS_ALL, AP_INVOICE_LINES_ALL, AP_INVOICES_ALL
Backups of ZX_REC_NREC_DIST, ZX_LINES, ZX_LINES_SUMMARY, ZX_LINES_DET_FACTORS
Backup of the orphaned XLA_EVENTS record

Step 2 – Delete orphaned ZX tax records

Delete from ZX tables in the following order for the affected invoice and accounting event:

ZX_REC_NREC_DIST — for distributions linked to the orphaned accounting event and for specific invoice line numbers
ZX_LINES — for the affected transaction and line IDs
ZX_LINES_SUMMARY — for the affected summary tax line IDs
ZX_LINES_DET_FACTORS — for the affected transaction/line/entity/event class combination

Step 3 – Delete AP distributions and invoice lines

Delete from AP_INVOICE_DISTRIBUTIONS_ALL for distributions linked to the orphaned accounting event and the specific invoice line numbers. Delete the corresponding rows from AP_INVOICE_LINES_ALL.

Step 4 – Delete orphaned XLA event

Delete the orphaned row from XLA_EVENTS by event ID.

Step 5 – Correct invoice and distribution amounts

Update AP_INVOICES_ALL to clear force_revalidation_flag and validation_request_id. Update AP_HOLDS_ALL to release outstanding holds. Correct distribution amounts in AP_INVOICE_DISTRIBUTIONS_ALL and line amounts in AP_INVOICE_LINES_ALL to reflect the correct totals.

Step 6 – Commit and revalidate

Issue COMMIT. Re-run AP Invoice Validation for the affected invoice to regenerate tax lines and accounting events cleanly.

Note: The original script (iteration A) was superseded; this is the final working version (iteration B). Ensure all backup tables are created successfully before proceeding with the deletes.

Script Inventory

File	Description
data_fix.sql	Creates backups for AP and ZX tables; deletes orphaned ZX tax records, AP distributions, AP invoice lines, and the stale XLA event; corrects invoice/distribution amounts; releases holds.

Key Tables Affected

Table	Action
XLA_EVENTS	Delete orphaned event
ZX_REC_NREC_DIST	Delete orphaned tax distributions
ZX_LINES	Delete orphaned tax lines
ZX_LINES_SUMMARY	Delete orphaned tax line summaries
ZX_LINES_DET_FACTORS	Delete orphaned tax determining factor records
AP_INVOICE_DISTRIBUTIONS_ALL	Delete affected distributions; correct amounts
AP_INVOICE_LINES_ALL	Delete affected lines; correct amounts
AP_INVOICES_ALL	Clear revalidation flags
AP_HOLDS_ALL	Release outstanding holds