OBIA PLP_W_EMPLOYEE_D_Low_effect_from_dt populates W_PURCH_COST_F with duplicates
(Doc ID 2128222.1)
Last updated on JULY 20, 2020
Applies to:
Business Intelligence Applications Consumer - Version 11.1.1.8.1 and laterInformation in this document applies to any platform.
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 "Technote_W_PURCH_COST_F" - 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
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 |