Symptoms

• An AP invoice is stuck on DIST VARIANCE and/or LINE VARIANCE holds that cannot be released through normal validation or manual release.
• Invoice lines show incorrect amounts or discarded_flag/cancelled_flag values that prevent validation from succeeding.
• Orphaned XLA event records and ZX tax data exist for accounting events that should no longer be present.
• The invoice cannot be posted or paid until the holds are cleared.

Root Cause

A failed or partial accounting run created an orphaned XLA event linked to specific AP invoice distributions. The associated ZX tax records (ZX_REC_NREC_DIST, ZX_LINES, ZX_LINES_SUMMARY, ZX_LINES_DET_FACTORS) and AP invoice lines/distributions retained stale data from the failed event. The line-level amount mismatches caused repeated DIST VARIANCE and LINE VARIANCE holds on validation. The fix removes the orphaned event and tax data, corrects line amounts, and releases the holds.

Identification

Query to identify the orphaned accounting event and affected distributions:

SELECT aid.invoice_id, aid.accounting_event_id, aid.invoice_distribution_id
  FROM ap_invoice_distributions_all aid
 WHERE aid.accounting_event_id = <event_id>
 UNION
SELECT ah.invoice_id, ah.hold_lookup_code, ah.status_flag
  FROM ap_holds_all ah
 WHERE ah.invoice_id = <invoice_id>
   AND ah.hold_lookup_code IN ('DIST VARIANCE','LINE VARIANCE')
   AND NVL(ah.status_flag,'x') <> 'R';

Resolution

Run data_fix.sql. The following backup tables are created automatically:

• rsi_xla_events_<case>, rsi_ap_dist_<case>, rsi_ap_lines_<case>
• rsi_zx_rec_nrec_<case>, rsi_zx_lines_<case>, rsi_zx_line_sum_<case>, rsi_zx_det_fact_<case>
• rsi_ap_holds_<case>

Delete the orphaned row from XLA_EVENTS by the affected event ID.

Delete in order: ZX_REC_NREC_DIST → ZX_LINES → ZX_LINES_SUMMARY → ZX_LINES_DET_FACTORS, filtering on the affected invoice and event combination.

Delete from AP_INVOICE_DISTRIBUTIONS_ALL for the orphaned accounting event. Delete from AP_INVOICE_LINES_ALL for the affected line numbers.

Update AP_INVOICE_LINES_ALL to set correct amounts and clear discarded_flag/cancelled_flag for remaining lines.

Update AP_HOLDS_ALL to set release_lookup_code = 'VARIANCE OVERRIDE' and status_flag = 'R' for all DIST VARIANCE and LINE VARIANCE holds on the affected invoice.

Issue COMMIT. Re-run AP Invoice Validation to confirm the invoice passes without holds.

Script Inventory

Key Tables Affected