ETL9.1 - Rapid Time processing abending at Step TL_ST_LIB.LDPCHTIM.Step020 (SQL) (Doc ID 2236004.1)

Last updated on MAY 15, 2017

Applies to:

PeopleSoft Enterprise HCM Time and Labor - Version 9.1 to 9.1 [Release 9]
Information in this document applies to any platform.

Symptoms

Rapid Time processing (TL_RAPIDTIME and TL_SUBMITALL) abends with error when punch time is being submitted for an employee on a day where an elapsed punch already exists:

File: e:\pt85218b-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1620 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_TL_RPTD_TIME) violated
Failed SQL stmt:INSERT INTO PS_TL_RPTD_TIME (EMPLID, EMPL_RCD, DUR, SEQ_NBR, PUNCH_TYPE, PUNCH_TIME, PUNCH_DTTM, PUNCH_END, TIMEZONE, TIMEZONE2, ST_INSTANCE, BADGE_ID, TCD_ID, RULE_ELEMENT_1, RULE_ELEMENT_2, RULE_ELEMENT_3, RULE_ELEMENT_4, RULE_ELEMENT_5, TASKGROUP, OPRID, TASK_PROFILE_ID, TCD_SUPERVISR_ID, OVERRIDE_RSN_CD, RT_SOURCE, REPORTED_STATUS, OPRID_LAST_UPDT, DTTM_MODIFIED, DTTM_CREATED, 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, SYNCID, LASTUPDDTTM) SELECT A.EMPLID, A.EMPL_RCD, A.DUR, A.SEQ_NBR, A.PUNCH_TYPE, DECODE(((A.PUNCH_BEGIN_R) + NUMTODSINTERVAL(T.BASEOFFSET, 'MINUTE')), NULL, TO_TIMESTAMP(NULL), TO_TIMESTAMP('1900-01-01-' || TO_CHAR(CAST((((A.PUNCH_BEGIN_R) + NUMTODSINTERVAL(T.BASEOFFSET, 'MINUTE'))) AS TIMESTAMP),'HH24.MI.SS.FF'), 'YYYY-MM-DD-HH24.MI.SS.FF')), B.PUNCH_DTTM, A.PUNCH_END_R, A.TIMEZONE, A.TIMEZONE2, 3205, A.BADGE_ID, A.TCD_ID, A.RULE_ELEMENT_1, A.RULE_ELEMENT_2, A.RULE_ELEMENT_3, A.RULE_ELEMENT_4, A.RULE_ELEMENT_5, A.TASKGROUP, B.OPRID, A.TASK_PROFILE_ID, B.TCD_SUPERVISR_ID, B.OVERRIDE_RSN_CD, B.RT_SOURCE, 'SB', '99103732', CAST(SYSTIMESTAMP AS TIMESTAMP) , CAST(SYSTIMESTAMP AS TIMESTAMP) , A.TRC, A.TL_QUANTITY, A.CURRENCY_CD, A.COUNTRY, A.STATE, A.LOCALITY, A.COMP_RATECD, CASE WHEN A.BILLABLE_IND =' ' THEN 'N' ELSE A.BILLABLE_IND END, A.OVERRIDE_RATE, A.COMPANY, A.BUSINESS_UNIT, A.SETID_LOCATION, A.LOCATION, A.SETID_DEPT, A.DEPTID, A.SETID_JOBCODE, A.JOBCODE, A.POSITION_NBR, A.PRODUCT, A.CUSTOMER, A.ACCT_CD, A.BUSINESS_UNIT_PC, A.BUSINESS_UNIT_PF, A.PROJECT_ID, A.SETID_ACTIVITY, A.ACTIVITY_ID, A.RESOURCE_TYPE, A.SETID_RESOURCE, A.RESOURCE_CATEGORY, A.RESOURCE_SUB_CAT, A.TASK, A.USER_FIELD_1, A.USER_FIELD_2, A.USER_FIELD_3, A.USER_FIELD_4, A.USER_FIELD_5, 0, CAST(SYSTIMESTAMP AS TIMESTAMP) FROM PS_TL_MTCHD_14 A, PSTZOFFSET T , PS_TL_ST_PCHTIME B WHERE A.PROCESS_INSTANCE = 1353841 AND B.ST_INSTANCE = 3205 AND B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.PUNCH_DTTM = A.PUNCH_BEGIN_R AND T.TIMEZONE = A.TIMEZONE AND A.PUNCH_BEGIN_R >= T.STARTDATETIME AND A.PUNCH_BEGIN_R < T.ENDDATETIME AND NOT EXISTS ( SELECT 'X' FROM PS_TL_RPTD_TIME B WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.DUR = A.DUR AND B.PUNCH_DTTM = A.PUNCH_BEGIN_R)

Process XXXX ABENDED at Step TL_ST_LIB.LDPCHTIM.Step020 (SQL) -- RC = 805 (108,524)

Replication steps:

1. Add an elapsed punch to employee for a particular day

2. Create Rapid Time session where punch time is being added for that employee on that same day.

3. Submit the session. Error can be seen from Process Monitor.

 

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