|
SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.CAL_RUN_ID , B.CAL_RUN_ID , A.ORIG_CAL_RUN_ID , A.CAL_ID , B.CAL_ID , A.PRC_ORD_TS , B.PRC_ORD_TS , A.GP_PAYGROUP , A.PIN_NUM , A.RSLT_SEG_NUM , (A.EMPLID %Concat %Cast(A.EMPL_RCD,Number,Character)) , A.PIN_TYPE , A.CALC_RSLT_VAL , A.BASE_RSLT_VAL , A.UNIT_RSLT_VAL , A.RATE_RSLT_VAL , A.PCT_RSLT_VAL , A.CALC_RSLT_SUM , A.UNIT_RSLT_SUM , A.CALC_RSLT_VAL , A.GP_CALC_VAL1 , B.GP_CALC_VAL1 , (CASE WHEN (B.GP_CALC_VAL1) <> 0 THEN %Round(((A.GP_CALC_VAL1) - (B.GP_CALC_VAL1)),6) ELSE %Round((A.GP_CALC_VAL1),6) END) ,(CASE WHEN (B.GP_CALC_VAL1) <> 0 THEN %Round((((A.GP_CALC_VAL1) - (B.GP_CALC_VAL1))/(%ABS(B.GP_CALC_VAL1)))*100 ,2) ELSE %Round((A.GP_CALC_VAL1),2) END) ,A.UNIT_RSLT_VAL , (A.GP_CALC_VAL2) ,( B.GP_CALC_VAL2 ) ,(CASE WHEN (B.GP_CALC_VAL2) <> 0 THEN %Round(((A.GP_CALC_VAL2) - (B.GP_CALC_VAL2)),6) ELSE %Round((A.GP_CALC_VAL2),6) END) ,(CASE WHEN (B.GP_CALC_VAL2) <> 0 THEN %Round((((A.GP_CALC_VAL2) - (B.GP_CALC_VAL2))/(%ABS(B.GP_CALC_VAL2)))*100 ,2) ELSE %Round((A.GP_CALC_VAL2),2) END) , A.GP_SEG_COUNT , A.PRD_BGN_DT , A.PRD_END_DT , A.SEG_BGN_DT , A.SEG_END_DT , A.RUN_TYPE , A.BUSINESS_UNIT , A.PAY_ENTITY , A.COUNTRY , A.COMPANY , A.LOCATION , A.CONTRACT_TYPE , A.ESTABID , A.DEPTID , A.FTE , A.JOBCODE , A.SETID_JOBCODE , A.SETID_DEPT , A.SETID_LOCATION , A.GP_INCLUDE_RETRO , A.GP_MULT_INS_EXIST , A.FIRST_NAME , A.LAST_NAME , A.FULL_PART_TIME , A.SUPERVISOR_ID , A.EMPL_STATUS , A.GP_PA_HIRE_DT , A.TERMINATION_DT , A.CONTRACT_NUM , A.STRM_NUM , A.GROUP_LIST_ID , A.PYE_CALC_STAT , A.PRC_ORD_TS , A.GP_SEG_EXISTS , A.GP_RPT_RETRO ,(CASE WHEN B.CAL_RUN_ID IS NULL THEN 'Y' ELSE 'N' END) , A.CURRENCY_CD , A.PYMT_DT , A.SETID_EMPL_CLASS , A.EMPL_CLASS , A.GP_CALC_VAL1 , A.GP_CALC_VAL2 FROM PS_GP_PA_ERN_DED A , PS_GP_PA_ERN_DED B WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.GP_PAYGROUP = B.GP_PAYGROUP AND A.PIN_NUM = B.PIN_NUM AND A.RSLT_SEG_NUM = B.RSLT_SEG_NUM AND A.PIN_TYPE = B.PIN_TYPE AND A.RUN_TYPE = B.RUN_TYPE AND A.PYMT_DT > B.PYMT_DT AND B.PYMT_DT = ( SELECT MAX(C.PYMT_DT) FROM PS_GP_PA_ERN_DED C WHERE C.EMPLID = B.EMPLID AND C.EMPL_RCD = B.EMPL_RCD AND C.GP_PAYGROUP = B.GP_PAYGROUP AND C.RUN_TYPE = B.RUN_TYPE AND C.PYMT_DT < A.PYMT_DT) UNION SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.CAL_RUN_ID , ' ' , A.ORIG_CAL_RUN_ID , A.CAL_ID , ' ' , A.PRC_ORD_TS ,%DateTimeNull , A.GP_PAYGROUP , A.PIN_NUM , A.RSLT_SEG_NUM , (A.EMPLID %Concat %Cast(A.EMPL_RCD,Number,Character)) , A.PIN_TYPE , A.CALC_RSLT_VAL , A.BASE_RSLT_VAL , A.UNIT_RSLT_VAL , A.RATE_RSLT_VAL , A.PCT_RSLT_VAL , A.CALC_RSLT_SUM , A.UNIT_RSLT_SUM , A.CALC_RSLT_VAL , A.GP_CALC_VAL1 , 0 , %Round((A.GP_CALC_VAL1),6) , %Round((A.GP_CALC_VAL1),2) , A.UNIT_RSLT_VAL , A.GP_CALC_VAL2 , 0 , %Round((A.GP_CALC_VAL2),6) , %Round((A.GP_CALC_VAL2),2) , A.GP_SEG_COUNT , A.PRD_BGN_DT , A.PRD_END_DT , A.SEG_BGN_DT , A.SEG_END_DT , A.RUN_TYPE , A.BUSINESS_UNIT , A.PAY_ENTITY , A.COUNTRY , A.COMPANY , A.LOCATION , A.CONTRACT_TYPE , A.ESTABID , A.DEPTID , A.FTE , A.JOBCODE , A.SETID_JOBCODE , A.SETID_DEPT , A.SETID_LOCATION , A.GP_INCLUDE_RETRO , A.GP_MULT_INS_EXIST , A.FIRST_NAME , A.LAST_NAME , A.FULL_PART_TIME , A.SUPERVISOR_ID , A.EMPL_STATUS , A.GP_PA_HIRE_DT , A.TERMINATION_DT , A.CONTRACT_NUM , A.STRM_NUM , A.GROUP_LIST_ID , A.PYE_CALC_STAT , A.PRC_ORD_TS , A.GP_SEG_EXISTS , A.GP_RPT_RETRO , 'Y' , A.CURRENCY_CD , A.PYMT_DT , A.SETID_EMPL_CLASS , A.EMPL_CLASS , A.GP_CALC_VAL1 , A.GP_CALC_VAL2 FROM PS_GP_PA_ERN_DED A WHERE NOT EXISTS ( SELECT 'X' FROM PS_GP_PA_ERN_DED B WHERE A.PIN_NUM = B.PIN_NUM AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.GP_PAYGROUP = B.GP_PAYGROUP AND A.RUN_TYPE = B.RUN_TYPE AND B.PYMT_DT = ( SELECT MAX(C.PYMT_DT) FROM PS_GP_PA_ERN_DED C WHERE C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.GP_PAYGROUP = A.GP_PAYGROUP AND C.RUN_TYPE = A.RUN_TYPE AND C.PYMT_DT < A.PYMT_DT))
|
EMPLID