My Oracle Support Banner

ETL9.0/9.1: TL_ST_LOADER Fails with "Unique Constraint" Error. (Doc ID 1193993.1)

Last updated on OCTOBER 02, 2018

Applies to:

PeopleSoft Enterprise HRMS Time and Labor - Version 8.9 to 9.1 [Release 8.9 to 9]
Information in this document applies to any platform.
***Checked for relevance on 17-Jul-2014***

Symptoms

TCD integration, the punches are coming from TCD Interface, one of the TL_ST_LOADER process abends causing all the following processes to also abend with the following error message:

ERROR
ORA-00001: unique constraint (SYSADM.PS_TL_MTCHD_1) violated

The issue can be reproduced at will with the following steps:
1. Punches sent through TCD Interface
2. The ST_INSTANCES triggers ST_LOADER process
3. One process picks some bad data and abended with a unique constraint error message, then the following process is trying to pick the same data as it is not processed, hence all processed continue to fail with same error message.
4. Around 300 + processes abended with above error message.

Due to this issue, employees payroll is delayed.


Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
 Steps to troubleshoot the "Unique Constraint" error
 Is there a way of resubmitting these Jobs even after deleting them from Process monitor?
 Thousands of punches have been lost - 6386 out of 6948 rows in PS_TL_ST_PCHTIME are for the same day when all these TL_ST_LOADER jobs failed and this table contained all the missing punch data for the employees.
 Please let us know how this issue could be overcome if it were to happen again.
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.