Case 00057940 – AP Invoice Tax and Distribution Amounts Incorrect with Full ZX and XLA Correction

AP Invoice Tax and Distribution Amounts Incorrect — Full ZX and XLA Correction

Case Number	00057940
Oracle Module	Accounts Payable (AP) / eBusiness Tax (ZX) / Subledger Accounting (XLA)
EBS Version	R12
Functional Area	Invoice Processing / Tax Calculation / Subledger Accounting
Related Patches	None

Symptoms

An AP invoice has incorrect non-zero tax and distribution amounts across AP, ZX, and XLA tables that should be zero.
Invoice lines and distributions show incorrect amount, total_dist_amount, and taxable_amount values.
eBusiness Tax tables (ZX_LINES, ZX_LINES_SUMMARY, ZX_REC_NREC_DIST, ZX_LINES_DET_FACTORS) contain stale non-zero tax amounts that do not match the corrected AP values.
XLA subledger accounting entries (XLA_AE_LINES, XLA_DISTRIBUTION_LINKS) reflect the incorrect amounts and must also be corrected.

Root Cause

A cancellation, adjustment, or correction process did not complete cleanly, leaving residual non-zero amounts across the AP invoice, tax, and accounting layers. The amounts need to be zeroed in all affected tables to bring AP, ZX, and XLA into alignment. This is a more comprehensive version of the invoice zeroing fix that additionally corrects ZX_LINES_SUMMARY, ZX_REC_NREC_DIST, and the XLA accounting lines.

Resolution

Step 1 – Create backup tables

Run data_fix.sql. Backup tables are created for:

AP_INVOICE_DISTRIBUTIONS_ALL
AP_INVOICE_LINES_ALL (via ap_invoice_lines)
ZX_LINES
ZX_LINES_SUMMARY
ZX_REC_NREC_DIST
ZX_LINES_DET_FACTORS
XLA_AE_LINES
XLA_DISTRIBUTION_LINKS

Step 2 – Zero AP invoice distribution amounts

Update AP_INVOICE_DISTRIBUTIONS_ALL:

Set amount = 0 and total_dist_amount = 0 for the tax distribution.
Set taxable_amount = 0 for the item distributions that reference the tax.

Step 3 – Zero AP invoice line amounts

Update AP_INVOICE_LINES_ALL to set amount = 0 for the affected invoice lines.

Step 4 – Zero ZX tax line amounts

Update ZX_LINES to set line_amt = 0, rec_tax_amt = 0, and tax_amt = 0.

Step 5 – Zero ZX summary and recoverable/non-recoverable distribution amounts

Update ZX_LINES_SUMMARY to set TAX_AMT = 0.
Update ZX_REC_NREC_DIST to set rec_nrec_tax_amt = 0, TRX_LINE_DIST_AMT = 0, and TRX_LINE_DIST_TAX_AMT = 0.

Step 6 – Zero ZX determining factor line amount

Update ZX_LINES_DET_FACTORS to set line_amt = 0 for the affected invoice and transaction line.

Step 7 – Zero XLA accounting line amounts

Update XLA_AE_LINES to set entered and accounted debit/credit amounts to zero for the affected accounting lines across the affected AE headers.

Step 8 – Zero XLA distribution link amounts

Update XLA_DISTRIBUTION_LINKS to set unrounded_entered_cr/dr = 0 and unrounded_accounted_cr/dr = 0.

Step 9 – Commit and validate

Issue COMMIT. Re-validate the invoice and confirm it passes with zero tax amounts across all layers.

Script Inventory

File	Description
data_fix.sql	Creates backups; zeros AP_INVOICE_DISTRIBUTIONS_ALL (amount, total_dist_amount, taxable_amount); zeros AP_INVOICE_LINES_ALL; zeros ZX_LINES, ZX_LINES_SUMMARY, ZX_REC_NREC_DIST, ZX_LINES_DET_FACTORS; zeros XLA_AE_LINES entered/accounted DR/CR; zeros XLA_DISTRIBUTION_LINKS unrounded amounts.

Key Tables Affected

Table	Action
AP_INVOICE_DISTRIBUTIONS_ALL	Update amount, total_dist_amount, taxable_amount to 0
AP_INVOICE_LINES_ALL	Update amount to 0
ZX_LINES	Update line_amt, rec_tax_amt, tax_amt to 0
ZX_LINES_SUMMARY	Update TAX_AMT to 0
ZX_REC_NREC_DIST	Update rec_nrec_tax_amt, TRX_LINE_DIST_AMT, TRX_LINE_DIST_TAX_AMT to 0
ZX_LINES_DET_FACTORS	Update line_amt to 0
XLA_AE_LINES	Update entered/accounted DR/CR amounts to 0
XLA_DISTRIBUTION_LINKS	Update unrounded entered/accounted amounts to 0