Symptoms

• AP invoice distributions and/or payment records are missing accounting_event_id values — the field is NULL on AP_INVOICE_DISTRIBUTIONS_ALL or AP_PAYMENT_HISTORY_ALL — preventing the Subledger Accounting (XLA) engine from creating journal entries for the affected transactions.
• The missing event condition causes AP transactions to remain unaccounted, which blocks period close and leaves balances unreconciled in the AP Trial Balance.
• For prepayment distributions specifically, missing events may be accompanied by an incorrect cancellation_flag that prevents event generation from being triggered by standard AP programs.
• A separate single-distribution scenario exists where one invoice distribution is missing its XLA event and entity entirely — requiring manual insertion of an XLA_EVENTS record, an XLA_TRANSACTION_ENTITIES record, and an update to AP_INVOICE_DISTRIBUTIONS_ALL.accounting_event_id.

Root Cause

Oracle Bug 8966238 addresses a class of data corruption in R12 AP where accounting_event_id is not populated on invoice distributions or payment history records. This can occur due to failed accounting program runs, mid-process errors during invoice validation or payment processing, or incomplete data migration from 11i. Without a valid accounting_event_id, the XLA accounting engine cannot link the AP transaction to its subledger journal entries.

Two resolution approaches are used:

• Script 01 (GDF Bug 8966238) — The Oracle-provided generic data fix script ap_trx_missing_event_fix.sql. Uses driver tables to identify affected invoices, prepayment distributions, and checks; calls AP_ACCOUNTING_EVENTS_PKG.Create_Events and AP_RECONCILIATION_PKG.insert_payment_history to generate missing events; commits per invoice/check.
• Script 02 (Phase A — Manual) — A targeted manual fix for a single invoice distribution (invoice_distribution_id = 44988861) where the XLA event and transaction entity records are entirely absent. Directly inserts into XLA_EVENTS and XLA_TRANSACTION_ENTITIES and updates AP_INVOICE_DISTRIBUTIONS_ALL.accounting_event_id.

Resolution — Script 01: Oracle GDF Bug 8966238 (ap_trx_missing_event_fix.sql)

Driver Tables (must be populated before execution):

• AP_TEMP_INV_DRIVER_8966238 — Invoice driver: (invoice_id, invoice_distribution_id, org_id, distribution_type, process_flag). Set process_flag = 'Y' for invoices to process.
• AP_TEMP_CHK_DRIVER_8966238 — Check driver: (check_id, org_id, process_flag). Set process_flag = 'Y' for checks to process.
• AP_TEMP_PREPAY_DRIVER_8966238 — Prepayment distribution driver: (invoice_distribution_id, distribution_type, process_flag).

Backup Tables (created inline by the script if not already present):

• AP_INV_DISTS_8966238 — Backup of AP_INVOICE_DISTRIBUTIONS_ALL for affected distributions.
• AP_SA_INV_DISTS_8966238 — Backup of AP_SELF_ASSESSED_TAX_DIST_ALL for affected self-assessed tax distributions.
• AP_INV_PAYMENTS_8966238 — Backup of AP_INVOICE_PAYMENTS_ALL rows where accounting_event_id IS NULL.
• AP_PAY_HIS_8966238 — Backup of AP_PAYMENT_HISTORY_ALL rows where accounting_event_id IS NULL.

Prepayment pre-processing:

1. Resets cancellation_flag = 'N' on AP_INVOICE_DISTRIBUTIONS_ALL and AP_SELF_ASSESSED_TAX_DIST_ALL for prepayment distributions in the driver table where the flag is incorrectly set.
2. Calls AP_ACCTG_DATA_FIX_PKG.repop_prepay_dists to repopulate prepayment accounting events before processing individual invoices.

Invoice processing loop (distinct invoice_id from driver, process_flag = 'Y'):

1. If active non-cancelled distributions exist with accounting_event_id IS NULL: calls AP_ACCOUNTING_EVENTS_PKG.Create_Events('INVOICES', ...) to generate the INVOICE VALIDATED event.
2. If cancelled distributions exist with accounting_event_id IS NULL: calls Create_Events('INVOICE CANCELLATION', ...) to generate the INVOICE CANCELLED event.
3. Commits per invoice. Logs progress via AP_ACCTG_DATA_FIX_PKG.Print_html_table HTML output.

Check processing loop (distinct check_id from driver, process_flag = 'Y'):

1. Creates output table Chk_New_Evnts_8966238 (columns: CHECK_ID, CHECK_NUMBER, CHECK_DATE, VENDOR_NAME, VENDOR_SITE_CODE, TRANSACTION_TYPE, EVENT_ID, ORG_ID) for reporting corrected payment events.
2. If no PAYMENT event exists (count_evt_created = 0): calls Create_Events('PAYMENT', ...) to generate the PAYMENT CREATED event; updates AP_INVOICE_PAYMENTS_ALL.accounting_event_id.
3. If no PAYMENT CREATED history record exists (count_his_created = 0): calls AP_RECONCILIATION_PKG.insert_payment_history to insert the missing PAYMENT CREATED payment history row.
4. If check status is VOIDED and no PAYMENT CANCELLED event exists (count_evt_cancelled = 0): calls Create_Events('PAYMENT CANCELLED', ...).
5. For other payment history types (Pay_Hist_Cur cursor — MATURITY, CLEARING) with accounting_event_id IS NULL: generates the appropriate event per transaction type.
6. Marks driver rows as 'D' (done) when the check is fully resolved.
7. Commits per check.

Resolution — Script 02: Manual Single-Distribution Fix (Phase A)

Used when a single AP invoice distribution is missing its XLA accounting event and the associated transaction entity record entirely. The fix performs the following steps sequentially (each step's output is used by the next):

1. Backup distribution: CREATE TABLE rsi_ap_inv_dist_76976 AS SELECT * FROM ap_invoice_distributions_all WHERE invoice_distribution_id = 44988861.
2. Get entity ID: SELECT XLA_TRANSACTION_ENTITIES_S.nextval FROM dual — captures the next sequence value to use as the new entity_id.
3. Insert XLA_EVENTS: inserts one row with hardcoded values:
   • event_id = 43000, application_id = 200 (AP)
   • event_type_code = 'INVOICE VALIDATED'
   • event_date = 01-JUN-2016
   • entity_id = <nextval from step 2>
   • event_status_code = 'U', process_status_code = 'U' (unprocessed)
   • event_number = 1
4. Insert XLA_TRANSACTION_ENTITIES: inserts one row linking the new entity to the AP invoice:
   • entity_id = <nextval from step 2>, application_id = 200
   • legal_entity_id = 23275
   • entity_code = 'AP_INVOICES'
   • source_id_int_1 = 25553452 (invoice_id)
   • security_id_int_1 = 85 (org_id)
   • transaction_number = '60499829'
   • ledger_id = 2021
5. Update AP_INVOICE_DISTRIBUTIONS_ALL: sets accounting_event_id = 43000 where invoice_distribution_id = 44988861.

Script Inventory

Key Tables Affected