TD_SECR_PROD_VW(SQL View) |
Index Back |
---|---|
Product Node Security ViewOPRID keyed view: Bring back valid NODE_IDs based on OPRID |
SELECT DISTINCT A.SETID , A.TD_TREE_ID , A.EFFDT , C.OPRID , A.PRODUCT_ID FROM PS_TD_TREE_NODE A , PS_TD_TREE_USERS B , PSOPRDEFN C , PS_TD_TREE_NODE D , PS_TD_TREE_HDR H WHERE A.SETID = B.SETID AND A.TD_TREE_ID = B.TD_TREE_ID AND A.EFFDT = B.EFFDT AND A.SETID = D.SETID AND A.TD_TREE_ID = D.TD_TREE_ID AND A.EFFDT = D.EFFDT AND ((B.TD_SECURITY_TYPE = 'U' AND B.TD_SECURITY_ID = C.OPRID) OR (B.TD_SECURITY_TYPE = 'R' AND (EXISTS ( SELECT 'X' FROM PSROLEUSER R WHERE R.ROLENAME = B.TD_SECURITY_ID AND R.ROLEUSER = C.OPRID)))) AND B.TD_NODE_ID = D.TD_NODE_ID AND A.TREE_NODE_NUM >= D.TREE_NODE_NUM AND A.TREE_NODE_NUM <= D.TREE_NODE_NUM_END AND H.SETID=A.SETID AND H.TD_TREE_ID=A.TD_TREE_ID AND H.EFFDT=A.EFFDT AND H.TD_TREE_TYPE = 'P' |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | SETID | Character(5) | VARCHAR2(5) NOT NULL |
SetID
Default Value: OPR_DEF_TBL_FS.SETID Prompt Table: SP_SETID_NONVW |
2 | TD_TREE_ID | Character(8) | VARCHAR2(8) NOT NULL | Tree ID |
3 | EFFDT | Date(10) | DATE | Effective Date |
4 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
5 | PRODUCT_ID | Character(18) | VARCHAR2(18) NOT NULL | Product ID |