Symptoms

• For two AP invoices, the trx_number stored in the ZX tax tables (ZX_LINES, ZX_LINES_SUMMARY, and ZX_LINES_DET_FACTORS) does not match the invoice_num in AP_INVOICES_ALL.
• Tax reporting or tax reconciliation processes fail or produce incorrect results because the ZX tax lines reference an incorrect transaction number.
• The ZX rows were created with a stale or incorrect trx_number, causing a mismatch with the AP invoice record.

Root Cause

The E-Business Tax (ZX) tax line tables (ZX_LINES, ZX_LINES_SUMMARY, ZX_LINES_DET_FACTORS) store the AP invoice number in a trx_number column. For the two affected invoices, this value was populated incorrectly at invoice creation time and does not match the current invoice_num in AP_INVOICES_ALL. The fix updates trx_number to the correct invoice number across all three ZX tables, sets cancel_flag = 'Y' on the ZX lines where applicable, and resets the object_version_number as required by the ZX data model.

Identification SQL

Use the following query to identify invoices where the ZX transaction number does not match the AP invoice number:

SELECT ai.invoice_id, ai.invoice_num, zl.trx_number
FROM   ap_invoices_all ai
       JOIN zx_lines zl ON zl.trx_id = ai.invoice_id
                       AND zl.application_id = 200
                       AND zl.entity_code = 'AP_INVOICES'
WHERE  ai.invoice_id IN (&invoice_ids)
  AND  zl.trx_number != ai.invoice_num;

Resolution

Run data_fix.sql (backup section). Creates backup copies of all affected ZX rows before any updates:

• Backup of ZX_LINES_DET_FACTORS rows for the affected invoice IDs
• Backup of ZX_LINES rows for the affected invoice IDs
• Backup of ZX_LINES_SUMMARY rows for the affected invoice IDs

For each affected invoice, update ZX_LINES_DET_FACTORS to set trx_number to the correct invoice number (matching AP_INVOICES_ALL.invoice_num). The update is keyed on trx_id = <invoice_id> and application_id = 200.

For each affected invoice, update ZX_LINES to:

• Set trx_number to the correct invoice number
• Set cancel_flag = 'Y' for the affected tax lines
• Set object_version_number = 1296120000 (resets the optimistic locking version number as required by the ZX data model)

The update is applied separately for each of the two invoice IDs.

For each affected invoice, update ZX_LINES_SUMMARY to set trx_number to the correct invoice number. The update is keyed on trx_id = <invoice_id>.

Commit after all updates. Verify that trx_number in all three ZX tables now matches invoice_num in AP_INVOICES_ALL for both affected invoices. Re-run any tax reports or reconciliation processes that previously showed the mismatch.

Script Inventory

Key Tables Affected