|
SELECT A.EMPLID , %TrimSubstr(D.COMPETENCY,1,8) %Concat 'x' %Concat %NumToChar(S.YEARS_OF_EXP) AS YRS FROM %Table(RS_EVALUATIONS) A , %Table(COMPETENCIES) C , %Table(COMPETENCY_TBL) D , %Table(REVW_RATING_TBL) E , %Table(RS_WRKR_ELGBL) F , %Table(RS_YRS_EXP_VW) S WHERE A.EVALUATION_TYPE = 'S' AND C.EFFDT = ( SELECT MAX(EFFDT) FROM PS_COMPETENCIES Z WHERE Z.EMPLID = C.EMPLID AND Z.EVALUATION_ID = C.EVALUATION_ID AND Z.COMPETENCY = C.COMPETENCY AND Z.EFFDT <= %CurrentDateIn) AND A.EMPLID = C.EMPLID AND A.EMPLID = F.EMPLID AND A.EVALUATION_ID = C.EVALUATION_ID AND C.COMPETENCY = D.COMPETENCY AND D.RATING_MODEL = E.RATING_MODEL AND C.PROFICIENCY = E.REVIEW_RATING AND C.YEARS_OF_EXP >= S.YEARS_OF_EXP AND F.SYSTEM_SOURCE='STF' UNION SELECT A.APPLID AS EMPLID , %TrimSubstr(D.COMPETENCY,1,8) %Concat 'x' %Concat %NumToChar(S.YEARS_OF_EXP) AS YRS FROM %Table(FO_APP_CM_EVALS) A , %Table(FO_APP_COMPS) C , %Table(COMPETENCY_TBL) D , %Table(REVW_RATING_TBL) E , %Table(APPLICANT) F , %Table(RS_YRS_EXP_VW) S WHERE F.APP_PER_STATUS = 'A' AND F.APP_STATUS_CODE ='A' AND A.EVALUATION_TYPE = 'S' AND A.APPLID = C.APPLID AND A.APPLID = F.APPLID AND A.EVALUATION_ID = C.EVALUATION_ID AND C.COMPETENCY = D.COMPETENCY AND D.RATING_MODEL = E.RATING_MODEL AND C.PROFICIENCY = E.REVIEW_RATING AND C.YEARS_OF_EXP >= S.YEARS_OF_EXP
|
EMPLID