Symptoms

• The PREPAY_DISTRIBUTION_ID column is NULL on tax distributions in AP_INVOICE_DISTRIBUTIONS_ALL for prepayment application and unapplication lines.
• The PREPAY_INVOICE_ID and PREPAY_LINE_NUMBER columns are also unpopulated on exclusive tax lines associated with prepayment application/unapplication lines.
• Accounting for the prepayment invoice may be incomplete or incorrect due to the missing linkage.
• Create Accounting (XLA) may produce errors or warnings related to the prepayment distribution.

Root Cause

Oracle Bug 9243855: Due to a code defect, when a prepayment is applied to a standard invoice, the tax distributions created for the prepay application/unapplication lines do not have their PREPAY_DISTRIBUTION_ID, PREPAY_INVOICE_ID, and PREPAY_LINE_NUMBER columns populated. This missing linkage prevents correct accounting regeneration for the prepayment.

Resolution

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

Run the companion selection script (ap_prepay_dist_id_pop_sel.sql) first to populate the driver table AP_TEMP_DATA_DRIVER_9243855 with the affected invoice distribution records.

Run data_fix.sql as a SYSDBA or AP schema user via SQL*Plus. The script prompts for:

• User Name (Oracle EBS username, e.g., the executing AP user)
• Responsibility (e.g., Payables Manager)
• Email ID (optional — for log delivery)

Execute command:

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

The script performs the following automatically:

1. Initializes Oracle Applications context via AP_Acctg_Data_Fix_PKG.apps_initialize.
2. Creates backup tables: AP_INV_LINES_9243855_BKP and AP_INV_DISTS_9243855_BKP.
3. Merges into the driver table to match tax distributions with their prepay distribution parent records using a hierarchical query.
4. Updates AP_INVOICE_DISTRIBUTIONS_ALL to populate PREPAY_DISTRIBUTION_ID on affected distributions.
5. Updates AP_INVOICE_LINES_ALL to populate PREPAY_INVOICE_ID and PREPAY_LINE_NUMBER on affected lines.
6. Deletes and recreates AP_PREPAY_APP_DISTS and AP_PREPAY_HISTORY_ALL records for invoices with unaccounted prepayment events.
7. Calls AP_ACCTG_PREPAY_DIST_PKG.prepay_dist_appl API to recreate the prepayment accounting distributions.
8. Issues COMMIT and writes an HTML log report of fixed and unfixed transactions.

Review the HTML log file generated in the Oracle UTL_FILE directory. Verify all records in the driver table show process_flag = 'P' (processed successfully).

Script Inventory

Related Oracle Patches / Bugs

Key Tables Affected