Symptoms

• AP checks show an incorrect status_lookup_code of 'CLEARED BUT UNACCOUNTED' when the Payables Reconciled Accounting option is set to N — these checks should be in 'CLEARED' status.
• After a supplier merge, the remit-to supplier columns (REMIT_TO_SUPPLIER_NAME, REMIT_TO_SUPPLIER_ID, REMIT_TO_SUPPLIER_SITE, REMIT_TO_SUPPLIER_SITE_ID, RELATIONSHIP_ID) on AP_INVOICES_ALL, AP_CHECKS_ALL, AP_RECURRING_PAYMENTS_ALL, and AP_PAYMENT_SCHEDULES_ALL are not updated to reflect the merged supplier, causing payment routing errors.
• AP invoices have various data corruption conditions (incorrect flags, orphaned distributions, tax data inconsistencies, prepayment issues) that prevent validation, accounting, or processing — see Oracle GDF Bug 13579759 corruption types (also addressed in Case 00040385).
• AP invoices have incorrect amount_paid, amount_remaining, payment_status_flag, or gross_amount values on payment schedules, causing incorrect aging, duplicate payment risk, or payment processing failures. Overpaid invoices are identified and flagged for business user review.

Root Cause

Four separate Oracle-released GDF scripts address distinct corruption patterns:

• Bug 12582979 — When Payables Reconciled Accounting is disabled (option = N), AP checks that were cleared should have status_lookup_code = 'CLEARED', not 'CLEARED BUT UNACCOUNTED'. Similarly, 'RECONCILED' status may need correction depending on context.
• Bug 17965275 — After a supplier merge, the remit-to columns on AP transactions and payment records are not updated by the merge process, leaving stale supplier references that cause remittance and payment routing to use the old (merged-away) supplier details.
• Bug 13579759 — Same Oracle GDF ap_one_off_scripts_fix.sql as applied in Case 00040385. Covers 5 component categories (INV, CAN, MAT, TAX, PRE) with 16 corruption types across AP invoice, line, distribution, batch, tax, matching, and prepayment data. Refer to Case 00040385 for full corruption type details.
• Bug 17942317 — Payment schedule gross_amount, amount_paid, amount_remaining, and payment_status_flag values are inconsistent with actual payment activity. Invoices with Reason = 'Incorrect Gross Amount on Schedule' also have PAY_CURR_INVOICE_AMOUNT recalculated. Overpaid invoices are logged to AP_OVERPAID_INVS_17942317 and excluded from automated correction — these require manual business review.

Resolution — Script 01: Wrong Check Status Fix (Bug 12582979)

Populate AP_TEMP_DATA_DRIVER_12582979 with the check_id values for all checks with incorrect status. Set PROCESS_FLAG = 'Y' for rows to be processed.

1. For each check in the driver table, evaluates the current status_lookup_code on AP_CHECKS_ALL.
2. Updates AP_CHECKS_ALL.status_lookup_code using DECODE logic:
   • 'CLEARED BUT UNACCOUNTED' → 'CLEARED'
   • 'RECONCILED' → 'RECONCILED' (retained; corrects other status variants as applicable)
3. Marks the driver table row as processed (PROCESS_FLAG = 'D').
4. Commits.

Resolution — Script 02: Supplier Merge Remit Columns Fix (Bug 17965275)

Populate the four driver tables with the affected transaction IDs before running:

• AP_TEMP_INVOICES_17965275 — affected invoice_id values
• AP_TEMP_CHECKS_17965275 — affected check_id values
• AP_TEMP_RECUR_INV_17965275 — affected recurring_payment_id values
• AP_TEMP_PAY_SCH_17965275 — affected invoice_id / payment schedule rows

1. AP_INVOICES_ALL: sets REMIT_TO_SUPPLIER_NAME, REMIT_TO_SUPPLIER_ID, REMIT_TO_SUPPLIER_SITE, REMIT_TO_SUPPLIER_SITE_ID, and RELATIONSHIP_ID to NULL for all invoices in AP_TEMP_INVOICES_17965275.
2. AP_CHECKS_ALL: nulls out the same five remit-to columns for all checks in AP_TEMP_CHECKS_17965275.
3. AP_RECURRING_PAYMENTS_ALL: nulls out the same five remit-to columns for all recurring payment records in AP_TEMP_RECUR_INV_17965275.
4. AP_PAYMENT_SCHEDULES_ALL: synchronizes remit-to columns from AP_INVOICES_ALL for all payment schedule rows in AP_TEMP_PAY_SCH_17965275.
5. Commits after each table update.

Resolution — Script 03: AP One-Off Invoice Corruption Fix (Bug 13579759)

This script is identical to 02_ap_one_off_fix.sql from Case 00040385 — the same Oracle GDF ap_one_off_scripts_fix.sql applied under a different case number. Refer to Case 00040385 for the full description of all 16 corruption types across the INV, CAN, MAT, TAX, and PRE components.

Uses AP_ACCTG_DATA_FIX_PKG framework with driver table AP_TEMP_DATA_DRIVER_13579759. One component (INV, CAN, MAT, TAX, or PRE) is processed per run. Populate the driver table with the correct CORRUPTION_TYPE codes and run the selection script (ap_one_off_scripts_sel.sql) before executing.

Resolution — Script 04: Incomplete Payment Schedules Fix (Bug 17942317)

Populate AP_TEMP_DATA_DRIVER_17942317 with the affected invoice_id, org_id, and Reason values. Valid Reason values include 'Incorrect Gross Amount on Schedule' (handled first) and other reasons for amount/status discrepancies. Set PROCESS_FLAG = 'Y'.

1. For invoices with Reason = 'Incorrect Gross Amount on Schedule':
   • Recalculates AP_INVOICES_ALL.PAY_CURR_INVOICE_AMOUNT using ap_utilities_pkg.ap_round_currency, accounting for retainage flag and payment_cross_rate.
   • Updates AP_PAYMENT_SCHEDULES_ALL.gross_amount and inv_curr_gross_amount from the recalculated invoice amounts.
2. For invoices with a non-null validation_request_id or active checkrun_id: skips processing (invoice is in-flight).

1. Before correcting payment schedules, calls AP_ACCTG_DATA_FIX_PKG.undo_acctg_entries (mode 'UNDO_DEL_ADJ') for any posted checks linked to affected invoices, to undo accounting before amount corrections.
2. Recalculates amount_paid from AP_INVOICE_PAYMENTS_ALL, deducting prepayment amounts.
3. If AP_INVOICES_ALL.amount_paid differs from the recalculated value: updates it.
4. Overpaid detection: if |amount_paid| > |actual_amount_topay|, inserts a row into AP_OVERPAID_INVS_17942317 and sets amount_remaining = 0, payment_status_flag = 'Y' on the payment schedule — then skips the remaining correction steps for that invoice (requires business user action).
5. Recalculates amount_remaining per payment schedule line by iterating schedules in due-date order and allocating remaining paid amount against each schedule's gross_amount.
6. Corrects payment_status_flag on both AP_PAYMENT_SCHEDULES_ALL and AP_INVOICES_ALL:
   • Initially sets 'P' (Partially Paid) on all schedules/invoice.
   • Sets 'N' (Unpaid) where amount_remaining = gross_amount (less AWT allocation).
   • Sets 'Y' (Fully Paid) where amount_remaining = 0 and appropriate payment/prepay conditions are met.
   • Updates the invoice header payment_status_flag based on the resulting schedule statuses.
7. Marks driver table rows as processed (PROCESS_FLAG = 'D'). Commits after each invoice.

Script Inventory

Key Tables Affected