ETL9.2: TL_ST_LOADER/ Unique Constraint On SYSADM.PS_TL_TR_COMMENT
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.
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
ORA-00001: unique constraint
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms