Symptoms

• XLA_AE_HEADERS rows exist for AP invoice events (application_id=200, February 2019, ledger_id=7244) with gl_transfer_status_code = 'Y' (transferred) and accounting_entry_status_code = 'F' (final), but no corresponding rows exist in GL_IMPORT_REFERENCES — meaning the subledger accounting was marked as transferred to the GL but no actual GL journal lines were created.
• The affected AP invoice distributions remain in a posted state (POSTED_FLAG = 'Y', ACCRUAL_POSTED_FLAG = 'Y') despite the absence of GL journal entries, causing the AP subledger to show balances that do not exist in the GL.
• Standard GL Transfer cannot re-process these headers because they are already flagged as transferred.

Root Cause

The XLA accounting entry creation and GL transfer process completed for a set of AP invoice headers but the corresponding GL_IMPORT_REFERENCES rows (which link XLA subledger lines to GL journal lines) were never created — or were deleted after transfer. The headers are stuck in a state that prevents normal re-transfer. The resolution is to delete the orphaned XLA accounting structures (headers, lines, and distribution links), reset the associated XLA events to unprocessed, and reset the AP invoice distributions to unposted so the full Create Accounting → Transfer to GL sequence can be repeated.

Resolution — Script 01: Driver Table Setup (Case 158080 — Supplemental)

Script 01_setup_driver_tables.sql is labeled for Case 158080 and addresses a related but separate AP Liability Balance CCID problem included with this engagement. It uses EXECUTE IMMEDIATE to create a driver table named RSI_AP_DRIVER_TABLE_158080.

Resolution — Script 02: AP Liability Balance CCID Fix (Case 158080 — Supplemental)

Script 02_apply_data_fix.sql is also labeled for Case 158080 and corrects incorrect code_combination_id values on AP_LIABILITY_BALANCE and AP_AE_LINES_ALL. Logic is identical to the Case 88203 liability balance fix pattern:

1. Selects invoices with 2+ liability balance entries from pre-populated driver table RSI_AP_DRIVER_TABLE_158080.
2. For each invoice: retrieves the correct CCID from the credit row (inv_credit <> 0) in the driver.
3. Joins AP_LIABILITY_BALANCE to AP_AE_LINES_ALL where code_combination_id <> v_new_ccid and LAST_UPDATED_BY <> -158080.
4. Backs up rows to RSI_AP_AE_LINES_158080 and RSI_AP_LIA_BALANCE_158080.
5. Updates AP_LIABILITY_BALANCE.CODE_COMBINATION_ID and AP_AE_LINES_ALL.CODE_COMBINATION_ID to the correct value.
6. Optional GL_INTERFACE journal entry insertion (controlled by l_gl_transfer flag — currently FALSE). Uses RSI_GL_INTERFACE_158080 as the GL interface staging table.
7. Logs errors to RSI_AP_TB_RECON_158080.

Resolution — Script 03: Operational XLA Re-processing Fix

Script 03_supplemental_fix.sql is the primary operational fix for Case 00166693. It is plain SQL (no PL/SQL wrapper) and executes the following steps:

1. Create Driver Table:

   CREATE TABLE rsi_ap_driver_table_166693A AS
   SELECT ... FROM XLA_AE_HEADERS xah
   JOIN XLA_TRANSACTION_ENTITIES xte ON ...
   WHERE xah.application_id = 200
     AND xte.entity_code = 'AP_INVOICES'
     AND xah.period_name LIKE '%FEB-19%'
     AND xah.ledger_id = 7244
     AND xah.gl_transfer_status_code = 'Y'
     AND xah.accounting_entry_status_code = 'F'
     AND NOT EXISTS (SELECT 1 FROM GL_IMPORT_REFERENCES gir
                     WHERE gir.gl_sl_link_id = xah.gl_sl_link_id)
   GROUP BY invoice_id, event_id, ae_header_id, ledger_id,
            period_name, group_id, account

   Creates three indexes on the driver: RSI_AP_DRIVER_IDX1_166693 (INVOICE_ID), RSI_AP_DRIVER_IDX2_166693 (EVENT_ID), RSI_AP_DRIVER_IDX3_166693 (AE_HEADER_ID).
2. Create Backup Tables: Five snapshot backups before any changes:
   • rsi_xla_events_166693A — from XLA_EVENTS keyed by EVENT_ID
   • rsi_xla_headers_166693A — from XLA_AE_HEADERS keyed by AE_HEADER_ID
   • rsi_xla_lines_166693A — from XLA_AE_LINES keyed by AE_HEADER_ID
   • rsi_xla_dist_links_166693A — from XLA_DISTRIBUTION_LINKS keyed by AE_HEADER_ID
   • rsi_ap_inv_dist__166693A — from AP_INVOICE_DISTRIBUTIONS_ALL keyed by INVOICE_ID
3. Delete XLA Accounting Structures: Removes the orphaned accounting rows keyed by AE_HEADER_ID from the driver:
   • DELETE FROM XLA_AE_HEADERS WHERE ae_header_id IN (SELECT ae_header_id FROM rsi_ap_driver_table_166693A)
   • DELETE FROM XLA_AE_LINES WHERE ae_header_id IN (...)
   • DELETE FROM XLA_DISTRIBUTION_LINKS WHERE ae_header_id IN (...)
4. Reset XLA Events:

   UPDATE XLA_EVENTS
   SET event_status_code = 'U',
       process_status_code = 'U'
   WHERE event_id IN (SELECT event_id FROM rsi_ap_driver_table_166693A)

   Resets affected events to unprocessed so Create Accounting can re-run.
5. Reset AP Invoice Distributions:

   UPDATE AP_INVOICE_DISTRIBUTIONS_ALL
   SET POSTED_FLAG = 'N',
       ACCRUAL_POSTED_FLAG = 'N'
   WHERE invoice_id IN (SELECT invoice_id FROM rsi_ap_driver_table_166693A)

   Resets distributions to unposted so the GL transfer can re-process them.
6. COMMIT.

After Script 03 completes, the standard AP Create Accounting concurrent program and Transfer Journal Entries to GL process can be re-run for the affected invoices.

Script Inventory

Key Tables Affected