|
SELECT A.EMPLID , %TrimSubstr(D.COMPETENCY,1,8) %Concat 'x' %Concat %TrimSubstr(E.REVIEW_RATING,1,1) AS INTEREST FROM %Table(RS_EVALUATIONS) A , %Table(COMPETENCIES) C , %Table(COMPETENCY_TBL) D , %Table(REVW_RATING_TBL) E , %Table(RS_WRKR_ELGBL) F 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 E.RATING_MODEL ='INT' AND C.INTEREST_LEVEL >= E.REVIEW_RATING AND E.REVIEW_RATING > '0' AND F.SYSTEM_SOURCE='STF' UNION SELECT A.APPLID AS EMPLID , %TrimSubstr(C.COMPETENCY,1,8) %Concat 'x' %Concat %TrimSubstr(E.REVIEW_RATING,1,1) AS INTEREST FROM %Table(FO_APP_CM_EVALS) A , %Table(FO_APP_COMPS) C , %Table(COMPETENCY_TBL) D , %Table(REVW_RATING_TBL) E , %Table(APPLICANT) F 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 E.RATING_MODEL ='INT' AND C.INTEREST_LEVEL >= E.REVIEW_RATING AND E.REVIEW_RATING > '0'
|
EMPLID