|
SELECT DISTINCT OPR.OPRID , SEC.EMPLID , P.JPM_PROFILE_ID , NM.LANGUAGE_CD , NM.NAME , NM.NAME_DISPLAY_SRCH , NM.LAST_NAME_SRCH , NM.NAME_AC FROM PS_SJT_PERSON SEC , PS_JPM_JP_SEC_VW S , PS_NAMES_LNG NM , PSOPRDEFN OPR , PS_JPM_PROFILE P WHERE SEC.EMPLID = P.EMPLID AND SEC.EMPLID = NM.EMPLID AND SEC.HR_STATUS = 'A' AND P.JPM_JP_PRFL_STATUS = 'A' AND S.JPM_JP_TYPE = P.JPM_JP_TYPE AND P.JPM_JP_TYPE = ( SELECT JPM_PRI_PERS_TYPE FROM PS_JPM_JP_PRI_TYPE) AND S.JPM_ROLE = 'EMP' AND SEC.APPT_TYPE <> '1' AND NM.EMPLID = SEC.EMPLID AND NM.NAME_TYPE = 'PRI' AND NM.EFFDT = ( SELECT MAX(NM1.EFFDT) FROM PS_NAMES_LNG NM1 WHERE NM1.EMPLID = NM.EMPLID AND NM1.NAME_TYPE = 'PRI' AND NM1.LANGUAGE_CD = NM.LANGUAGE_CD AND NM1.EFFDT <= %CurrentDateIn) AND (EXISTS ( SELECT 'X' FROM PS_SJT_CLASS_ALL CLS , PS_SJT_OPR_CLS SOC WHERE CLS.SCRTY_SET_CD = 'PPLJOB' AND CLS.SCRTY_TYPE_CD = SEC.SCRTY_TYPE_CD AND CLS.SCRTY_KEY1 = SEC.SCRTY_KEY1 AND CLS.SCRTY_KEY2 = SEC.SCRTY_KEY2 AND CLS.SCRTY_KEY3 = SEC.SCRTY_KEY3 AND CLS.TREE = 'Y' AND SOC.OPRID=OPR.OPRID AND SOC.CLASSID = CLS.CLASSID AND SOC.CLASSID = OPR.ROWSECCLASS AND SOC.SEC_RSC_FLG = '1' ) OR EXISTS ( SELECT 'X' FROM PS_SJT_CLASS_ALL CLS , PS_SJT_OPR_CLS SOC WHERE CLS.SCRTY_SET_CD = 'PPLJOB' AND CLS.SCRTY_TYPE_CD = SEC.SCRTY_TYPE_CD AND CLS.SCRTY_KEY1 = SEC.SCRTY_KEY1 AND CLS.SCRTY_KEY2 = SEC.SCRTY_KEY2 AND CLS.SCRTY_KEY3 = SEC.SCRTY_KEY3 AND CLS.TREE = 'N' AND SOC.OPRID=OPR.OPRID AND SOC.CLASSID = CLS.CLASSID ) OR EXISTS ( SELECT 'X' FROM PS_SJT_CLASS_ALL CLS , PS_SJT_OPR_CLS SOC WHERE CLS.SCRTY_SET_CD = 'PPLJOB' AND CLS.SCRTY_TYPE_CD = SEC.SCRTY_TYPE_CD AND CLS.SCRTY_KEY1 = SEC.SCRTY_KEY1 AND CLS.SCRTY_KEY2 = SEC.SCRTY_KEY2 AND CLS.SCRTY_KEY3 = SEC.SCRTY_KEY3 AND CLS.TREE = 'Y' AND SOC.OPRID=OPR.OPRID AND SOC.CLASSID = CLS.CLASSID AND SOC.CLASSID = OPR.ROWSECCLASS AND SOC.SEC_RSC_FLG = '3' ))
|
OPRID