Symptoms

• Duplicate records exist in IBY_EXT_BANK_ACCOUNTS — multiple ext_bank_account_id values represent the same physical bank account (same account number, bank, branch, and currency).
• Payments, invoices, checks, and other AP/AR transactions reference the duplicate (invalid) bank account IDs instead of the canonical (valid) account ID.
• Payment processes or bank account assignment lookups may fail or return unexpected results due to the duplicate accounts.
• The driver table IBY_EFFECTED_ACCTS_10144798 must be pre-populated with the duplicate account IDs (ranked, with rank=1 being the valid/canonical account) before the fix script can run.

Root Cause

Duplicate external bank account records were created in IBY_EXT_BANK_ACCOUNTS — typically through concurrent data entry, upgrades, or data migrations that did not enforce uniqueness on the bank account number + bank + branch + currency combination. All downstream references (payment instrument assignments, payments, documents payable, AP invoices, AP checks, AR interface lines, OKL contract and lease tables) still point to the duplicate account IDs. The fix uses a driver table (IBY_EFFECTED_ACCTS_10144798) that maps each duplicate ext_bank_account_id to its valid canonical counterpart, then remaps all downstream references and deletes the duplicates. Country-specific uniqueness logic is applied for Japan (includes bank_account_type) and New Zealand (includes ACCOUNT_SUFFIX).

Resolution

Create and populate IBY_EFFECTED_ACCTS_10144798 with all duplicate external bank account IDs, ranked so that rank=1 identifies the valid/canonical account for each duplicate group. Run the Oracle selection script (iby_dup_bnkAcct_Sel.sql) to identify the duplicate accounts and populate this table before running the fix.

Run data_fix.sql. The script uses IBY_DATA_FIX_UTILITY_PKG for HTML log output. It creates the following tables:

• Driving tables: IBY_DUP_ACCTS_10144798 (maps duplicate to valid ext_bank_account_id), IBY_DUP_INSTR_ASGN_10144798 (maps duplicate to valid instrument_payment_use_id), global temp table IBY_EFFECTED_ASSIGN_TMP
• Backup tables (structure-only): for all affected IBY, AP, AR, OKL tables

Insert into IBY_DUP_ACCTS_10144798 by joining the driver table to itself to identify duplicate-to-valid account ID mappings for all countries (standard), Japan (bank_account_type included in key), and New Zealand (ACCOUNT_SUFFIX included in key). Populate IBY_DUP_INSTR_ASGN_10144798 to map duplicate instrument payment uses to their valid equivalents.

Insert affected rows into backup tables from: IBY_EXT_BANK_ACCOUNTS, IBY_ACCOUNT_OWNERS, IBY_PMT_INSTR_USES_ALL, IBY_PAYMENTS_ALL, IBY_DOCS_PAYABLE_ALL, IBY_UPG_INSTRUMENTS, AP_PAYMENT_SCHEDULES_ALL, AP_CHECKS_ALL, AP_INVOICE_PAYMENTS_ALL, AP_INVOICES_ALL, IBY_FNDCPT_TX_EXTENSIONS, IBY_TRXN_SUMMARIES_ALL, OKL_TAA_REQUEST_DETAILS_B, OKC_RULES_B, RA_INTERFACE_LINES_ALL, and conditionally OKL contract/extract tables if the columns exist.

Update all downstream tables to replace duplicate ext_bank_account_id references with the valid canonical ID:

• IBY_DOCS_PAYABLE_ALL, IBY_PAYMENTS_ALL: update EXTERNAL_BANK_ACCOUNT_ID
• IBY_ACCOUNT_OWNERS: delete rows where the party is already an owner of the valid account; remap remaining rows to valid account ID
• IBY_FNDCPT_TX_EXTENSIONS: update INSTR_ASSIGNMENT_ID (if duplicate assignments exist)
• IBY_TRXN_SUMMARIES_ALL: update PAYER_INSTR_ASSIGNMENT_ID and PAYERINSTRID
• IBY_PMT_INSTR_USES_ALL: delete instrument uses with duplicate assignment IDs; update remaining to valid account ID
• IBY_UPG_INSTRUMENTS: update INSTRUMENT_ID and INSTR_ASSIGNMENT_ID (loop-based to handle duplicate assignments)
• AP_INVOICES_ALL, AP_PAYMENT_SCHEDULES_ALL, AP_CHECKS_ALL, AP_INVOICE_PAYMENTS_ALL: update EXTERNAL_BANK_ACCOUNT_ID
• OKL tables (OKL_TAA_REQUEST_DETAILS_B, OKC_RULES_B, OKL_CNTRCT_LINE_FIN_EXTRACT_T): update bank account/assignment ID columns
• RA_INTERFACE_LINES_ALL, OKL_TRX_AR_INVOICES_B, OKL_TXL_AR_INV_LNS_B: update customer bank account ID columns

Delete from IBY_EXT_BANK_ACCOUNTS for all ext_bank_account_id values identified as duplicates in the driver table. Commit.

Script Inventory

Key Tables Affected