ETL9.2: Time Administration Abends At Step TL_TA000501.AA000.Step150 (Doc ID 1677802.1)

Last updated on AUGUST 30, 2023

Applies to:

Symptoms

On : 9.2 version, Time Administration

When attempting to run Time Admin it abends with below error, the following error occurs.

ERROR
-----------------------
/
-- Row(s) affected: 0
-- 14.06.19 Process  ABENDED at Step TL_TA000501.AA000.Step150 (Action SQL) -- RC = 805
ROLLBACK
/
-- 14.06.19 SQL Error: ORA-00001: unique constraint (SMUADM.PS_TL_VALID_DATA4) violated

-- 14.06.19 ....(TL_TA000501.AA000.Step150) (SQL)
INSERT INTO PS_TL_VALID_DATA4 (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, EXCEPTION_SOURCE, PUNCH_TIME, PUNCH_DTTM, OPRID,
TCD_SUPERVISR_ID, OVERRIDE_RSN_CD, TL_CC_VALID, TL_CF_VALID, TEAM_MEMBER_IND)
SELECT 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, 'TA', TO_TIMESTAMP('1900-01-01-' ||
TO_CHAR(SYSTIMESTAMP,'HH24.MI.SS.FF'),'YYYY-MM-DD-HH24.MI.SS.FF'),
PUNCH_BEGIN, ' ', ' ', ' ', 'Y', 'Y', ' ' FROM PS_TL_IPT14 WHERE
PROCESS_INSTANCE =
/


Time Admin abends with an error

Time admin should process time successfully and not abend.

The issue can be reproduced at will with the following steps:
1. Run time admin to process time and it fails with error

Cause

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