ETL 9.2: DUPLICATE PROCESS_INSTANCE IN TL_EMPL_DATA CAUSED BY UPG_TL91.HCTL003.STEP100 for different EMPLID and EMPL_RCD comb (Doc ID 2294664.1)

Last updated on AUGUST 07, 2017

Applies to:

PeopleSoft Enterprise HCM Time and Labor - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Other

ACTUAL BEHAVIOR
---------------
PROCESS_INSTANCE field in TL_EMPL_DATA record has same value for different EMPLID and EMPL_RCD comb

We have recently upgraded our system from HCM 9.1 to 9.2 PUM 18. We have found that in our production environment PROCESS_INSTANCE field in TL_EMPL_DATA record has same values for different EMPLID and EMPL_RCD combinations. In theory, Process Instance should be unique for an employee id and employee record combination.

After the upgrade, there are approximately 763 Process Instance values in TL_EMPL_DATA where the same values is assigned to multiple EMPLID's. To determine duplicates we ran below SQL.

select PROCESS_INSTANCE, count(DISTINCT EMPLID) from PS_TL_EMPL_DATA
where PROCESS_INSTANCE not in (select PRCSINSTANCE from PSPRCSRQST)
group by PROCESS_INSTANCE
having count(distinct EMPLID) > 1


It seems like the delivered conversion program AE section UPG_TL91.HCTL003 is itself generating the same process instance for different employees. I extracted the SQL from app engine section Step 100 Load TL_WRK01_RCD and then tweaked it a bit to see what it returns in our database and found that it is in-fact returning multiple employee/ empl_rcd combination for the same process instance. This may be happening because we were already having multiple effective dated rows for an employee in our system. Please find the attached output of the SQL it has generated.

SQL :

SELECT PROCESS_INSTANCE, COUNT(distinct EMPLID) FROM
(select EMPLID, EMPL_RCD, PROCESS_INSTANCE from
(SELECT EMPLID
 , EMPL_RCD
 , (SUM(SEQ_NBR) OVER (
  ORDER BY EMPLID
  , EMPL_RCD) * SEQ_NBR + 100000000) AS PROCESS_INSTANCE
  FROM (
 SELECT A.EMPLID
 , A.EMPL_RCD
 , COUNT(*) "SEQ_NBR"
  FROM PS_TL_EMPL_DATA A
  GROUP BY A.EMPLID
  , A.EMPL_RCD)))
  group by PROCESS_INSTANCE
having count(distinct EMPLID) > 1;

EXPECTED BEHAVIOR
-----------------------
Unique process instances need to be associated with each emplid and rcd combination.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
Check TL_EMPL_DATA for duplicates.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users see duplicate process instances for emplid and rcd combo.

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