--Query 1 INSERT /*+ APPEND*/ INTO PS_OH_TL_PT_TMP SELECT %ProcessInstance , TL_PAYABLE_TIME.EMPLID , TL_PAYABLE_TIME.EMPL_RCD , TL_PAYABLE_TIME.DUR , TL_PAYABLE_TIME.SEQ_NBR , PERSONAL_DATA.NAME , JOB.BARG_UNIT , TL_PAYABLE_TIME.TRC , TL_TRC.DESCR AS OH_TRC_DESCR , ' ' , TL_PAYABLE_TIME.TL_QUANTITY , ' ' , 0 , TL_PAYABLE_TIME.PAYABLE_STATUS , TL_PAYABLE_TIME.PAYROLL_REQ_NUM , TL_PAYABLE_TIME.FROZEN_SW , TL_PAYABLE_TIME.FROZEN_DATE , TL_PAYABLE_TIME.PUBLISH_SW , TL_PAYABLE_TIME.ACTUAL_PUB_DATE , TL_PAYABLE_TIME.APPRV_PRCS_DTTM , TL_PAYABLE_TIME.OPRID , TL_PAYABLE_TIME.ORIG_SEQ_NBR , TL_PAYABLE_TIME.OVERRIDE_RATE , TL_PAYABLE_TIME.RECORD_ONLY_ADJ , TL_PAYABLE_TIME.LBR_DIST_AMT , TL_PAYABLE_TIME.EST_GROSS , TL_PAYABLE_TIME.DILUTED_GROSS , TL_PAYABLE_TIME.SOURCE_IND , TL_PAYABLE_TIME.TASKGROUP , TL_PAYABLE_TIME.BILLABLE_IND , TL_PAYABLE_TIME.ACCT_CD , TL_PAYABLE_TIME.ACCOUNT , TL_PAYABLE_TIME.DEPTID_CF AS OH_CF_DEPTID , TL_PAYABLE_TIME.PROJECT_ID , TL_PAYABLE_TIME.FUND_CODE , TL_PAYABLE_TIME.PROGRAM_CODE , TL_PAYABLE_TIME.CLASS_FLD , TL_PAYABLE_TIME.CHARTFIELD1 , TL_PAYABLE_TIME.CHARTFIELD2 , TL_PAYABLE_TIME.CHARTFIELD3 , TL_PAYABLE_TIME.BUDGET_REF , JOB.COMPANY , TL_PAYABLE_TIME.BUSINESS_UNIT , TL_PAYABLE_TIME.PRODUCT , TL_PAYABLE_TIME.CUSTOMER , TL_PAYABLE_TIME.BUSINESS_UNIT_PC , TL_PAYABLE_TIME.BUSINESS_UNIT_PF , TL_PAYABLE_TIME.SETID_ACTIVITY , TL_PAYABLE_TIME.ACTIVITY_ID , TL_PAYABLE_TIME.RESOURCE_TYPE , TL_PAYABLE_TIME.SETID_RESOURCE , TL_PAYABLE_TIME.RESOURCE_CATEGORY , TL_PAYABLE_TIME.RESOURCE_SUB_CAT , TL_PAYABLE_TIME.TASK , TL_PAYABLE_TIME.USER_FIELD_1 , TL_PAYABLE_TIME.USER_FIELD_2 , TL_PAYABLE_TIME.USER_FIELD_3 , TL_PAYABLE_TIME.USER_FIELD_4 , TL_PAYABLE_TIME.USER_FIELD_5 , PERS_NID.NATIONAL_ID , JOB.SETID_DEPT AS SETID , JOB.DEPTID , ' ' AS OH_DEPT_DESCR , JOB.POSITION_NBR , ' ' AS OH_POSITION_DESCR , JOB.JOBCODE , ' ' AS OH_JOBCODE_DESCR , JOB.LOCATION , ' ' AS OH_LOCATION_DESCR , JOB.UNION_CD , ' ' AS OH_UNION_DESCR FROM PS_TL_PAYABLE_TIME TL_PAYABLE_TIME , PS_JOB JOB , PS_PERSONAL_DATA PERSONAL_DATA , PS_PERS_NID PERS_NID , PS_TL_TRC_TBL TL_TRC , PS_LOCATION_TBL LOCATION , PS_JOBCODE_TBL JOBCODE WHERE TL_PAYABLE_TIME.EMPLID = PERSONAL_DATA.EMPLID AND TL_PAYABLE_TIME.EMPLID = PERS_NID.EMPLID AND PERS_NID.COUNTRY = 'USA' AND PERS_NID.NATIONAL_ID_TYPE = 'PR' AND TL_PAYABLE_TIME.EMPLID = JOB.EMPLID AND TL_PAYABLE_TIME.EMPL_RCD = JOB.EMPL_RCD AND LOCATION.SETID = JOB.SETID_LOCATION AND LOCATION.LOCATION = JOB.LOCATION AND JOBCODE.JOBCODE = JOB.JOBCODE AND JOBCODE.SETID = JOB.SETID_JOBCODE AND TL_TRC.TRC = TL_PAYABLE_TIME.TRC AND TL_TRC.EFFDT = ( SELECT MAX(TL_TRC_ED.EFFDT) FROM PS_TL_TRC_TBL TL_TRC_ED WHERE TL_TRC.TRC = TL_TRC_ED.TRC AND TL_TRC_ED.EFFDT <= TL_PAYABLE_TIME.DUR ) AND JOB.EFFDT = ( SELECT MAX(JOB_ED.EFFDT) FROM PS_JOB JOB_ED WHERE JOB.EMPLID = JOB_ED.EMPLID AND JOB.EMPL_RCD = JOB_ED.EMPL_RCD AND JOB_ED.EFFDT <= TL_PAYABLE_TIME.DUR ) AND JOB.EFFSEQ = ( SELECT MAX(JOB_ES.EFFSEQ) FROM PS_JOB JOB_ES WHERE JOB.EMPLID = JOB_ES.EMPLID AND JOB.EMPL_RCD = JOB_ES.EMPL_RCD AND JOB.EFFDT = JOB_ES.EFFDT ) AND LOCATION.EFFDT = ( SELECT MAX(LOCATION_ED.EFFDT) FROM PS_LOCATION_TBL LOCATION_ED WHERE LOCATION.SETID = LOCATION_ED.SETID AND LOCATION.LOCATION = LOCATION_ED.LOCATION AND LOCATION_ED.EFFDT <= JOB.EFFDT ) AND JOBCODE.EFFDT = ( SELECT MAX(JOBCODE_ED.EFFDT) FROM PS_JOBCODE_TBL JOBCODE_ED WHERE JOBCODE.SETID = JOBCODE_ED.SETID AND JOBCODE.JOBCODE = JOBCODE_ED.JOBCODE AND JOBCODE_ED.EFFDT <= JOB.EFFDT ) AND TL_PAYABLE_TIME.DUR >= TO_DATE('2008-01-01' , 'YYYY-MM-DD') -- Query 2 UPDATE %Table(OH_TL_PT_TMP) TMP SET (TMP.TL_ERNCD) = ( SELECT ERNCD.TL_ERNCD FROM PS_OH_S_TL_ERNCD ERNCD , PS_TL_PAYABLE_TIME TLPT WHERE ERNCD.TRC = TLPT.TRC AND ERNCD.PAY_SYSTEM = TLPT.PAY_SYSTEM AND TLPT.EMPLID = TMP.EMPLID AND TLPT.EMPL_RCD = TMP.EMPL_RCD AND TLPT.DUR = TMP.DUR AND TLPT.SEQ_NBR = TMP.SEQ_NBR AND TLPT.SETID_DEPT = TMP.SETID AND TLPT.DEPTID = TMP.DEPTID AND ERNCD.EFFDT = ( SELECT MAX(ERNCD_ED.EFFDT) FROM PS_OH_S_TL_ERNCD ERNCD_ED WHERE ERNCD.TRC = ERNCD_ED.TRC AND ERNCD.PAY_SYSTEM = ERNCD_ED.PAY_SYSTEM AND ERNCD_ED.EFFDT <= TLPT.DUR )) WHERE EXISTS ( SELECT 'X' FROM PS_OH_S_TL_ERNCD EE , PS_TL_PAYABLE_TIME TLPT1 WHERE EE.TRC = TLPT1.TRC AND EE.PAY_SYSTEM = TLPT1.PAY_SYSTEM AND TLPT1.EMPLID = TMP.EMPLID AND TLPT1.EMPL_RCD = TMP.EMPL_RCD AND TLPT1.DUR = TMP.DUR AND TLPT1.SEQ_NBR = TMP.SEQ_NBR AND TLPT1.SETID_DEPT = TMP.SETID AND TLPT1.DEPTID = TMP.DEPTID AND EE.EFFDT = ( SELECT MAX(EE1.EFFDT) FROM PS_OH_S_TL_ERNCD EE1 WHERE EE.TRC = EE1.TRC AND EE.PAY_SYSTEM = EE1.PAY_SYSTEM AND EE1.EFFDT <= TLPT1.DUR) )