J_Dim_PS_D_EMPL_JOB_4 And Delete Strategy Fail To Update Change In CURRENT_IND (pos 223) Field. (Doc ID 2199084.1)

Last updated on OCTOBER 31, 2016

Applies to:

PeopleSoft Enterprise EPM HCM Warehouse - Version 9.1 and later
Information in this document applies to any platform.

Symptoms

On: PeopleSoft Enterprise EPM Foundation 9.1; DataStage 8.5 ETL Jobs

ACTUAL BEHAVIOR
=================
J_Dim_PS_D_EMPL_JOB_4 (EPM91 - Bundle12) and delete strategy fail to update change in CURRENT_IND (pos 223) field.

TEST CASE
==========
in ps_job confirm a job's latest effdt using (2016-01-01 for discussion) row has corresponding data in PS_D_EMPL_JOB and also this row's CURRENT_IND = 'Y' like below:
effdt = 2016-01-01, CURRENT_IND = 'Y', Data_Origin = 'S'

now in ps_job, edit latest effdt (2016-01-01) to a prior date (2015-09-01 )and since effdt is a key, with delete strategy is on, we now have 2 rows of data like below:
effdt = 2015-09-01, CURRENT_IND = 'N', Data_Origin = 'S'
effdt = 2016-01-01, CURRENT_IND = 'Y', Data_Origin = 'D'

The CURRENT_IND now has the bad data.

I believe the data should looks like
effdt = 2015-09-01, CURRENT_IND = 'Y', Data_Origin = 'S'
effdt = 2016-01-01, CURRENT_IND = 'N', Data_Origin = 'D'

The above situations seems only happen when you change the last EFFDT'ed row of data to a prior date.

Additional Issue(s)

Found an issue with this CURRENT_IND and effdt.
We do have current and future effdt'ed ps_job data in the system and I found we have CURRENT_IND = 'Y' for future dated row.
so bottom line is the job should only consider rows of data with effdt less than or equal current for CURRENT_IND = 'Y'.

Found an issue with 2015-01-01, we than add new row as correction with same effdt but effseq = 2, but this new row was always rejected.
"If Not(HASH_PS_D_EMPL_JOB_SCD_LKP.NOTFOUND) and PS_D_EMPL_JOB4_Load.EFFDT < HASH_PS_D_EMPL_JOB_SCD_LKP.EFFDT and HASH_PS_D_EMPL_JOB_lkp.NOTFOUND Then 'Y' Else 'N'"
HASH_PS_D_EMPL_JOB_SCD_LKP.EFFDT now has 2016-01-01 as effdt that is why this row was not updated.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms