Symptoms

• AP invoices display accounting errors (ORA-95353 or zero-amount errors) or show fractional amounts on the Trial Balance.
• Invoice header base_amount does not match the correctly calculated value derived from invoice_amount * exchange_rate.
• Invoice lines or distributions have base_amount or rounding_amt values with opposite signs, or variance amounts (IPV, ERV, TRV, TIPV, TERV) that are incorrect.
• ZX tax distributions exist that are linked to the incorrect variance distributions.
• Affected invoices may be both active (non-cancelled) and cancelled.

Root Cause

Oracle Bug 9574881 caused AP invoice distributions to be created with incorrect base_amount values or with amount and base_amount having opposite signs, leading to variance distribution miscalculations. The fix script uses the Oracle GDF framework to classify affected invoices into reason categories, then corrects each category with appropriate recalculations using AP_UTILITIES_PKG.ap_round_currency and the AP events status API.

Resolution

Populate AP_TEMP_DATA_DRIVER_9574881 with the affected invoice IDs and a reason classification before running the fix script. Reason values used by the script:

• header base amount issue — invoice header base_amount is incorrect
• lines base amount issue — invoice line base_amount/rounding_amt is incorrect
• dists base amount issue — distribution base_amount is incorrect
• wrong variance — tax/variance distributions have incorrect amounts
• opposite signs — amount and base_amount have opposing signs

Run data_fix.sql via SQL*Plus. The script prompts for User Name, Responsibility Name, and optionally an email address for log delivery. It creates backup tables and then processes each reason category.

Backup tables are created for:

• AP_INVOICES_ALL (header base amount records)
• AP_INVOICE_LINES_ALL
• AP_INVOICE_DISTRIBUTIONS_ALL
• AP_SELF_ASSESSED_TAX_DIST_ALL
• ZX_LINES, ZX_LINES_SUMMARY, ZX_REC_NREC_DIST

For active (non-cancelled) invoices:

1. Corrects AP_INVOICES_ALL.base_amount using ap_utilities_pkg.ap_round_currency(invoice_amount * exchange_rate).
2. Nulls out base_amount and rounding_amt on affected invoice lines.
3. Deletes incorrect variance distributions (TRV, TERV, TIPV, REC_TAX, NONREC_TAX) from AP_INVOICE_DISTRIBUTIONS_ALL and linked ZX_REC_NREC_DIST records.
4. Deletes uncorrected ERV/IPV distributions.
5. Recalculates distribution amounts using quantity × unit price for PO-matched distributions.
6. Recalculates base_amount on reversal distributions.
7. Nulls out total_dist_amount and total_dist_base_amount.
8. Sets force_revalidation_flag = 'Y' on the invoice header.
9. Calls AP_ACCOUNTING_EVENTS_PKG.UPDATE_INVOICE_EVENTS_STATUS for each affected invoice.

For cancelled invoices:

1. Zeros base_amount and rounding_amt on cancelled/discarded lines with zero amounts.
2. Recalculates base_amount on distributions using the exchange rate.
3. Recalculates total_dist_amount and total_dist_base_amount.
4. Calls RSI_AP_Acctg_Data_Fix_PKG.repop_prepay_dists to repopulate prepayment distributions.

The script commits and updates process_flag = 'P' in the driver table. Re-validate affected non-cancelled invoices via the AP Validate Invoices program.

Script Inventory

Key Tables Affected