Symptoms

• AP invoices have miscellaneous data corruption across distributions, holds, tax lines, and line records — including incorrect match_status_flag values, orphaned tax distributions, cancelled invoices with non-zero amount_paid, missing summary_tax_line_id on TAX distributions, and reversal distributions out of sync with their originals.
• Some AP invoices are stuck in an uncancellable state — the cancellation process fails or leaves the invoice in an inconsistent state with invalid accounting events, tax lines, or distribution records.
• The standard AP Validate and AP Cancel processes cannot resolve the corruption through normal processing.

Root Cause

Two separate Oracle-released Generic Data Fix (GDF) scripts address distinct but related corruption patterns:

• Bug 9738413 — Miscellaneous invoice data corruption: up to 11 distinct corruption types affecting AP_INVOICE_DISTRIBUTIONS_ALL, AP_HOLDS_ALL, AP_INVOICE_LINES_ALL, and tax tables. Caused by edge cases in the AP processing pipeline leaving distribution, hold, and line data in inconsistent states.
• Bug 9088967 — Stuck invoice cancellation: invoices cannot be cancelled through the UI because prior accounting events, tax lines, and distribution records are in a state the cancellation process cannot handle. The fix undoes accounting, removes all tax-related data, cleans up distributions and holds, and resets invoice events so that a clean cancellation can proceed.

Both scripts use the AP_ACCTG_DATA_FIX_PKG framework with pre-populated driver tables to identify and process affected invoices.

Resolution — Script 01: AP Miscellaneous Invoice Corruption Fix (Bug 9738413)

Populate AP_TEMP_DATA_DRIVER_9738413 with the invoice_id values for all affected invoices before running the fix script.

The script iterates through invoices in the driver table and applies fixes for up to 11 corruption types:

1. Type 1 — match_status_flag incorrect for posted distributions: sets match_status_flag = 'A' (Accounted).
2. Type 2 — TAX distributions missing summary_tax_line_id: deletes the orphaned TAX distribution rows from AP_INVOICE_DISTRIBUTIONS_ALL.
3. Type 3 — Orphan self-assessed tax distributions: deletes from AP_SELF_ASSESSED_TAX_DIST_ALL.
4. Type 4 — Orphan invoice tax distributions with no parent line: deletes from AP_INVOICE_DISTRIBUTIONS_ALL.
5. Type 5 — Self-assessed tax distributions with null accounting_event_id: sets force_revalidation_flag = 'Y' to trigger re-accounting.
6. Type 6 — CANNOT EXECUTE ALLOCATION hold on invoice: deletes the hold from AP_HOLDS_ALL.
7. Type 7 — Cancelled invoice with non-zero amount_paid: zeros out amount_paid and sets payment_status_flag = 'N' on AP_PAYMENT_SCHEDULES_ALL.
8. Type 8 — Cancelled invoice with Schedule payment hold: sets hold_flag = 'N' on the payment schedule.
9. Type 9 — Reversal distribution out of sync with original: updates reversal distribution amount to -1 * original_distribution.amount.
10. Type 10 — match_status_flag incorrect after validation error: sets match_status_flag = 'N' (Not validated).
11. Type 11 — match_status_flag incorrect for cancelled invoice distributions: sets match_status_flag = 'A'.

Resolution — Script 02: AP Cancel Invoice Fix (Bug 9088967)

Populate AP_TEMP_DATA_DRIVER_9088967 with the invoice_id values for all invoices that are stuck and cannot be cancelled. Invoices with AWT (Automatic Withholding Tax) distributions are automatically marked as 'X' (skip) by the script — these must be handled separately.

1. Skip AWT invoices: marks invoices with AWT distributions as status 'X' in the driver table — these are excluded from processing.
2. Undo payment accounting: calls AP_ACCTG_DATA_FIX_PKG to undo any payment accounting entries (XLA events) for the affected invoice.
3. Undo invoice accounting: calls the package to undo the invoice accounting events.
4. Back up affected rows: creates backups of:
   • AP_INVOICE_DISTRIBUTIONS_ALL
   • AP_HOLDS_ALL
   • AP_INVOICE_LINES_ALL
   • AP_SELF_ASSESSED_TAX_DIST_ALL
   • ZX_LINES
   • ZX_LINES_SUMMARY
   • ZX_REC_NREC_DIST
   • ZX_LINES_DET_FACTORS
5. Delete ZX tax data: deletes all rows from ZX_LINES_DET_FACTORS, ZX_REC_NREC_DIST, ZX_LINES, and ZX_LINES_SUMMARY for the invoice.
6. Delete self-assessed tax distributions: removes rows from AP_SELF_ASSESSED_TAX_DIST_ALL.
7. Delete AWT reversed distributions: removes reversed AWT distribution rows from AP_INVOICE_DISTRIBUTIONS_ALL.
8. Delete TAX/TIPV/TERV/TRV/REC_TAX/NONREC_TAX distributions: removes all tax-type distribution rows.
9. Delete IPV/ERV distributions: removes invoice price variance and exchange rate variance distributions.
10. Fix exchange rate: if the invoice has a NO RATE hold, sets exchange_rate = 1.
11. Delete holds: removes all holds from AP_HOLDS_ALL.
12. Delete incorrectly created reversal distributions: removes any distribution rows that were created erroneously as reversals.
13. Delete orphan reversal distributions: removes reversal distributions that have no corresponding original.
14. Delete lines with no distributions: removes invoice lines from AP_INVOICE_LINES_ALL that have no remaining distribution rows.
15. Update line amounts: recalculates and updates the amount on remaining invoice lines to match distribution totals.
16. Clear discarded/cancelled flags on lines: resets discard and cancel flags on invoice lines as appropriate.
17. Zero total_tax_amount: sets total_tax_amount = 0 on the invoice header (AP_INVOICES_ALL).
18. Reset distribution reversal/cancellation flags: clears reversal_flag and cancellation_flag on remaining distributions.
19. Reset match_status_flag: sets match_status_flag = 'N' on all remaining distributions, clearing prior validation state.
20. Reset accounting events: calls AP_ACCOUNTING_EVENTS_PKG.UPDATE_INVOICE_EVENTS_STATUS per invoice to reset the event status so that the invoice can be re-validated and cancelled cleanly.

Script Inventory

Key Tables Affected