GPS_SCRTYPM2_VW

(SQL View)
 Index Back

GPS Security by department tre

ensures security by department tree is implemented

SELECT DISTINCT A.SETID , A.tree_node , OPR.OPRID FROM PSTREENODE A , PSOPRDEFN OPR , PS_SCRTY_TBL_DEPT SEC WHERE A.TREE_NAME = 'DEPT_SECURITY' AND sec.SETID = A.SETID AND A.TREE_NODE_NUM BETWEEN sec.TREE_NODE_NUM AND sec.TREE_NODE_NUM_END AND SEC.ACCESS_CD = 'Y' AND a.tree_node_num NOT IN ( SELECT sub.tree_node_num FROM PSTREENODE sub , PS_SCRTY_TBL_DEPT sub_sec WHERE sub.TREE_NAME = 'DEPT_SECURITY' AND sub.SETID = A.SETID AND sub.setid = sub_sec.setid AND sub.TREE_NODE_NUM BETWEEN sub_sec.TREE_NODE_NUM AND sub_sec.TREE_NODE_NUM_END AND sub_SEC.ACCESS_CD = 'N' AND sub_sec.rowsecclass = SEC.rowsecclass AND sub.EFFDT = ( SELECT MAX(C.EFFDT) FROM PSTREENODE C WHERE C.SETID = sub.SETID AND C.TREE_NAME = sub.TREE_NAME AND C.EFFDT <= %CurrentDateIn) ) AND A.EFFDT = ( SELECT MAX(C.EFFDT) FROM PSTREENODE C WHERE C.SETID = A.SETID AND C.TREE_NAME = A.TREE_NAME AND C.EFFDT <= %CurrentDateIn) AND (( SEC.ROWSECCLASS = OPR.ROWSECCLASS ) OR EXISTS ( SELECT 'X' FROM PS_SCRTY_TBL_DEPT STD , PS_SJT_OPR_CLS SOC WHERE STD.ROWSECCLASS = SOC.CLASSID AND SOC.OPRID=OPR.OPRID AND STD.rowsecclass = SEC.rowsecclass AND STD.SETID = A.SETID AND SOC.SEC_RSC_FLG = '2' ))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 TREE_NODE Character(20) VARCHAR2(20) NOT NULL Node name
3 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).