Case 00032977 – AP Invoice Incorrectly Appearing on R12 Trial Balance (Created in 11i)

AP Invoice Incorrectly Appearing on R12 Trial Balance (Created and Paid in 11i)

Case Number	00032977
Oracle Module	Accounts Payable (AP)
EBS Version	R12 (post-upgrade from 11i)
Functional Area	AP Trial Balance / Upgrade Data
Related Patches	Oracle Bug 13872039 (ap_ignore_tb_fix.sql)

Symptoms

The AP Trial Balance report in R12 includes invoices that were created and fully paid in 11i prior to the R12 upgrade.
These historical 11i invoices should not appear on the R12 Trial Balance because they were fully cleared in the legacy system.
The invoices cannot be removed from the Trial Balance through normal AP processing.
Reconciliation between GL and AP is affected because these phantom entries inflate AP balances.

Root Cause

Oracle Bug 13872039: During the upgrade from 11i to R12, certain fully paid AP invoices that originated in 11i were not flagged with the IGNORE_RATE_DIFFS_FLAG or the equivalent "ignore in Trial Balance" attribute in the upgraded R12 data. As a result, the R12 AP Trial Balance report picks up these legacy records and includes them as open items even though they were fully reconciled in 11i.

Resolution

This fix uses the Oracle Generic Data Fix (GDF) framework via the AP_Acctg_Data_Fix_PKG package and driver table AP_IGNORE_TB_13872039.

Step 1 – Prerequisites

Ensure the driver table AP_IGNORE_TB_13872039 has been created and populated with the affected invoice/distribution records using the companion selection script.

Step 2 – Execute the fix script

Run data_fix.sql via SQL*Plus:

sqlplus <user_name>/<password>@<database> @data_fix.sql

Step 3 – Script processing

The script performs the following:

Creates backup tables for AP_LIABILITY_BALANCE and AP_AE_LINES_ALL (and/or XLA tables in R12).
Stamps the affected records in AP_INVOICE_DISTRIBUTIONS_ALL and related tables with the flag that marks them as "ignore in Trial Balance".
Updates the driver table to set PROCESS_FLAG = 'P' for successfully processed records.
Generates an HTML log report showing fixed and unfixed transactions.

Step 4 – Validate

Re-run the AP Trial Balance report and confirm the legacy 11i invoices no longer appear.

Note: This fix targets invoices that were created AND paid in 11i before the R12 upgrade. It should not be applied to invoices that were created in 11i but paid in R12, as those may legitimately appear on the Trial Balance.

Script Inventory

File	Description
data_fix.sql	Oracle GDF fix for Bug 13872039. Sets the "ignore in Trial Balance" flag on AP invoice distributions that were fully processed in 11i but incorrectly appear on the R12 AP Trial Balance. Uses AP_Acctg_Data_Fix_PKG with logging.

Related Oracle Patches / Bugs

Bug / Patch	Description
Bug 13872039	R12 AP Trial Balance report includes invoices created and paid in 11i that should not appear after upgrade

Key Tables Affected

Table	Action
AP_IGNORE_TB_13872039	Driver table — populated by selection script; process_flag updated to 'P'
AP_INVOICE_DISTRIBUTIONS_ALL	Update to set ignore-in-Trial-Balance flag
AP_AE_LINES_ALL / XLA tables	Backup and update as applicable