|
SELECT J.EMPLID , J.EMPL_RCD , J.EFFDT , J.ESTABID , CN.MEMBERSHIP_NBR_FRA , E.POPULATION_CTG_FRA , P.DCL_POP_CTG_FRA , P.BASE_CALC_TYPE_FRA , P.ER_RATE_FRA , P.EE_RATE_FRA , CN.MEMBERSHIP_IND_FRA , P.FORMULA_PIN_FRA , I.DUCS_CD_FRA , I.DADS_CD_FRA , E.COVERED_POP_CD_FRA , E.CONTCT_KIND_CD_FRA FROM PS_JOB J , PS_CONTRACT_DATA C , PS_EMPL_CNTRCT_FRA E , PS_FUND_INST_FRA I , PS_CNTRCT_FUND_FRA CN , PS_POPC_CNTRCT_FRA P , PS_GP_PYE_STAT_WRK S WHERE S.EMPLID = J.EMPLID AND S.EMPL_RCD = J.EMPL_RCD AND J.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID=J.EMPLID AND EMPL_RCD=J.EMPL_RCD AND EFFDT<=S.SEG_END_DT) AND J.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID=J.EMPLID AND EMPL_RCD=J.EMPL_RCD AND EFFDT=J.EFFDT) AND J.HR_STATUS='A' AND J.EMPLID = C.EMPLID AND J.CONTRACT_NUM = C.CONTRACT_NUM AND E.CONTRACT_NUM = C.CONTRACT_NUM AND E.EFFDT = ( SELECT MAX(EFFDT) FROM PS_EMPL_CNTRCT_FRA WHERE EMPLID=E.EMPLID AND CONTRACT_NUM=E.CONTRACT_NUM AND EFFDT <=S.SEG_END_DT) AND J.EMPLID = E.EMPLID AND CN.MEMBERSHIP_NBR_FRA = E.MEMBERSHIP_NBR_FRA AND CN.MEMBERSHIP_NBR_FRA = P.MEMBERSHIP_NBR_FRA AND CN.EFFDT = P.EFFDT AND CN.EFFDT= ( SELECT MAX(EFFDT) FROM PS_CNTRCT_FUND_FRA WHERE MEMBERSHIP_NBR_FRA=CN.MEMBERSHIP_NBR_FRA AND EFFDT <=S.SEG_END_DT) AND CN.EFF_STATUS = 'A' AND CN.EFFDT <= E.EFFDT AND P.EFFDT = CN.EFFDT AND CN.FUND_INST_FRA = I.FUND_INST_FRA AND I.FND_INST_TYPE_FRA = 'P' AND E.POPULATION_CTG_FRA = P.POPULATION_CTG_FRA
|
EMPLID