ETL9.2:Time Admin Abends With Unique Constraint On PS_WRK_SHIFT_TAO4 (Doc ID 2173679.1)

Last updated on SEPTEMBER 26, 2016

Applies to:

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

Symptoms

On : 9.2 version, Time Administration

Time Admin abends with unique constraint on PS_WRK_SHIFT_TAO4

Time admin error:

Process 251384 resumed after step TL_SCHRES_AE.JA000.Step020 (108,538)

File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1723 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_WRK_SHIFT_TAO4) violated
Failed SQL stmt: INSERT INTO PS_WRK_SHIFT_TAO4 (PROCESS_INSTANCE, EMPLID, EMPL_RCD, DUR, START_DTTM, SHIFTNUM, DAYNUM, SCHEDULE_ID, SCH_ADHOC_IND, SETID, WRKDAY_ID, SHIFT_ID, SCHED_HRS, TIMEZONE, SCH_DLS_OPT, SCH_TZ_OFFSET, SCH_TYPE, END_DTTM, SCHED_SOURCE, SCHEDULE_GRP, STRT_STOP_DIFF, SEQ_NO, SCH_EFFDT, COMPARE_DTTM) SELECT A.PROCESS_INSTANCE, A.EMPLID, A.EMPL_RCD, X.THE_DATE, NULL , D.SHIFTNUM, D.DAYNUM, D.SCHEDULE_ID, D.SCH_ADHOC_IND, D.SETID, D.WRKDAY_ID, D.SHIFT_ID, CASE WHEN P.OFFDAY_IND='N' THEN P.SCHED_HRS ELSE P.STRT_STOP_DIFF END, CASE WHEN D.TIMEZONE = ' ' THEN PW.TIMEZONE ELSE D.TIMEZONE END, S.SCH_DLS_OPT, 0, P.SCH_TYPE, NULL, 'L', A.SCHEDULE_GRP, D.STRT_STOP_DIFF, 0, P.EFFDT, NULL FROM PS_TL_DATES_TBL X, PS_WRK_ASIGN_TAO4 A, PS_SCH_DEFN_TBL S, PS_SCH_DEFN_ROTATN R, PS_SCH_DEFN_DTL D, PS_SCH_SHIFT_TBL P, PS_TL_PROF_WRK4 PW WHERE A.PROCESS_INSTANCE = :1 AND PW.PROCESS_INSTANCE = A.PROCESS_INSTANCE AND X.THE_DATE BETWEEN A.EFFDT AND A.END_EFFDT AND X.THE_DATE BETWEEN TO_DATE(:2,'YYYY-MM-DD') AND TO_DATE(:3,'YYYY-MM-DD') AND S.SCH_TYPE = 'E' AND S.SETID = A.SETID AND S.SCH_ADHOC_IND = A.SCH_ADHOC_IND AND S.SCHEDULE_ID = A.SCHEDULE_ID AND R.SETID = S.SETID AND R.SCH_ADHOC_IND = S.SCH_ADHOC_IND AND R.SCHEDULE_ID = S.SCHEDULE_ID AND R.EFFDT = S.EFFDT AND R.ROTATION_ID = A.ROTATION_ID AND S.EFFDT <= X.THE_DATE AND S.END_EFFDT >= X.THE_DATE AND PW.EFFDT <= X.THE_DATE AND PW.END_EFFDT >= X.THE_DATE AND D.SETID = S.SETID AND D.SCH_ADHOC_IND = S.SCH_ADHOC_IND AND D.SCHEDULE_ID = S.SCHEDULE_ID AND D.EFFDT = S.EFFDT AND D.DAYNUM = (( X.THE_DATE) - (S.EFFDT)) + R.ROTATION_START_DAY - 1 - TRUNC(((( X.THE_DATE) - (S.EFFDT)) + R.ROTATION_START_DAY - 1) / S.SCHEDULE_DAYS, 0) * S.SCHEDULE_DAYS + 1 AND D.SHIFT_ID <>

ERROR
-----------------------
Process 251384 resumed after step TL_SCHRES_AE.JA000.Step020 (108,538) File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1723 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_WRK_SHIFT_TAO4) violated Failed SQL stm

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