ETL9.1 TL_OUTTCD fails with Unique Constraint Error : Cannot insert duplicate key row in object 'dbo.PS_WRK_EMPLY_TAO5' with unique index 'PS_WRK_EMPLY_TAO5'.

(Doc ID 1562299.1)

Last updated on AUGUST 09, 2017

Applies to:

PeopleSoft Enterprise HRMS Time and Labor - Version 9.1 and later
Information in this document applies to any platform.

Symptoms

ISSUE STATEMENT
======================
TL_OUTTCD failed with unique constraint error when there are multiple rows in tl_empl_data with same Taskgroup. In other words when multiple empl_rcds on maintain time reporter data with similar taskgroup, process abends. However if Taskgroups are different on TL_EMPL_DATA process works fine.
File: e:\pt85210b-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1620 Error Position: 0 Return: 805 - [Microsoft][SQL Server Native Client
10.0][SQL Server]Cannot insert duplicate key r ow in object 'dbo.PS_WRK_EMPLY_TAO5' with unique index 'PS_WRK_EMPLY_TAO5'.

REPLICATION STEPS
=================
1) Create 2 Empl Rcds rows on TL_EMPL_DATA
2) Assign same task group to both Empl Rcds
2) Configure TIME_DEVICE_EMPL_ATT_FULLSYNC & TASK_TEMPLATE , using XML file to send data.
3) Navigate to Setup HRMS>Prod Related>Time & Labor>time Collection Device>Send setup to TCD
3) Run TL_OUTTCD
4) Get following error
File: e:\pt85210b-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt # 1620 Error Position: 0 Return: 805 - [Microsoft][SQL Server Native Client
10.0][SQL Server]Cannot insert duplicate key r ow in object 'dbo.PS_WRK_EMPLY_TAO5' with unique index 'PS_WRK_EMPLY_TAO5'. Failed SQL
stmt:INSERT INTO PS_WRK_EMPLY_TAO5 (PROCESS_INSTANCE, MSGNODENAME, TCD_ID, EMPLID, EMPL_R CD, IN_PROCESS_FLG, AUDIT_ACTN, TCD_NETWORK_ID, TCD_NODE_ID,
BADGE_ID, TIME_RPTG_STATUS, LAST_NAME, FIRST_NAME, HIRE_DT, SERVICE_DT, BIRTHDATE, RESTRICTION_PRF_ID, TASK_PRFL_TMPLT_ID, TASK_PROFILE_ID) SELECT
DISTINCT A.PROCESS_INSTANCE, A.MSGNODENAME, A.TCD_ID, A.EMPLID, A.EMPL_RCD, A.IN_PROCESS_FL G, A.AUDIT_ACTN, A.TCD_NETWORK_ID, A.TCD_NODE_ID, A.BADGE_ID,
A.TIME_RPTG_STATUS, A.LAST_NAME, A.FIR ST_NAME, A.HIRE_DT, A.SERVICE_DT, A.BIRTHDATE, A.RESTRICTION_PRF_ID, C.TASK_PRFL_TMPLT_ID, C.TASK_PR

ENVIRONMENT INFORMATION
=======================
PeopleSoft HCM Bundle 11

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