ETL9.2:One Instance ST_LOADER Fails with Unique Constraint Error and Other Duplicate Errors. (Doc ID 2036852.1)

Last updated on SEPTEMBER 13, 2016

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

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.

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