Symptoms

• An AP invoice is in a corrupted partially-cancelled state: temp_cancelled_amount is populated but cancelled_date is NULL on AP_INVOICES_ALL, meaning the cancellation process was interrupted before completion.
• The invoice cannot be re-cancelled through the AP UI because the invoice data is inconsistent — tax distributions, reversal flags, and/or distribution line types are in an invalid state that prevents the standard cancellation API from completing.
• XLA accounting entries for related payments may be posted and must be undone before the invoice accounting can be reversed.

Root Cause

During an AP invoice cancellation, the process failed mid-way, leaving the invoice with temp_cancelled_amount set but cancelled_date unset. The invoice distributions, tax tables (ZX), and reversal rows were left in a mixed state. The resolution requires using the Oracle Generic Data Fix (GDF) framework to undo existing XLA accounting entries for both payments and the invoice, cleaning up all corrupt data across AP and ZX tables, correcting distribution flags, and then allowing the standard AP cancellation to be re-applied manually through the application.

Resolution — Script 01: Create Driver Table

Run 01_setup_driver_tables.sql first. Creates (drops and recreates) XXRSI_AP_INV_DRIVER_154415 with columns: org_id, invoice_id, invoice_num, invoice_type_lookup_code, quick_credit, process_flag (default 'Y').

The active version targets a single hardcoded invoice: l_invoice_id = 28579511. A commented-out alternative SELECT identifies the general pattern (invoices where invoice_amount = 0 AND cancelled_date IS NULL AND temp_cancelled_amount IS NOT NULL) which can be activated if multiple invoices require repair.

Resolution — Script 02: Apply Data Fix

Run 02_apply_data_fix.sql after Script 01. Uses v_user_id = -154415 as the update sentinel. Executes the following steps in sequence:

1. Undo Payment Accounting: Creates XXRSI_AP_PAY_UNDO_TBL_154415 (check_id, accounting_event_id, proposed_undo_date = sysdate, process_flag = 'Y') by selecting from AP_PAYMENT_HISTORY_ALL where posted_flag = 'Y' for checks linked to the driver invoice. Calls AP_ACCTG_DATA_FIX_PKG.Undo_Acctg_Entries with p_bug_no='154415', p_driver_table = pay_undo_tab, p_mode='UNDO_DEL_ADJ' to reverse and delete payment accounting entries.
2. Flag Payment Undo Failures: Sets process_flag = 'E' in the driver table for any invoice where XLA headers still remain after payment undo (joined to AP_PAYMENT_HISTORY_ALL, XLA_EVENTS, XLA_AE_HEADERS where accounting_entry_status_code <> 'U' and upg_batch_id <> -9999).
3. Undo Invoice Accounting: Creates XXRSI_AP_INV_UNDO_TBL_154415 (invoice_id, event_id) from posted invoice distributions and XLA_EVENTS rows with event_status_code = 'P'. Calls AP_ACCTG_DATA_FIX_PKG.Undo_Acctg_Entries to undo invoice accounting entries.
4. Set Driver Process Flags: Updates driver process_flag = 'D' for invoices where no posted or encumbered distributions remain; sets 'E' for others.
5. Backup AP and ZX Tables: Creates eight backup tables using CREATE TABLE AS SELECT:
   • XXRSI_AP_INV_DIST_154415 — from AP_INVOICE_DISTRIBUTIONS_ALL
   • XXRSI_AP_HOLDS_154415 — from AP_HOLDS_ALL
   • XXRSI_AP_INV_LINES_154415 — from AP_INVOICE_LINES_ALL
   • XXRSI_AP_TAX_DIST_154415 — from AP_SELF_ASSESSED_TAX_DIST_ALL
   • XXRSI_ZX_LINES_154415 — from ZX_LINES
   • XXRSI_ZX_LINE_SUM_154415 — from ZX_LINES_SUMMARY
   • XXRSI_ZX_REC_DIST_154415 — from ZX_REC_NREC_DIST
   • XXRSI_ZX_DET_FACTORS_154415 — from ZX_LINES_DET_FACTORS
6. Delete Tax Data: Deletes from ZX_LINES_SUMMARY, ZX_REC_NREC_DIST, ZX_LINES, ZX_LINES_DET_FACTORS, and AP_SELF_ASSESSED_TAX_DIST_ALL for the affected invoice.
7. Delete Invalid AWT Distributions: Deletes AWT reversal rows (reversal_flag = 'Y', parent_reversal_id IS NOT NULL) where amount does not match the parent or the parent row is missing. Then deletes remaining duplicate AWT reversal rows.
8. Delete Tax Distribution Lines: Deletes distributions with line_type_lookup_code IN ('REC_TAX','NONREC_TAX','TIPV','TERV','TRV'). Then deletes 'IPV' and 'ERV' type distributions.
9. Correct Exchange Rate for NO RATE Holds: Updates AP_INVOICES_ALL setting exchange_rate = 1, exchange_rate_type = 'User' for invoices with NO RATE holds.
10. Correct Invoice Lines:
    • Deletes AP_INVOICE_LINES_ALL rows with no remaining distributions.
    • Sets discarded_flag = NULL for PREPAY lines.
    • Updates line amount to the sum of its distributions.
    • Clears cancelled_flag and discarded_flag for non-zero ITEM, FREIGHT, MISCELLANEOUS, and AWT lines.
11. Zero Invoice Tax Amount: Updates AP_INVOICES_ALL setting total_tax_amount = 0 for affected invoices.
12. Correct Distribution Flags: Updates AP_INVOICE_DISTRIBUTIONS_ALL setting reversal_flag = 'N' and cancellation_flag = 'N' for non-discarded, non-cancelled lines that are not parent reversal rows.
13. Correct Reversal Amounts: Ensures reversal distributions have amount = -original.amount and base_amount = -original.base_amount.
14. Reset Match Status: Sets match_status_flag = 'N' via dynamic SQL for affected distributions.
15. Set Final Process Flag and Commit: Sets process_flag = 'P' in the driver table for processed invoices; commits.

Resolution — Script 03: Supplemental AP Liability Balance CCID Fix (Case 88203)

Script 03_cancel_invoice_fix.sql is a supplemental script from a related engagement (Case 88203) included with this case folder. It addresses a different problem: incorrect code_combination_id values on AP_LIABILITY_BALANCE and AP_AE_LINES_ALL rows for AP invoices.

Uses a pre-populated driver table RSI_AP_BAL_TMP_88203:

1. c1 cursor: Selects invoices with 2 or more liability balance entries in the driver table.
2. c2 cursor: Selects invoices with 3 or more entries (for secondary processing).
3. For each invoice: retrieves v_new_ccid from the credit row (inv_credit <> 0) in RSI_AP_BAL_TMP_88203.
4. c3 cursor: Joins AP_LIABILITY_BALANCE to AP_AE_LINES_ALL for the invoice where code_combination_id <> v_new_ccid and LAST_UPDATED_BY <> -88203 (not already fixed).
5. Backs up rows to RSI_AP_AE_LINES_88203 and RSI_AP_LIA_BALANCE_88203.
6. Updates AP_LIABILITY_BALANCE.CODE_COMBINATION_ID = v_new_ccid.
7. Updates AP_AE_LINES_ALL.CODE_COMBINATION_ID = v_new_ccid.
8. Optional GL_INTERFACE insertion (controlled by l_gl_transfer flag — currently set to FALSE): inserts correcting journal entries to GL_INTERFACE using RSI_DATAFIX_SEQ_ID_S.NEXTVAL and set_of_books_id=687.
9. Logs errors to RSI_AP_TB_RECON_88203; tracks processing in RSI_DATAFIX_CONTROL_88203.

Script Inventory

Key Tables Affected