Symptoms

• AP invoice lines have generate_dists = 'Y' when they should have 'D' (distributions already generated), causing the AP Invoice Validation process to attempt to regenerate distributions that already exist.
• AP invoices have various data corruption conditions across invoice lines, distributions, holds, tax data, matching data, and prepayment applications that prevent validation, accounting, or normal processing.

Root Cause

Two separate issues are addressed by this case:

• Script 01 — A specific invoice (invoice_id = 106840) has invoice lines with generate_dists = 'Y' but the distributions were already created. This causes the validation engine to re-attempt distribution generation, creating duplicates or validation errors.
• Script 02 — Oracle-released Generic Data Fix (GDF) for Bug 13579759 (ap_one_off_scripts_fix.sql). Addresses 5 categories of AP invoice data corruption across INV (invoice), CAN (cancel), MAT (matching), TAX (tax/AWT), and PRE (prepayment) components. Uses the AP_ACCTG_DATA_FIX_PKG framework with pre-populated driver table AP_TEMP_DATA_DRIVER_13579759.

Resolution — Script 01: AP Invoice Lines generate_dists Fix

1. Back up affected lines into a backup table (lines for invoice_id = 106840 where generate_dists = 'Y').
2. Update AP_INVOICE_LINES_ALL: set generate_dists = 'D' for all lines in the backup table (identified by invoice_id and line_number).
3. Commit. Re-validate the invoice.

Resolution — Script 02: AP One-Off Invoice Corruption Fix (Oracle GDF Bug 13579759)

Populate AP_TEMP_DATA_DRIVER_13579759 with invoice IDs, line numbers, distribution IDs, and CORRUPTION_TYPE codes before running. Run the companion selection script (ap_one_off_scripts_sel.sql) to identify affected invoices if not already done. Set PROCESS_FLAG = 'Y' for rows to be processed.

The script processes one component per run, determined by the leading 3 characters of CORRUPTION_TYPE in the driver table (INV, CAN, MAT, TAX, or PRE).

• INV1: Null amounts on reversed distributions → set amount = 0, base_amount = 0 on parent and reversal distributions.
• INV2: Junk characters (chr(0)) in line/distribution descriptions causing accounting errors → strip chr(0) from description in AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL, XLA_AE_HEADERS, and XLA_AE_LINES.
• INV3: Default CCID on invoice line is -1 → set default_dist_ccid = NULL.
• INV4: org_id mismatch between AP batches and invoices → set AP_BATCHES_ALL.org_id = NULL.
• INV5: invoice_type_lookup_code NULL for upgraded invoices → set to 'STANDARD'.
• INV6: party_id and party_site_id null on upgraded invoices → populate from AP_Suppliers and AP_Supplier_Sites_All.
• INV7: generate_dists flag incorrect for expense report invoice lines → set 'D'.
• INV8: accrual_posted_flag and cash_posted_flag null for unaccounted distributions → set both to 'N'.
• INV9: Line marked discarded but has active distributions → set discarded_flag = 'N'.

• CAN1: included_tax_amount not null for cancelled invoice → set to 0 on AP_INVOICE_LINES_ALL.
• CAN2: Approval status for cancelled invoice is incorrect → insert APPROVED record in AP_INV_APRVL_HIST_ALL; set WFApproval_Status = 'NOT REQUIRED' on AP_INVOICES_ALL.

• MAT1: PO/Receipt not matched but quantities and amounts stamped → zero out quantity_billed, amount_billed, and related columns on PO_DISTRIBUTIONS_ALL; recalculate sums on PO_LINE_LOCATIONS_ALL.
• MAT2: UOM code mismatch between PO and AP → sync UNIT_MEAS_LOOKUP_CODE from PO_LINE_LOCATIONS_ALL to AP lines; sync MATCHED_UOM_LOOKUP_CODE on AP distributions.
• MAT3: quantity_invoiced mismatch between AP line and distribution → recalculate per distribution based on whether single or multiple ITEM/ACCRUAL distributions exist for the line.
• MAT4: AP distributions matched to receipts missing rcv_transaction_id → populate from driver table PO_AP_LINE_ID / PO_AP_DIST_ID columns.

• TAX1: Amount and base_amount differ for functional currency AWT distributions → set base_amount = amount.
• TAX2: 1099 Payment Report shows double amounts due to inactive supplier site → set tax_reporting_site_flag = 'N' on AP_SUPPLIER_SITES_ALL.

• PRE1: Prepayment application belongs to different org or currency → delete prepayment distributions, XLA events (unprocessed), invoice lines, XLA AE lines and headers (if event deleted), XLA distribution links, prepay history, and prepay app dists.
• PRE2: Prepayment unapplication event date earlier than application event date → sync accounting_date and period_name from parent distribution; reset accounting_event_id = NULL and match_status_flag = 'N'; delete unprocessed XLA events and prepay history rows.
• PRE3: Prepayment invoice missing ZX determination factor data → set force_revalidation_flag = 'Y' on AP_INVOICES_ALL.

Script Inventory

Key Tables Affected