OBIA 11G Unique Constraint Errors ORA-20000 for Multiple Fact Tables using a Lookup in Table W_EMPLOYEE_D (Doc ID 2023696.1)

Last updated on DECEMBER 10, 2021

Applies to:

Symptoms

Executing a load plan for OBIA 11.1.1.9.2, Datasource EBS R12.1.3, Financials and Human Resources Subject Areas:

• The following error occurs (similar errors occur for other failed steps):

• During Load Plan execution 5 sessions failed due to duplicate unique keys:
  • SIL_ARTransactionFact
  • SIL_ExpenseFact
  • SIL_ExpeseCreditCardFact
  • SIL_FixedAssetTransactionFact
  • SIL_FixedAssetBalanceFact
• All of them use a lookup in table W_EMPLOYEE_D using EFFECTIVE_FROM_DT and EFFECTIVE_TO_DT.
• They all are creating duplicate INTEGRATION_IDs in the fact tables.
• The SIL_EmployeeDimension hard codes '01-01-3714' in the EFFECTIVE_TO_DT column.
• In every place where there is a lookup for the employee associated to the transaction dates, they are being duplicated.
• The EFFECTIVE_TO_DTs are not the ACTUAL EBS Effective to date since an employee may have more than one row in the table with the transaction date less that the high value date.

Cause

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