Symptoms

• Unaccounted AP invoice and payment transactions exist in closed accounting periods, preventing period close or producing accounting errors.
• AP invoice distribution and payment history accounting dates are out of sync with the corresponding XLA_EVENTS.event_date, causing period mismatches in the GL.
• AP checks are missing exchange rates for one or more of the PAYMENT CREATED, PAYMENT MATURITY, or PAYMENT CLEARING accounting events, causing those events to fail accounting or produce incorrect foreign currency amounts.
• Duplicate entity_id rows exist in XLA_TRANSACTION_ENTITIES for the same AP transaction, causing XLA accounting errors or duplicate journal entries.
• A set of AP payment records are unposted in XLA and AP tables despite being correctly accounted, requiring a manual force-post.
• Specific AP invoices have incorrect amount_paid or payment_status_flag values requiring manual correction.

Root Cause

This case applies seven separate scripts addressing four Oracle-released GDF bugs plus three client-specific one-off data corrections:

• Bug 8529957 — Unaccounted AP transactions exist with accounting dates in closed periods. The fix sweeps event dates forward to the start of the next open or future-entry period.
• Bug 8531305 — AP transaction accounting dates (on distributions, tax distributions, payment history, etc.) are out of sync with XLA_EVENTS.event_date. The fix updates the AP-side date columns to match XLA.
• Bug 17941503 — AP checks are missing exchange rates for PAYMENT CREATED, PAYMENT MATURITY, or PAYMENT CLEARING events. The fix uses ap_utilities_pkg.get_exchange_rate and gl_currency_api.convert_amount_sql to derive and populate missing rates, and calls AP_ACCTG_DATA_FIX_PKG.undo_acctg_entries to undo any incorrectly-accounted maturity events before re-populating.
• Bug 9651687 — Duplicate entity_id values exist in XLA_TRANSACTION_ENTITIES (and XLA_TRANSACTION_ENTITIES_UPG) for the same AP transaction. The fix reassigns all XLA events, AE headers, and distribution links to the minimum (surviving) entity ID and marks retired duplicate entities as entity_code = 'MANUAL'.
• Scripts 05 and 07 — Client-specific parametric scripts that correct amount_paid and payment_status_flag on AP_INVOICES_ALL for specified invoices.
• Script 06 — Client-specific script that force-marks a specific set of 27 AP payment records as posted across XLA_EVENTS, XLA_AE_HEADERS, AP_PAYMENT_HISTORY_ALL, and AP_INVOICE_PAYMENTS_ALL.

Resolution — Script 01: Unaccounted Transactions in Closed Period Sweep (Bug 8529957)

Populate AP_TEMP_DATA_DRIVER_8529957 with the invoice_id or check_id and associated entity type (AP_INVOICES or AP_PAYMENTS) for all unaccounted transactions in closed periods. Optionally set sweep_to_date to override the target period start date.

1. For each row in the driver table, determines the start_date of the next open or future-entry GL period relative to the current accounting date.
2. AP_INVOICES entity: updates accounting_date on AP_INVOICE_DISTRIBUTIONS_ALL, AP_SELF_ASSESSED_TAX_DIST_ALL, AP_PREPAY_HISTORY_ALL, and ZX_REC_NREC_DIST to the target period start date.
3. AP_PAYMENTS entity: updates accounting_date on AP_INVOICE_DISTRIBUTIONS_ALL (AWT distributions), AP_INVOICE_PAYMENTS_ALL, and AP_PAYMENT_HISTORY_ALL.
4. Updates XLA_EVENTS.event_date to the same target date for both entity types.
5. Commits after each invoice or payment processed.

Resolution — Script 02: Accounting Date Out of Sync with XLA Events (Bug 8531305)

Populate AP_TEMP_DATA_DRIVER_8531305 with the event IDs for all transactions where AP accounting dates differ from XLA_EVENTS.event_date.

1. For each event in the driver table, reads the correct date from XLA_EVENTS.event_date.
2. Updates accounting_date on AP_INVOICE_DISTRIBUTIONS_ALL to match the XLA event date.
3. Updates accounting_date on AP_SELF_ASSESSED_TAX_DIST_ALL, ZX_REC_NREC_DIST, AP_INVOICE_PAYMENTS_ALL, AP_PAYMENT_HISTORY_ALL, and AP_PREPAY_HISTORY_ALL as applicable for the event type.
4. Commits after all updates for each event.

Resolution — Script 03: AP Missing Exchange Rate Fix (Bug 17941503)

Populate AP_TEMP_DATA_DRIVER_17941503 with the affected check_id values and the event types that are missing exchange rates (PAYMENT CREATED, PAYMENT MATURITY, PAYMENT CLEARING). Identify whether each payment is FOREIGN (foreign currency, domestic bank) or INTERNATIONAL (foreign bank account) using AP_RECONCILIATION_PKG.Case_Type.

1. For PAYMENT MATURITY events that were already (incorrectly) accounted: calls AP_ACCTG_DATA_FIX_PKG.undo_acctg_entries to undo those accounting entries before applying the rate correction.
2. For each affected check, derives the correct exchange rate using ap_utilities_pkg.get_exchange_rate for the applicable exchange rate type and date.
3. Calculates base_amount using gl_currency_api.convert_amount_sql.
4. Updates AP_CHECKS_ALL: sets exchange_rate, base_amount, and (for maturity events) maturity_exchange_rate and maturity_base_amount.
5. Updates AP_INVOICE_PAYMENTS_ALL: sets exchange_rate and payment_base_amount for affected payment rows.
6. Updates AP_PAYMENT_HISTORY_ALL: sets exchange_rate and pmt_to_base_xrate for PAYMENT CREATED, MATURITY, and CLEARING history rows.
7. Handles INTERNATIONAL payments separately, updating the bank-side exchange rate columns (bank_to_base_xrate, cleared_base_amount) as appropriate for CLEARING events.
8. Commits per check.

Resolution — Script 04: XLA Duplicate Transaction Entities Fix (Bug 9651687)

Populate XTE_MULTIPLE_9651687 with the duplicate entity_id pairs — for each AP transaction that has multiple entity rows in XLA_TRANSACTION_ENTITIES, record the min_entity_id (surviving) and the duplicate entity IDs to be retired. Uses XLA_GLOBAL_DATA_FIX_PKG framework.

1. For duplicate entities where event_count = 0: sets entity_code = 'MANUAL' on XLA_TRANSACTION_ENTITIES and XLA_TRANSACTION_ENTITIES_UPG — effectively retiring the empty duplicate.
2. For duplicate entities that have events:
   • Reassigns all rows in XLA_EVENTS from the duplicate entity ID to the min_entity_id (surviving entity).
   • Reassigns all rows in XLA_AE_HEADERS from the duplicate entity ID to the min_entity_id.
   • Updates XLA_DISTRIBUTION_LINKS.applied_to_entity_id from the duplicate to the surviving entity ID.
   • Sets entity_code = 'MANUAL' on the now-empty duplicate entity row.
3. Commits after processing each entity group.

Resolution — Scripts 05 and 07: Manual AP Invoice Amount Paid Correction

Both scripts use the same template. For a specified invoice_id and trackit reference number:

1. Creates a backup table XX_CUSTOM_AP_INV_DRIVER_<trackit> with the current AP_INVOICES_ALL row for the specified invoice.
2. Updates AP_INVOICES_ALL: sets amount_paid and payment_status_flag to the correct values for the specified invoice.
3. Commits.

Resolution — Script 06: Manual Force-Post AP Payment Records

1. Creates backup tables for AP_PAYMENT_HISTORY_ALL, AP_INVOICE_PAYMENTS_ALL, XLA_EVENTS, and XLA_AE_HEADERS for the 27 affected check and entity IDs.
2. Updates XLA_EVENTS: sets event_status_code = 'P' (Processed) and process_status_code = 'P' for the affected event IDs.
3. Updates XLA_AE_HEADERS: sets gl_transfer_status_code = 'Y' (transferred to GL) for the affected AE header IDs.
4. Updates AP_PAYMENT_HISTORY_ALL: sets posted_flag = 'Y' for the affected check IDs.
5. Updates AP_INVOICE_PAYMENTS_ALL: sets posted_flag = 'Y', accrual_posted_flag = 'Y', and cash_posted_flag = 'Y' for payments associated with the affected check IDs.
6. Commits.

Script Inventory

Key Tables Affected