|
SELECT DISTINCT OPR1.OPRID , O.CLASSID , E.EMPLID , P.NAME , P.LAST_NAME_SRCH , J.DEPTID , X.OWNERSHIP_ID FROM PSCLASSDEFN O , PSOPRDEFN OPR1 , PS_TL_EMPL_DATA E , PS_PERSON_NAME P , PS_JOB J , PS_FO_EMPL_XREF X WHERE((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.GROUP_ID = F.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_FROM) WHERE %Sql(SCRTY_NO_APPT1) AND %Sql(SCRTY_WHERE, '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 J.EMPLID = E.EMPLID AND J.EMPL_RCD = E.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT <= E.EFFDT) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT = J.EFFDT) AND X.EMPLID = E.EMPLID AND X.EMPL_RCD = E.EMPL_RCD
|
OPRID