My Oracle Support Banner

OBIA11g: Supervisor Dimension And Supervisor Hierarchy Has Incorrect Supervisor Values For Expats - Fusion Applications Source (Doc ID 2583526.1)

Last updated on AUGUST 30, 2019

Applies to:

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

Symptoms

Supervisor dimension and supervisor hierarchy has incorrect supervisor values for expats home country records.

Below are the scenario details:

1. For one of the expats we have multiple assignment changes made on the same date. This generated multiple records for the same assignment id and person id in the workforce event persisted stage table (W_FSN_WEVT_PS) for the same EFFECTIVE_FROM_DT.

2. As part of the OOTB logic, the column 'EFFECTIVE_TO_DT' in W_FSN_WEVT_PS table is populated with ODI variable '#HI_DATE' (having a static value of 01-01-3714).

3. To populate the 'MANAGER_ID' in the supervisor dimension staging table (W_SUPERVISOR_DS), we have the OOTB logic as:

MANAGER_ID=
CASE
WHEN WEVT.TERM_EVENT_IND=1 OR WEVT.ASG_END_EVENT_IND=1 THEN SUP.MANAGER_PRV_ID
ELSE SUP.MANAGER_ID
END

WEVT: W_FSN_WEVT_PS
SUP: W_FSN_WEVT_SUP_PS

JOIN CRITERIA:

WEVT.ASSIGNMENT_ID = SUP.ASSIGNMENT_ID AND
WEVT.DATASOURCE_NUM_ID = SUP.DATASOURCE_NUM_ID AND
SUP.MANAGER_TYPE = 'LINE_MANAGER' AND
WEVT.EFFECTIVE_FROM_DT >= SUP.EFFECTIVE_START_DATE AND
WEVT.EFFECTIVE_FROM_DT <= SUP.EFFECTIVE_END_DATE

4. As all the records in-focus in the workforce event persisted stage table (W_FSN_WEVT_PS) are getting qualified (as the EFFECTIVE_TO_DT has the value of 01-01-3714) based on the above join criteria and for one of the records we have ASG_END_EVENT_IND=1, this is causing the MANAGER_ID to populate incorrectly with the previous manager id.

5. This issue is resulting in incorrect MANAGER_ID for expats home country in W_SUPERVISOR_D which in turn is resulting in incorrect supervisor
hierarchy (W_SUPERVISOR_RH) as the source for the hierarchy is the supervisor dimension table (W_SUPERVISOR_D).

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
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.