|
SELECT A.EMPLID , D.FIRST_NAME , D.LAST_NAME , A.EMPL_RCD ,E.HIRE_DT , E.TERMINATION_DT , E.COMPANY , E.BUSINESS_UNIT , E.DEPTNAME , E.ESTABID , E.LOCATION_DESCR , E.JOBCODE , E.JOBCODE_DESCR , E14X.XLATLONGNAME , E15X.XLATLONGNAME , E.GP_PAYGROUP , E.EMPL_CLASS , E.CONTRACT_TYPE , E.MONTHLY_RT , E.FTE , F.COUNTRY_CD , F.INTL_BANK_ACCT_NBR , F.BANK_CD , F.BRANCH_EC_CD , F.ACCOUNT_EC_ID , F.CHECK_DIGIT , F.ACCOUNT_NAME , F.CURRENCY_CD , F.ACCOUNT_NAME_AC , A.CAL_ID , A.CAL_RUN_ID , E.CONTRACT_NUM FROM ((PS_GP_PYE_PRC_STAT A ) LEFT OUTER JOIN PS_PYE_BANKACCT F ON A.EMPLID = F.EMPLID AND F.EFF_STATUS <> 'I' ) , PS_PERSON_NAME D , PS_GP_JB_DESCR_VW E LEFT OUTER JOIN ( SELECT * FROM PSXLATITEM TA WHERE TA.FIELDNAME='HR_STATUS' AND TA.EFF_STATUS = 'A' AND TA.EFFDT = ( SELECT MAX(EFFDT) FROM PSXLATITEM TB WHERE TB.FIELDNAME='HR_STATUS' AND TA.FIELDVALUE=TB.FIELDVALUE AND TB.EFF_STATUS = 'A' AND TB.EFFDT <= %CurrentDateIn )) E14X ON E14X.FIELDVALUE = E.HR_STATUS AND E14X.FIELDNAME='HR_STATUS' LEFT OUTER JOIN ( SELECT * FROM PSXLATITEM TA WHERE TA.FIELDNAME='EMPL_STATUS' AND TA.EFF_STATUS = 'A' AND TA.EFFDT = ( SELECT MAX(EFFDT) FROM PSXLATITEM TB WHERE TB.FIELDNAME='EMPL_STATUS' AND TA.FIELDVALUE=TB.FIELDVALUE AND TB.EFF_STATUS = 'A' AND TB.EFFDT <= %CurrentDateIn)) E15X ON E15X.FIELDVALUE = E.EMPL_STATUS AND E15X.FIELDNAME='EMPL_STATUS' , PS_GP_PYE_SEG_STAT H WHERE ( ( NOT EXISTS ( SELECT B.EMPLID FROM PS_GP_NET_DIST_DTL B , PS_GP_PYE_SEG_STAT C WHERE ( B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_GP_NET_DIST_DTL B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.EMPL_RCD = B_ED.EMPL_RCD AND B.RUN_TYPE = B_ED.RUN_TYPE AND B_ED.EFFDT <= C.SEG_END_DT) AND B.EMPLID = C.EMPLID AND B.EMPL_RCD = C.EMPL_RCD AND B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND C.CAL_RUN_ID = A.CAL_RUN_ID AND A.GP_PAYGROUP = C.GP_PAYGROUP AND A.CAL_ID = C.CAL_ID )) AND A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND A.EMPL_RCD = E.EMPL_RCD AND E.EFFDT = ( SELECT MAX(E_ED.EFFDT) FROM PS_GP_JB_DESCR_VW E_ED WHERE E.EMPLID = E_ED.EMPLID AND E.EMPL_RCD = E_ED.EMPL_RCD AND E_ED.EFFDT <= H.SEG_END_DT+1) AND E.EFFSEQ = ( SELECT MAX(E_ES.EFFSEQ) FROM PS_GP_JB_DESCR_VW E_ES WHERE E.EMPLID = E_ES.EMPLID AND E.EMPL_RCD = E_ES.EMPL_RCD AND E.EFFDT = E_ES.EFFDT) AND A.CALC_TYPE = 'P' AND A.GP_PAYGROUP = E.GP_PAYGROUP AND EXISTS ( SELECT G.GP_PAYGROUP FROM PS_GP_WC_USR_PYGRP G WHERE ( G.GP_PAYGROUP = A.GP_PAYGROUP )) AND A.CAL_RUN_ID = A.ORIG_CAL_RUN_ID AND A.PRD_TYPE = 'C' AND A.EMPLID = H.EMPLID AND A.CAL_RUN_ID = H.CAL_RUN_ID AND A.EMPL_RCD = H.EMPL_RCD AND A.GP_PAYGROUP = H.GP_PAYGROUP AND A.CAL_ID = H.CAL_ID AND A.ORIG_CAL_RUN_ID = H.ORIG_CAL_RUN_ID ))
|
EMPLID