Case 00030917 – AP 11i Invoice Accounting Events Duplicated — Reassign Unique Event IDs and Correct Distribution CCIDs

AP 11i Invoice Accounting Events Duplicated — Reassign Unique Event IDs and Correct Distribution CCIDs

Case Number	00030917
Oracle Module	Accounts Payable (AP)
EBS Version	11i
Functional Area	AP Invoices / Accounting Events / Distribution CCID
Related Patches	None

Symptoms

Multiple AP invoice distributions in AP_INVOICE_DISTRIBUTIONS_ALL share the same accounting_event_id, even though they belong to different invoices or invoice groups.
Duplicate rows exist in AP_ACCOUNTING_EVENTS_ALL — multiple event rows with the same accounting_event_id pointing to the same source_id — causing the GL Transfer and AP accounting processes to fail or produce incorrect journals.
For a separate group of invoices, AP_INVOICE_DISTRIBUTIONS_ALL contains incorrect dist_code_combination_id (CCID) values that cause postings to the wrong GL accounts.

Root Cause

In Oracle 11i, AP_ACCOUNTING_EVENTS_ALL and AP_INVOICE_DISTRIBUTIONS_ALL must maintain a one-to-one relationship between each distribution group and its accounting event. For this case, a subset of distributions were assigned a shared accounting_event_id that already belonged to other invoices, and duplicate event rows were created in AP_ACCOUNTING_EVENTS_ALL. The fix deletes the duplicate event rows and reassigns each affected distribution (and its related distributions) a fresh unique event ID from AP_ACCOUNTING_EVENTS_S.NEXTVAL, then updates AP_ACCOUNTING_EVENTS_ALL to use the same new IDs. A separate set of scripts corrects wrong CCIDs on distributions for a broader set of invoices.

Resolution

Scripts 01–05 and 06 — Reassign accounting event IDs (Groups A–E and specific invoices)

Each script applies the same pattern to a different group of affected invoice distributions:

Back up the affected rows from AP_INVOICE_DISTRIBUTIONS_ALL and AP_ACCOUNTING_EVENTS_ALL into per-group backup tables (e.g., RSI_AP_DIST_00030917A, RSI_AP_ACCTG_EVENTS_00030917A).
Delete the duplicate/orphan event row from AP_ACCOUNTING_EVENTS_ALL — the event ID that was incorrectly shared across distributions is removed.
Assign new event IDs to lead distributions: for each group's primary distribution (the first distribution in the group), update AP_INVOICE_DISTRIBUTIONS_ALL.accounting_event_id to AP.AP_ACCOUNTING_EVENTS_S.NEXTVAL.
Cascade the new event ID to related distributions: for any additional distributions that belong to the same invoice/group as the lead distribution, update their accounting_event_id to match the lead distribution's new value (using a sub-select on the lead invoice_distribution_id).
Update AP_ACCOUNTING_EVENTS_ALL: for each old event ID row, update its accounting_event_id primary key to the new value taken from the lead distribution (sub-select on invoice_distribution_id), filtered by source_table = 'AP_INVOICES' and the specific source_id (invoice ID).
Commit after each group.

Script 06 (06_data_fix_specific_invoices.sql) applies the same event reassignment pattern to a specific pair of invoices that were also affected, without a preceding delete step.

Script 07 — Correct distribution CCIDs (Group F)

Run 07_data_fix_group_f.sql. For a larger set of invoices with incorrect dist_code_combination_id values:

Back up all affected distributions into RSI_AP_DIST_ACCT_30917F.
Update AP_INVOICE_DISTRIBUTIONS_ALL.dist_code_combination_id per invoice ID to the correct CCID value. Each invoice in the affected group receives its own specific CCID update (19 invoices in total, each with a defined correct CCID).
Commit.

Note: Scripts 01–05 must be run in order. Script 06 covers two specific invoices that overlap with Group D event IDs — verify no double-processing before running. Script 07 is independent and targets a different set of invoices (CCID correction only, no event ID changes). The correct CCID values in Script 07 must be verified against the GL chart of accounts and AP accounting rules before executing.

Script Inventory

File	Description
01_data_fix_group_a.sql	Group A: Backs up and reassigns accounting_event_ids for 3 invoice distribution groups (4 lead distributions). Deletes 3 duplicate event rows. Updates AP_ACCOUNTING_EVENTS_ALL with new event IDs.
02_data_fix_group_b.sql	Group B: Backs up and reassigns accounting_event_ids for 4 invoice distribution groups. Deletes 1 duplicate event row. Updates AP_ACCOUNTING_EVENTS_ALL.
03_data_fix_group_c.sql	Group C: Backs up and reassigns accounting_event_ids for 3 invoice distribution groups. Deletes 1 duplicate event row. Updates AP_ACCOUNTING_EVENTS_ALL.
04_data_fix_group_d.sql	Group D: Backs up and reassigns accounting_event_ids for 4 invoice distribution groups. Deletes 3 duplicate event rows. Updates AP_ACCOUNTING_EVENTS_ALL.
05_data_fix_group_e.sql	Group E: Backs up and reassigns accounting_event_ids for 5 invoice distribution groups (including one with a related distribution cascade). No delete step. Updates AP_ACCOUNTING_EVENTS_ALL.
06_data_fix_specific_invoices.sql	Specific invoices fix: Reassigns accounting_event_ids for a pair of specific invoice distributions (overlapping with Group D event IDs). No delete step.
07_data_fix_group_f.sql	Group F: Corrects dist_code_combination_id values for 19 invoices with incorrect CCIDs. Backs up to RSI_AP_DIST_ACCT_30917F. No event ID changes.

Key Tables Affected

Table	Action
AP_ACCOUNTING_EVENTS_ALL	Delete duplicate event rows; update accounting_event_id primary key to new sequence values
AP_INVOICE_DISTRIBUTIONS_ALL	Update accounting_event_id to new AP_ACCOUNTING_EVENTS_S.NEXTVAL values per distribution group; update dist_code_combination_id for Group F invoices