OBIA PLP_W_EMPLOYEE_D_Low_effect_from_dt populates W_PURCH_COST_F with duplicates (Doc ID 2128222.1)

Last updated on MARCH 29, 2017

Applies to:

Symptoms

The mapping PLP_W_EMPLOYEE_D_Low_effect_from_dt would update the column "low effective" from date with Low Date for the row with minimum effective from date.

In a previous document "Data_of_W_Employee_D.docx" used for Bug 22852305 - W_PURCH_COST_F is getting populated with 0

the LOW_EFFECTIVE_FROM_DT is set to 01/01/1899 for EFFECTIVE_FROM_DT 5/17/2010.
 
The issue here is in the last two rows.  Both have the same LOW_EFFECTIVE_FROM_DT (5/17/2011).
 
Please provide the output of the below SQLs from DW Schema.
 
SELECT COUNT(1) FROM
(SELECT DISTINCT DATASOURCE_NUM_ID,INTEGRATION_ID
FROM W_EMPLOYEE_D);
 
SELECT DATASOURCE_NUM_ID
,INTEGRATION_ID
,LOW_EFFECTIVE_FROM_DT
,TO_CHAR(SRC_EFF_FROM_DT,'YYYY-MM-DD HH24:MI:SS') SRC_EFF_FROM_DT
,TO_CHAR(SRC_EFF_TO_DT,'YYYY-MM-DD HH24:MI:SS') SRC_EFF_TO_DT
,TO_CHAR(LOW_EFFECTIVE_FROM_DT,'YYYY-MM-DD HH24:MI:SS')
LOW_EFFECTIVE_FROM_DT_CHAR
,TO_CHAR(EFFECTIVE_FROM_DT,'YYYY-MM-DD HH24:MI:SS') EFFECTIVE_FROM_DT
,TO_CHAR(EFFECTIVE_TO_DT,'YYYY-MM-DD HH24:MI:SS') EFFECTIVE_TO_DT
,TO_CHAR(W_INSERT_DT,'YYYY-MM-DD HH24:MI:SS') W_INSERT_DT
,TO_CHAR(W_UPDATE_DT,'YYYY-MM-DD HH24:MI:SS') W_UPDATE_DT
,ETL_PROC_WID
FROM W_EMPLOYEE_D
WHERE (DATASOURCE_NUM_ID,INTEGRATION_ID)
IN (SELECT DISTINCT A.DATASOURCE_NUM_ID,A.INTEGRATION_ID
  FROM W_EMPLOYEE_D A
  WHERE EXISTS (SELECT 1
  FROM W_EMPLOYEE_D B
  WHERE A.DATASOURCE_NUM_ID = B.DATASOURCE_NUM_ID
  AND A.INTEGRATION_ID = B.INTEGRATION_ID
  AND A.LOW_EFFECTIVE_FROM_DT >= B.LOW_EFFECTIVE_FROM_DT
  AND A.LOW_EFFECTIVE_FROM_DT < B.EFFECTIVE_TO_DT
  AND A.ROW_WID <> B.ROW_WID
  )
  )
ORDER BY 1,2,3;

Cause

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