ETL9.2 - TL_TIMEADMIN throwing DB error ORA-08103: Object No Longer Exists (Doc ID 2434779.1)

Last updated on FEBRUARY 23, 2023

Applies to:

Symptoms

On : 9.2 version, Time Administration

TL_TIMEADMIN is failing with a SQL error ORA-08103: object no longer exists 

EXAMPLE ERROR
-----------------------
File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1723 Error Position: 12 Return: 8103 - ORA-08103: object no longer exists
Failed SQL stmt: INSERT INTO PS_TL_PROF_WRK4 (PROCESS_INSTANCE, EMPLID, EMPL_RCD, EFFDT, END_EFFDT, JOBCODE, POSITION_NBR, HOLIDAY_SCHEDULE, SHIFT, SETID_JOBCODE, SETID_LOCATION, SETID_SALARY, SETID_DEPT, STD_HOURS, STD_HRS_FREQUENCY, ANNUAL_RT, MONTHLY_RT, DAILY_RT, SAL_ADMIN_PLAN, EMPL_CLASS, OFFICER_CD, JOB_INDICATOR, GRADE, STEP, NAME, BIRTHDATE, BADGE_NBR, HIRE_DT, REHIRE_DT, CMPNY_SENIORITY_DT, SUPERVISOR_ID, SERVICE_DT, TERMINATION_DT, WORKGROUP, GP_PAYGROUP, PAYGROUP, EMPL_STATUS, REG_TEMP, FULL_PART_TIME, EMPL_TYPE, PER_TYPE, COMPANY, LOCATION, COMPRATE, UNION_CD, DEPTID, BUSINESS_UNIT, CURRENCY_CD, TIME_RPTG_STATUS, TASKGROUP, TASK_PROFILE_ID, TL_TIME_TO_PAY, RULE_ELEMENT_1, RULE_ELEMENT_2, RULE_ELEMENT_3, RULE_ELEMENT_4, RULE_ELEMENT_5, HOURLY_RT, TIMEZONE, PAY_SYSTEM_FLG, COUNTRY, TL_EXCLUDE_HOL, TL_CR_PARTIAL_HRS, NEEDS_APPR_FLAG, FLSA_STATUS) SELECT J.PROCESS_INSTANCE, J.EMPLID, J.EMPL_RCD, DT.EFFDT, DT.END_EFFDT, J.JOBCODE, J.POSITION_NBR, case when (J.HOLIDAY_SCHEDULE = ' ' AND PG.HOLIDAY_SCHEDULE = ' ') THEN W.HOLIDAY_SCHEDULE when (J.HOLIDAY_SCHEDULE = ' ' AND PG.HOLIDAY_SCHEDULE <> ' ') THEN PG.HOLIDAY_SCHEDULE ELSE J.HOLIDAY_SCHEDULE END , J.SHIFT, J.SETID_JOBCODE, J.SETID_LOCATION, J.SETID_SALARY, J.SETID_DEPT, J.STD_HOURS, J.STD_HRS_FREQUENCY, 0, 0, 0, J.SAL_ADMIN_PLAN, J.EMPL_CLASS, J.OFFICER_CD, J.JOB_INDICATOR, J.GRADE, J.STEP, N.NAME_DISPLAY, P.BIRTHDATE, ' ', E.HIRE_DT, E.REHIRE_DT, E.CMPNY_SENIORITY_DT, E.SUPERVISOR_ID, E.SERVICE_DT, E.TERMINATION_DT, W.WORKGROUP, J.GP_PAYGROUP, J.PAYGROUP, J.EMPL_STATUS, J.REG_TEMP, J.FULL_PART_TIME, J.EMPL_TYPE, ' ', J.COMPANY, J.LOCATION, J.COMPRATE, J.UNION_CD, J.DEPTID, J.BUSINESS_UNIT, J.CURRENCY_CD, ED.TIME_RPTG_STATUS, ED.TASKGROUP, ED.TASK_PROFILE_ID, ED.TL_TIME_TO_PAY, ED.RULE_ELEMENT_1, ED.RULE_ELEMENT_2, ED.RULE_ELEMENT_3, ED.RULE_ELEMENT_4, ED.RULE_ELEMENT_5, J.HOURLY_RT, ED.TIMEZONE, J.PAY_SYSTEM_FLG, PG.COUNTRY, W.TL_EXCLUDE_HOL, W.TL_CR_PARTIAL_HRS, W.NEEDS_APPR_FLAG, J.FLSA_STATUS FROM PS_TL_TRDT_WRK4 DT, PS_PERSON P , PS_NAMES N, PS_EMPLOYMENT E, PS_TL_EMPDTA_WRK4 ED , PS_TL_JOB_WRK4 J, PS_TL_PAYGRP_WRK4 PG, PS_TL_WRKGRP_WRK4 W WHERE DT.PROCESS_INSTANCE = :1 AND ED.PROCESS_INSTANCE = DT.PROCESS_INSTANCE AND J.PROCESS_INSTANCE = DT.PROCESS_INSTANCE AND PG.PROCESS_INSTANCE = DT.PROCESS_INSTANCE AND W.PROCESS_INSTANCE = DT.PROCESS_INSTANCE AND J.PROCESS_INSTANCE = PG.PROCESS_INSTANCE AND J.COMPANY = PG.COMPANY AND DT.EMPLID = P.EMPLID AND DT.EMPLID=N.EMPLID AND DT.EMPLID = E.EMPLID AND DT.EMPLID = ED.EMPLID AND DT.EMPLID = J.EMPLID AND DT.EMPLID = PG.EMPLID AND DT.EMPLID = W.EMPLID AND DT.EMPL_RCD = ED.EMPL_RCD AND DT.EMPL_RCD = E.EMPL_RCD AND DT.EMPL_RCD = J.EMPL_RCD AND DT.EMPL_RCD = PG.EMPL_RCD AND DT.EMPL_RCD = W.EMPL_RCD AND N.NAME_TYPE='PRI' AND N.EFFDT = ( SELECT MAX(EFFDT) FROM PS_NAMES N2 WHERE N2.EMPLID=N.EMPLID AND N2.NAME_TYPE='PRI' AND EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) AND ED.EFFDT = ( SELECT MAX(T1.EFFDT) FROM PS_TL_EMPDTA_WRK4 T1 WHERE T1.PROCESS_INSTANCE = :2 AND T1.PROCESS_INSTANCE = DT.PROCESS_INSTANCE AND T1.EMPLID = DT.EMPLID AND T1.EMPL_RCD = DT.EMPL_RCD AND T1.EFFDT <= DT.EFFDT) AND W.WORKGROUP = ED.WORKGROUP AND W.EFFDT = ( SELECT MAX(W1.EFFDT) FROM PS_TL_WRKGRP_WRK4 W1 WHERE W1.PROCESS_INSTANCE = :3 AND W1.PROCESS_INSTANCE = DT.PROCESS_INSTANCE AND W1.EMPLID = DT.EMPLID AND W1.EMPL_RCD = DT.EMPL_RCD AND W1.EFFDT <= DT.EFFDT AND W1.WORKGROUP = W.WORKGROUP ) AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_TL_JOB_WRK4 J1 WHERE J1.PROCESS_INSTANCE = :4 AND J1.PROCESS_INSTANCE = DT.PROCESS_INSTANCE AND J1.EMPLID = DT.EMPLID AND J1.EMPL_RCD = DT.EMPL_RCD AND J1.EFFDT <= DT.EFFDT) AND ((PG.PAYGROUP = J.PAYGROUP AND PG.GP_PAYGROUP = ' ' AND PG.PAY_SYSTEM_FLG = 'NA' AND (J.PAY_SYSTEM_FLG = 'NA' OR J.PAY_SYSTEM_FLG = 'PI') AND J.ABSENCE_SYSTEM_CD = 'OT' ) OR (PG.GP_PAYGROUP = J.GP_PAYGROUP AND PG.PAYGROUP = ' ' AND PG.PAY_SYSTEM_FLG = 'GP' AND (J.PAY_SYSTEM_FLG = 'GP' OR ((J.PAY_SYSTEM_FLG = 'NA' OR J.PAY_SYSTEM_FLG = 'PI') AND J.ABSENCE_SYSTEM_CD = 'AM'))) OR (J.PAY_SYSTEM_FLG = 'OT' AND PG.PAY_SYSTEM_FLG = 'OT') ) AND PG.EFFDT = ( SELECT MAX(PG1.EFFDT) FROM PS_TL_PAYGRP_WRK4 PG1 WHERE PG1.PROCESS_INSTANCE = :5 AND PG1.PROCESS_INSTANCE = DT.PROCESS_INSTANCE AND PG1.COMPANY = PG.COMPANY AND PG1.EMPLID = DT.EMPLID AND PG1.EMPL_RCD = DT.EMPL_RCD AND PG1.EFFDT <= DT.EFFDT AND ((PG1.PAYGROUP = J.PAYGROUP AND PG1.COMPANY = J.COMPANY AND PG1.GP_PAYGROUP = ' ' AND PG1.PAY_SYSTEM_FLG = 'NA' AND (J.PAY_SYSTEM_FLG = 'NA' OR J.PAY_SYSTEM_FLG = 'PI') AND J.ABSENCE_SYSTEM_CD = 'OT' ) OR (PG1.GP_PAYGROUP = J.GP_PAYGROUP AND PG1.PAYGROUP = ' ' AND PG1.PAY_SYSTEM_FLG = 'GP' AND (J.PAY_SYSTEM_FLG = 'GP' OR ((J.PAY_SYSTEM_FLG = 'NA' OR J.PAY_SYSTEM_FLG = 'PI') AND J.ABSENCE_SYSTEM_CD = 'AM'))) OR (J.PAY_SYSTEM_FLG = 'OT' AND PG1.PAY_SYSTEM_FLG = 'OT')))

Process xxxx ABENDED at Step TL_TRPROFILE.GA000.Step100 (SQL) -- RC = 8103 (108,524)

Cause

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