Symptoms

• The accounting_date stored on AP_INVOICE_DISTRIBUTIONS_ALL, AP_INVOICE_PAYMENTS_ALL, and/or AP_PAYMENT_HISTORY_ALL does not match the event_date on the corresponding XLA_EVENTS row (joined via accounting_event_id).
• The accounting period derived from the AP table period_name also differs from the period implied by the XLA event date, causing discrepancies between AP subledger reporting and the GL.
• Affected rows are identified by joining AP tables to XLA_EVENTS on accounting_event_id and to GL_PERIOD_STATUSES to derive the correct period name corresponding to the event date.

Root Cause

In Oracle R12 AP, the accounting_date on AP invoice distributions, payments, and payment history records must match the event_date on the associated XLA_EVENTS row. When these dates diverge — due to data corruption, manual adjustments, or interface issues — the AP subledger and GL show inconsistent periods. The fix re-synchronises accounting_date (and period_name where applicable) on the AP tables to match the XLA event date and period.

Resolution — Script 01: Create Driver and Backup Tables

Run 01_setup_objects.sql first. All table creation statements are executed via EXECUTE IMMEDIATE with an ORA-955 (table already exists) handler to allow safe re-runs. Creates three driver tables and three backup tables:

Driver tables (created as CREATE TABLE AS SELECT):

• RSI_AP_INV_DIST_80896 — identifies AP_INVOICE_DISTRIBUTIONS_ALL rows where accounting_date <> XLA_EVENTS.event_date. Columns: invoice_num, invoice_id, aid_accounting_date, invoice_distribution_id, aid_period_name, XE_ACCOUNTING_DATE, xe_accounting_event_id, xe_period_name. Joins: AP_INVOICE_DISTRIBUTIONS_ALL → XLA_EVENTS (via accounting_event_id) → AP_INVOICES_ALL → GL_PERIOD_STATUSES (to derive xe_period_name) → AP_SYSTEM_PARAMETERS_ALL.
• RSI_AP_INV_PAY_80896 — same pattern for AP_INVOICE_PAYMENTS_ALL where accounting_date <> event_date. Same column set as the distributions driver.
• RSI_AP_INV_PAY_HIST_80896 — same pattern for AP_PAYMENT_HISTORY_ALL where accounting_date <> event_date. Columns: payment_history_id, check_number, check_id, aph_accounting_date, XE_ACCOUNTING_DATE, xe_accounting_event_id, xe_period_name. Note: no period_name column captured for payment history (only the date is corrected in the fix script).

Backup tables (created as empty shells via WHERE 1=2):

• RSI_AP_INV_DIST_BKUP_80896 — columns: INVOICE_DISTRIBUTION_ID, ACCOUNTING_DATE, PERIOD_NAME.
• RSI_AP_INV_PAY_BKUP_80896 — columns: INVOICE_PAYMENT_ID, ACCOUNTING_DATE, PERIOD_NAME.
• RSI_AP_INV_PAY_HIST_BKUP_80896 — columns: payment_history_id, ACCOUNTING_DATE.

Resolution — Script 02: Apply Accounting Date Correction

Run 02_apply_data_fix.sql after Script 01. Processes three sequential cursor loops driven by the driver tables created in Script 01. No per-record backup is performed within the fix script.

1. c1 loop — Invoice Distributions: Iterates over all rows in RSI_AP_INV_DIST_80896. For each row, updates AP_INVOICE_DISTRIBUTIONS_ALL:
   • Sets accounting_date = XE_ACCOUNTING_DATE
   • Sets period_name = xe_period_name
   • Keyed by INVOICE_DISTRIBUTION_ID
2. c2 loop — Invoice Payments: Iterates over all rows in RSI_AP_INV_PAY_80896. For each row, updates AP_INVOICE_PAYMENTS_ALL:
   • Sets accounting_date = XE_ACCOUNTING_DATE
   • Sets period_name = xe_period_name
   • Keyed by INVOICE_PAYMENT_ID
3. c3 loop — Payment History: Iterates over all rows in RSI_AP_INV_PAY_HIST_80896. For each row, updates AP_PAYMENT_HISTORY_ALL:
   • Sets accounting_date = XE_ACCOUNTING_DATE
   • Note: period_name is not updated for payment history rows (no period_name column was captured in the driver table for this source).
   • Keyed by payment_history_id

Transaction control: Each loop rolls back individually on error (per-record exception handling); a single COMMIT is issued at the end of the entire block after all three loops complete. No separate error table is used — errors surface via exception propagation.

Script Inventory

Key Tables Affected