My Oracle Support Banner

OBIA 11g: Overlapping Effective Dates In W_EMPLOYEE_D (Type-2 Configuration) (Doc ID 2526953.1)

Last updated on AUGUST 13, 2019

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.10.1 and later
Information in this document applies to any platform.

Symptoms

The issue is encountering due to overlapping Effective Dates in the W_EMPLOYEE_D table.
W_EMPLOYEE_D table configured to be a Type-2 SCD and Employee dimension data is sourced from Oracle Cloud Application 19A (11.13.19.01.0).
The fixes recommended in Doc ID 2300170.1 tech note "Instruction_Common_EmployeeDimension_EBS_Fix.doc" after getting errors from the scenario for the SIL_Employee_Addl_Attrib_Dimension interface(s) are applied also applied the fixes recommended in Doc ID 2291135.1.

Changes

In summary, have made the following changes to enable the Employee Type-2 SCD functionality:

  1. Added a Set Variable step in the package for the SIL_EmployeeDimension to set the TYPE2_FLG variable ='Y'
  2. Cloned the IKM BIAPPS Oracle Slowly Changing Dimension knowledge module to create a custom IKM entitled "THG IKM BIAPPS Oracle Slowly Changing Dimension" with the following changes
    1. Created a "Flag rows for update - fix" step by duplicating the "Flag rows for update" step and replacing the code on Command on Target with the code from the file
      “IKM_SCD_New_Step_Flag_Rows_For_Update_Fix.txt” file
    2. Created a “Delete duplicates” step by by duplicating the "Flag rows for update" step and replacing the code on Command on Target with the code from the file
      “IKM_SCD_New_Step_Delete_Duplicate_Rows_Fix.txt” file
  3. Changed the IKM on the Target in the SIL_EmployeeDimension to the custom "THG IKM BIAPPS Oracle Slowly Changing Dimension"
  4. Added CURRENT_FLG to the W_EMPLOYEE_ADDL_D_U1 constraint on the model for the W_EMPLOYEE_ADDL_D table
  5. Changed the Detection Strategy to NONE in the SIL_Employee_Addl_Attrib_Dimension_Insert.W_EMPLOYEE_ADDL_D interface

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


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