HR_SRCH_SEC_VW

(SQL View)
 Index Back

Search Job Security

This view has been created to implement Row level security for job data search. It is on the same lines as the search record for Job Data component.

SELECT DISTINCT SEC.EMPLID ,SEC.EMPL_RCD ,OPR.ROWSECCLASS FROM PS_SJT_PERSON SEC , PSOPRDEFN OPR , PS_SJT_CLASS_ALL CLS , PS_SJT_OPR_CLS SOC WHERE SEC.APPT_TYPE IN ('0', '3') AND 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' UNION ALL SELECT DISTINCT SEC.EMPLID ,SEC.EMPL_RCD ,OPR.ROWSECCLASS FROM PS_SJT_PERSON SEC , PSOPRDEFN OPR , PS_SJT_CLASS_ALL CLS , PS_SJT_OPR_CLS SOC WHERE SEC.APPT_TYPE IN ('0', '3') AND 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 UNION ALL SELECT DISTINCT SEC.EMPLID ,SEC.EMPL_RCD ,OPR.ROWSECCLASS FROM PS_SJT_PERSON SEC , PSOPRDEFN OPR , PS_SJT_CLASS_ALL CLS , PS_SJT_OPR_CLS SOC WHERE SEC.APPT_TYPE IN ('0', '3') AND 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'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 ROWSECCLASS Character(30) VARCHAR2(30) NOT NULL The class used to determine row level security