ETL9.2: TL_ST_LOADER/ Unique Constraint On SYSADM.PS_TL_TR_COMMENT

(Doc ID 2164579.1)

Last updated on AUGUST 02, 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

TL_ST_LOADER/ Unique Constraint on SYSADM.PS_TL_TR_COMMENT

We are planning on going live with Tools 855/Image16 starting June 3rd and this can be a show stopper for us. In testing our bulk load of elapse time transactions we have encountered an error with one of the multiple tl_st_loader processes that run concurrently. The error is a unique constraint on PS_TL_TR_COMMENT. However, if you than run the sql manually the unique constraint is not there. At this point the transactions have already made it to timesheets but the transactions are still sitting in hte staging table (ps_tl_st_elptime. I see no code differences between our current production environment to this new tools/image system.

PeopleTools 8.55.04 - Application Engine Server
Copyright (c) 1988-2016 Oracle and/or its affiliates.
All Rights Reserved


PSAESRV started service request at 19.31.13 2016-05-13


2016-05-13-19.31.16.000000: Starting TL_ST_LIB for ST Instance 600353. (13504,206)

2016-05-13-19.31.16.000000: Starting BA000 Comp Leave Preparation. (13507,13)

2016-05-13-19.31.27.000000: Starting GA000_E Process Elapsed Time. (13507,13)

File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1723 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_TL_TR_COMMENT) violated
Failed SQL stmt: INSERT INTO PS_TL_TR_COMMENT (EMPLID , EMPL_RCD , DUR) SELECT DISTINCT EMPLID , EMPL_RCD , DUR FROM PS_TL_TR_COMMENTS S WHERE ST_INSTANCE = 600353 AND NOT EXISTS ( SELECT 1 FROM PS_TL_TR_COMMENT WHERE PS_TL_TR_COMMENT.EMPLID = S.EMPLID AND PS_TL_TR_COMMENT.EMPL_RCD=S.EMPL_RCD AND PS_TL_TR_COMMENT.DUR=S.DUR)

Process 3842792 ABENDED at Step TL_ST_LIB.FB000.Step03 (SQL) -- RC = 805 (108,524)

Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s

PSAESRV completed service request at 19.32.08 2016-05-13
 

ERROR
-----------------------
ORA-00001: unique constraint


STEPS
-----------------------

1.TL_ST_LOADER processes elapsed and deamon punched transactions
2. TL_ST_LOADER is run through the day as the transactions are coming.  Deamon process is scheduled to run every 10 minutes
3.Every once in while when two processes are running simultaneously, TL_ST_LOADER abends with above error

Due to this issue, users cannot load time into reported time tale in a timely manner and that may results in delaying payroll.

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