ETL9.1: TL_TIMEADMIN App Engine Job Is Failing After TL_TA000600 (Doc ID 1481419.1)

Last updated on AUGUST 18, 2017

Applies to:

PeopleSoft Enterprise HRMS Time and Labor - Version 9.1 and later
Information in this document applies to any platform.

Symptoms

On : 9.1 version, Time Administration

When running Time Administration to create payable time, the process is abending at step TL_TA000600.CALC_TA.CALCDIFF (SQL) with a unique constraint error.

ERROR
-----------------------
Process 957753 ABENDED at Step TL_TA000600.CALC_TA.CALCDIFF (SQL) -- RC = 805
File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1620 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_TL_MTCHD_15) violated
Failed SQL stmt:INSERT INTO PS_TL_MTCHD_15 (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, PUNCH_DTTM, SCHEDULED) SELECT A.PROCESS_INSTANCE, A.EMPLID, A.EMPL_RCD, A.DUR, A.SEQ_NBR, A.PUNCH_TYPE, ((((A.PUNCH_BEGIN) + NUMTODSINTERVAL( T.BASEOFFSET, 'MINUTE')) ) + NUMTODSINTERVAL( 0, 'MINUTE')), ((((A.PUNCH_BEGIN_R) + NUMTODSINTERVAL( T.BASEOFFSET, 'MINUTE')) ) + NUMTODSINTERVAL( 0, 'MINUTE')), A.TIME_IN_MIN1, ((((A.PUNCH_END) + NUMTODSINTERVAL( U.BASEOFFSET, 'MINUTE'))) + NUMTODSINTERVAL( 0, 'MINUTE')), ((((A.PUNCH_END_R) + NUMTODSINTERVAL( U.BASEOFFSET, 'MINUTE'))) + NUMTODSINTERVAL( 0, 'MINUTE')), A.TIME_IN_MIN2, A.TIMEZONE, A.TIMEZONE2, A.TCD_ID, A.BADGE_ID, A.EST_GROSS, A.LBR_DIST_AMT, A.DILUTED_GROSS

Report punch time using calendar period view.  Report punches as follows:
  8am in
  12pm lunch
  1pm in
  5pm out
  
  Now submit and verify data is saved in TL_RPTD_TIME table.
  
  Then place cursor in first in punch field, tab to lunch punch and enter zero, tab to next in punch and enter zero, tab to out punch then hit submit.
  
  An error is generated at this point say OK
  
  Now place cursor into first in punch, tab to lunch punch and hit delete, tab to next in punch and hit delete, tab to out punch then hit submit.  
  
  If you query the data in TL_RPTD_TIME table there is a null value in the meal punch row in the punch_time field instead of the row being deleted.


This is continually happening every payroll and causing major delays in the processing due to the time it takes to debug and correct data.

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