ETL9.1:Time Administration Fails Unique Constraint Error on Template 250 (Doc ID 1373551.1)

Last updated on JULY 13, 2016

Applies to:

PeopleSoft Enterprise HRMS Time and Labor - Version 9.1 and later
Information in this document applies to any platform.
Add ***Checked for relevance on 07-Jul-2014***

Symptoms

On :  9.1 version, Time Administration

When attempting to run TA the process abneds on rule template 250 with the following error message.

ERROR
-----------------------
File:
/vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1617 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_TL_IPT21)
violated
Failed
SQL stmt:INSERT INTO PS_TL_IPT21 (PROCESS_INSTANCE, EMPLID, EMPL_RCD, DUR, SEQ_NBR, PUNCH_TYPE, PUNCH_BEGIN, PUNCH_BEGIN_R, TIME_IN_MIN1, PUNCH_END,PUNCH_END_R, TIME_IN_MIN2, TIMEZONE, TIMEZONE2, TCD_ID, BADGE_ID, EST_GROSS,LBR_DIST_AMT, DILUTED_GROSS, CURRENCY_CD1, CURRENCY_CD2, RULE_ELEMENT_1,RULE_ELEMENT_2, RULE_ELEMENT_3, RULE_ELEMENT_4, RULE_ELEMENT_5, RULE_FLAG1,RULE_FLAG2, RULE_FLAG3, RULE_FLAG4, RULE_FLAG5, TASKGROUP, DFLT_TASKGROUP,TASK_PROFILE_ID, DFLT_TASK_PROF_ID, DFLT_TASKTMPL_ID, PERIOD_INSTANCE,PERIOD_SEQUENCE, PAYABLE_STATUS, IN_BATCH, OFFDAY_IND, TRC, TL_QUANTITY,CURRENCY_CD, COUNTRY, STATE, LOCALITY, COMP_RATECD, BILLABLE_IND,OVERRIDE_RATE, COMPANY, BUSINESS_UNIT, SETID_LOCATION, LOCATION, SETID_DEPT,DEPTID, SETID_JOBCODE, JOBCODE, POSITION_NBR, PRODUCT, CUSTOMER, ACCT_CD,BUSINESS_UNIT_PC, BUSINESS_UNIT_PF, PROJECT_ID, SETID_ACTIVITY, ACTIVITY_ID,RESOURCE_TYPE, SETID_RESOURCE,  RESOURCE_CATEGORY, RESOURCE_SUB_CAT, TASK,USER_FIELD_1, USER_FIELD_2, USER_FIELD_3, USER_FIELD_4, USER_FIELD_5,TL_RULE_ID, ORIG_TRC, ORIG_TL_QTY,RT_SOURCE, PAY_SYSTEM_FLG, ORIG_SEQ_NBR,
INITIAL_SEQ_NBR)
SELECT  DISTINCT
186808, B.EMPLID, B.EMPL_RCD, B.DUR, B.SEQ_NBR, B.PUNCH_TYPE, B.PUNCH_DTTM,
NULL, 0, B.PUNCH_END, NULL, 0, B.TIMEZONE, B.TIMEZONE2, B.TCD_ID, B.BADGE_ID,
0, 0, 0, ' ', ' ', B.RULE_ELEMENT_1, B.RULE_ELEMENT_2, B.RULE_ELEMENT_3,
B.RULE_ELEMENT_4, B.RULE_ELEMENT_5, 0, 0, 0, 0, 0, B.TASKGROUP, ' ',
B.TASK_PROFILE_ID, ' ', ' ', 0, 0, ' ', ' ', ' ', B.TRC, B.TL_QUANTITY,
B.CURRENCY_CD, B.COUNTRY, B.STATE, B.LOCALITY, B.COMP_RATECD, B.BILLABLE_IND,
B.OVERRIDE_RATE, B.COMPANY, B.BUSINESS_UNIT, B.SETID_LOCATION, B.LOCATION,
B.SETID_DEPT, B.DEPTID, B.SETID_JOBCODE, B.JOBCODE, B.POSITION_NBR, B.PRODUCT,
B.CUSTOMER, B.ACCT_CD, B.BUSINESS_UNIT_PC, B.BUSINESS_UNIT_PF, B.PROJECT_ID,
B.SETID_ACTIVITY, B.ACTIVITY_ID, B.RESOURCE_TYPE, B.SETID_RESOURCE,
B.RESOURCE_CATEGORY, B.RESOURCE_SUB_CAT, B.TASK, B.USER_FIELD_1,
B.USER_FIELD_2, B.USER_FIELD_3, B.USER_FIELD_4, B.USER_FIELD_5, ' ', ' ', 0,
B.RT_SOURCE, ' ', 0, 0 FROM PS_TL_RPTD_TIME B, PS_TL_TA_BATCHC1 A WHERE
A.PROCESS_INSTANCE = 186808 AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCDAND A.BATCH_NUM = 1 AND B.DUR BETWEEN A.START_DT AND A.END_DT AND B.PUNCH_TYPEIN ('1') AND NOT EXISTS (SELECT 'X' FROM PS_TL_IPT21 T WHERE T.PROCESS_INSTANCE= 186808 AND T.EMPLID = B.EMPLID AND T.EMPL_RCD = B.EMPL_RCD AND T.DUR = B.DUR ANDT.PUNCH_BEGIN= B.PUNCH_DTTM AND T.PUNCH_END = B.PUNCH_END)
Process
186808 ABENDED at Step TL_TA_RULES.RCLMISO2.Step04 (SQL) -- RC = 805 (108,524)

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Enter time for a day say IN OUT IN punches
2. Run TA , the process ran to success
3. Enter more time just the previous day In and out Punches and rerun TA
4. The process abends with above error message.

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