SELECT DISTINCT OPR1.OPRID , OPR1.ROWSECCLASS , E.EMPLID , P.NAME , P.LAST_NAME_SRCH FROM PSCLASSDEFN O , PSOPRDEFN OPR1 , PS_TL_EMPL_DATA E , PS_PERSON_NAME P WHERE OPR1.ROWSECCLASS = O.CLASSID AND ((EXISTS ( SELECT 'X' FROM PS_TL_GRP_SECURITY S WHERE S.ROWSECCLASS = O.CLASSID) AND E.EMPLID IN ( SELECT F.EMPLID FROM PS_TL_GRP_SECURITY S , PS_TL_GROUP_DTL F WHERE S.TL_GROUP_ID = F.TL_GROUP_ID AND F.EMPLID = E.EMPLID AND F.EMPL_RCD = E.EMPL_RCD AND S.ROWSECCLASS = O.CLASSID)) OR (NOT EXISTS ( SELECT 'X' FROM PS_TL_GRP_SECURITY S WHERE S.ROWSECCLASS = O.CLASSID) AND E.EMPLID IN ( SELECT SEC.EMPLID FROM %Sql(SCRTY_PER_FROM2) WHERE %Sql(SCRTY_NO_APPT1) AND %Sql(SCRTY_WHERE2, 'PPLJOB') AND E.EMPLID = SEC.EMPLID AND E.EMPL_RCD = SEC.EMPL_RCD AND OPR.ROWSECCLASS = OPR1.ROWSECCLASS AND OPR.OPRID = OPR1.OPRID))) AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_TL_EMPL_DATA E1 WHERE E1.EMPLID = E.EMPLID AND E1.EMPL_RCD = E.EMPL_RCD AND E1.EFFDT <= %CurrentDateIn) AND E.EMPLID = P.EMPLID AND EXISTS ( SELECT 'X' FROM PS_JOB J WHERE J.EMPLID = E.EMPLID AND J.EMPL_RCD = E.EMPL_RCD)
|