TD_APPR_NODEVW2 (SQL View)	Index Back
Customer Node Security View Customer Node Security View: OPRID keyed view: Bring back valid NODE_IDs based on OPRID

TD_APPR_NODEVW2

(SQL View)

Customer Node Security View

Customer Node Security View: OPRID keyed view: Bring back valid NODE_IDs based on OPRID

SELECT DISTINCT A.SETID , A.TD_TREE_ID , A.EFFDT , C.OPRID , A.TD_NODE_ID , A.TD_NODE_PARENT_ID , A.TD_SEQNO , A.DESCR , A.TD_TREE_BRANCH , A.TD_TREE_BRANCH_PAR , A.CUST_ID , A.TD_NODE_LEVEL FROM PS_TD_TREE_NODE A , PS_TD_TREE_APPROVE 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 = 'C'

# 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 TD_NODE_ID Character(8) VARCHAR2(8) NOT NULL Node ID

6 TD_NODE_PARENT_ID Character(8) VARCHAR2(8) NOT NULL Node ID

7 TD_SEQNO Number(15,4) DECIMAL(14,4) NOT NULL Sequence Number

8 DESCR Character(30) VARCHAR2(30) NOT NULL Description

9 TD_TREE_BRANCH Character(20) VARCHAR2(20) NOT NULL Tree Branch Node ID

10 TD_TREE_BRANCH_PAR Character(20) VARCHAR2(20) NOT NULL Parent Branch

11 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.

12 TD_NODE_LEVEL Number(3,0) SMALLINT NOT NULL Node Level

#	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	TD_NODE_ID	Character(8)	VARCHAR2(8) NOT NULL	Node ID
6	TD_NODE_PARENT_ID	Character(8)	VARCHAR2(8) NOT NULL	Node ID
7	TD_SEQNO	Number(15,4)	DECIMAL(14,4) NOT NULL	Sequence Number
8	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
9	TD_TREE_BRANCH	Character(20)	VARCHAR2(20) NOT NULL	Tree Branch Node ID
10	TD_TREE_BRANCH_PAR	Character(20)	VARCHAR2(20) NOT NULL	Parent Branch
11	CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
12	TD_NODE_LEVEL	Number(3,0)	SMALLINT NOT NULL	Node Level