ETL9.2:One Instance ST_LOADER Fails with Unique Constraint Error and Other Duplicate Errors.
Last updated on OCTOBER 30, 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.
Frequent ST LOADER errors are occurring. One Instance ST_LOADER Fails with Unique Constraint Error causing all subsequent ST_LOADER threads to fail as they seem to process the same error employee again. The first fail or error often is due to a duplicate punch or an out of sequence punch on the time-sheet. What accentuates the problem is the way all subsequent ST_LOADER jobs try to reprocess all punches again from the failed Instance and error out. This perpetual ripple effect has created some duplicate punches on the time-sheet also.
Data is coming via xml message from tcd. TL_ST_LOADER are being triggered one after another by the messages to process large amount of data. They way duplicate punches happens is as documented below. First tl_st_loader is processing the punches and to mark the punches as processed updates st_instance to y in intermediate tables. Then second one is running and processing another set of data , while second one is running the third one comes and before second one can mark the st_instance to y indicating punches has been loaded the third one picked up same transactions along with it's own and causes duplicates.
We would like to have a better understanding of the Program and also would be interested to know how we could prevent the new ST_LOADERS from erroring out.
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