Financials (AP) Does Not Appear To Be Able to Handle Multi Period Accounting Invoices (Doc ID 1679993.1)

Last updated on SEPTEMBER 11, 2023

Applies to:

Symptoms

Background
-----------------
Doing a full data load in Test Environment. The full data load worked fine previously until transactions of the type described below were introduced to EBS.
The issue is with the mapping not being able to handle these types of Multi Period Accounting Invoices.

Problem
-----------------
OBIA Financials (AP) does not appear to be able to handle Invoices which are flagged for Multi Period Accounting from EBS 12.1.3 where the Invoice has been processed and then subsequently cancelled.


steps to reproduce
---------------------------
- The issue occurs when each duplicate line in w_ap_xact_fs has the same INVOICE_LINE_NUMBER and also has the same DISTRIBUTION_LINE_NUMBER.
- If each of the 11 lines shares the same INVOICE_LINE_NUMBER and also has the same DISTRIBUTION_LINE_NUMBER the ETL sees these 11 lines as being identical and will assign them all the same INTEGRATION_ID.

As there are a couple of AP Invoices that meet the above criteria the full data load in STAG fails whilst executing the task SIL_APTransactionFact at the point of Create Index W_AP_XACT_F_U1 (ETL). This is because there are duplicate INTEGRATION_ID values as a result of the above issue. The following error message is generated.

CREATE UNIQUE INDEX W_AP_XACT_F_U1...ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Working
----------------
So for example there may be an AP Invoice Raised in DEC-13 with 11 periods for payment finishing up in NOV-14. This will produce 11 lines for the invoice, one for each month. If each line has a unique DISTRIBUTION_LINE_NUMBER but shares the INVOICE_LINE_NUMBER there is no problem as each line will be treated by the ETL as unique.

Environment
------------------
OBIA 7.9.6.4
DAC: 11.1.1.6.4
Informatica: 9.1.0 Hotfix 2
Database: 11.2.0.3

Changes

 none

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.