|
SELECT CAL.EMPLID , CAL.EMPL_RCD , CAL.CAL_RUN_ID , CAL.GP_PAYGROUP , CAL.CAL_ID , CAL.PRD_BGN_DT , CAL.PRD_END_DT , CGRP.RUN_FINALIZED_IND , CGRP.RUN_OPEN_IND , CAL.OFF_CYCLE , CAL.PROCESS_STRM_IND , CAL.STRM_NUM FROM PS_TL_CALPYESEL_VW CAL , PS_GP_CAL_RUN CGRP WHERE CGRP.CAL_RUN_ID = CAL.CAL_RUN_ID AND CGRP.RUN_FINALIZED_IND = 'N' AND (EXISTS ( SELECT 'X' FROM PS_GP_RTO_TRGR RTO WHERE RECNAME = 'GP_ABS_EVENT' AND RTO.TRGR_STATUS = 'U' AND RTO.EMPLID = CAL.EMPLID AND RTO.TRGR_EFFDT < CAL.PRD_BGN_DT AND (RTO.TRGR_EFFDT >=COALESCE(CAL.TL_FROM_DATE, CAL.GP_CALC_BGN_DT) AND RTO.TRGR_EFFDT >= CAL.GP_CALC_BGN_DT) AND RTO.COUNTRY = CAL.COUNTRY ) OR (CGRP.RUN_OPEN_IND = 'Y' AND EXISTS ( SELECT 'X' FROM PS_GP_ITER_TRGR ITR LEFT OUTER JOIN PS_GP_ABS_EVENT EVT ON ITR.EMPLID=EVT.EMPLID WHERE RECNAME = 'GP_ABS_EVENT' AND ITR.ITER_TRGR_STATUS = 'U' AND ITR.EMPLID = CAL.EMPLID AND ITR.CAL_RUN_ID = CAL.CAL_RUN_ID AND CAL.EMPLID=EVT.EMPLID AND CAL.EMPL_RCD=EVT.EMPL_RCD AND EVT.BGN_DT <= CAL.PRD_END_DT AND EVT.END_DT >= CAL.PRD_BGN_DT AND EVT.MANAGER_APPR_IND='Y')) OR EXISTS ( SELECT 'X' FROM PS_GP_ABS_EVENT EVT WHERE EVT.EMPLID = CAL.EMPLID AND EVT.EMPL_RCD = CAL.EMPL_RCD AND CGRP.RUN_FINALIZED_IND = 'N' AND EVT.MANAGER_APPR_IND = 'Y' AND EVT.BGN_DT <= CAL.PRD_END_DT AND EVT.END_DT >= CAL.PRD_BGN_DT AND NOT EXISTS ( SELECT 'X' FROM PS_GP_PYE_PRC_STAT ST WHERE ST.CAL_RUN_ID = CGRP.CAL_RUN_ID AND ST.EMPLID = EVT.EMPLID AND ST.EMPL_RCD = EVT.EMPL_RCD)))
|