Case 00131619 – AP Invoice NONREC Tax Holds and Unprocessed XLA Events Cleanup

AP Invoice NONREC Tax Holds and Unprocessed XLA Events Cleanup

Case Number	00131619
Oracle Module	Accounts Payable (AP) / Subledger Accounting (XLA)
EBS Version	R12
Functional Area	Invoice Processing / Tax Distributions / Invoice Holds / Accounting Events
Related Patches	None

Symptoms

Multiple AP invoices are stuck with holds that have no release_lookup_code and cannot be released through the UI.
Invoice distributions contain erroneous NONREC_TAX (or truncated variant) type distributions that should not exist.
XLA events for the affected invoices are in an unprocessed state (event_status_code = 'I', process_status_code = 'U') and are blocking accounting.
Invoice line amounts, period names, and accounting dates are out of sync with the underlying distributions.

Root Cause

An incomplete tax calculation or cancellation process left orphaned NONREC_TAX distributions and stuck invoice holds on multiple invoices. XLA accounting events were created in an incomplete/unprocessed state. The invoice line amounts are inconsistent with the distributions, and the accounting events need to be deleted (if unprocessed) or marked as processed to allow the invoices to be re-accounted cleanly.

Resolution

Step 1 – Create backup tables

Run data_fix.sql. Backup tables are created for:

AP_INVOICE_LINES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
XLA_EVENTS (for AP_INVOICES entity)
AP_HOLDS_ALL

Step 2 – Delete unreleased holds

Delete from AP_HOLDS_ALL for the affected invoices where release_lookup_code IS NULL (holds that have never been released — stuck system holds).

Step 3 – Delete orphaned NONREC_TAX distributions

Delete from AP_INVOICE_DISTRIBUTIONS_ALL for the affected invoices where LINE_TYPE_LOOKUP_CODE = 'NONREC_TAX' (or the truncated variant).

Step 4 – Delete unprocessed XLA events

Delete from XLA_EVENTS for the AP_INVOICES entity where event_status_code = 'I' (incomplete) and process_status_code = 'U' (unprocessed) for events created after the applicable cutoff date.

Step 5 – Release remaining holds

Update AP_HOLDS_ALL.STATUS_FLAG = 'R' for all remaining holds on the affected invoices (releasing any holds not already deleted in Step 2).

Step 6 – Resync invoice line amounts

Update AP_INVOICE_LINES_ALL to recalculate amount, period_name, and ACCOUNTING_DATE from the remaining distributions for each invoice line.

Step 7 – Mark distributions as posted and validated

Update AP_INVOICE_DISTRIBUTIONS_ALL to set accrual_posted_flag = 'Y', posted_flag = 'Y', and match_status_flag = 'A' for the affected invoices.

Step 8 – Mark remaining XLA events as processed

Update XLA_EVENTS to set event_status_code = 'P' and process_status_code = 'P' for any remaining incomplete/unprocessed events on the affected invoices.

Step 9 – Commit and validate

Issue COMMIT. Confirm the invoices are released from holds and validate successfully.

Script Inventory

File	Description
data_fix.sql	Creates backups; deletes unreleased holds, NONREC_TAX distributions, and unprocessed XLA events; releases remaining holds; resyncs invoice line amounts from distributions; marks distributions as posted/validated; marks remaining XLA events as processed.

Key Tables Affected

Table	Action
AP_HOLDS_ALL	Delete unreleased holds; update STATUS_FLAG to 'R'
AP_INVOICE_DISTRIBUTIONS_ALL	Delete NONREC_TAX distributions; update posted/match status flags
XLA_EVENTS	Delete unprocessed events; update remaining to processed status
AP_INVOICE_LINES_ALL	Update amount, period_name, accounting_date from distributions