VC_TREE_GRP_VW

(SQL View)
 Index Back

Group Tree View

Variable Comp: This view returns child groups for a given parent group and tree. This view is useful for inheritance.

SELECT A.TREE_NAME , A.EFFDT , %Substring(A.PARENT_NODE_NAME,1,15) , %Substring(A2.TREE_NODE,1,15) FROM PSTREENODE A , PSTREENODE A2 , PSTREEDEFN A3 WHERE A3.SETID = ' ' AND A3.SETCNTRLVALUE = ' ' AND A3.EFF_STATUS = 'A' AND A3.VALID_TREE = 'Y' AND A3.TREE_STRCT_ID = 'VC_GROUP_STRUCT' AND A.TREE_NAME = A3.TREE_NAME AND A.EFFDT = A3.EFFDT AND A.SETID = A3.SETID AND A.SETCNTRLVALUE = A3.SETCNTRLVALUE AND A.TREE_BRANCH = ' ' AND A.TREE_NAME = A2.TREE_NAME AND A.EFFDT = A2.EFFDT AND A.SETID = A2.SETID AND A.SETCNTRLVALUE = A2.SETCNTRLVALUE AND A.TREE_BRANCH = A2.TREE_BRANCH AND ( A.TREE_NODE_NUM = A2.TREE_NODE_NUM OR EXISTS ( SELECT 'X' FROM PSTREENODE B WHERE A.TREE_NAME = B.TREE_NAME AND A.EFFDT = B.EFFDT AND A.SETID = B.SETID AND A.SETCNTRLVALUE = B.SETCNTRLVALUE AND A.TREE_BRANCH = B.TREE_BRANCH AND A.TREE_NODE_NUM = B.PARENT_NODE_NUM AND ( A2.TREE_NODE_NUM = B.TREE_NODE_NUM OR EXISTS ( SELECT 'X' FROM PSTREENODE C WHERE A.TREE_NAME = C.TREE_NAME AND A.EFFDT = C.EFFDT AND A.SETID = C.SETID AND A.SETCNTRLVALUE = C.SETCNTRLVALUE AND A.TREE_BRANCH = C.TREE_BRANCH AND B.TREE_NODE_NUM = C.PARENT_NODE_NUM AND ( A2.TREE_NODE_NUM = C.TREE_NODE_NUM OR EXISTS ( SELECT 'X' FROM PSTREENODE D WHERE A.TREE_NAME = D.TREE_NAME AND A.EFFDT = D.EFFDT AND A.SETID = D.SETID AND A.SETCNTRLVALUE = D.SETCNTRLVALUE AND A.TREE_BRANCH = D.TREE_BRANCH AND C.TREE_NODE_NUM = D.PARENT_NODE_NUM AND ( A2.TREE_NODE_NUM = D.TREE_NODE_NUM OR EXISTS ( SELECT 'X' FROM PSTREENODE E WHERE A.TREE_NAME = E.TREE_NAME AND A.EFFDT = E.EFFDT AND A.SETID = E.SETID AND A.SETCNTRLVALUE = E.SETCNTRLVALUE AND A.TREE_BRANCH = E.TREE_BRANCH AND D.TREE_NODE_NUM = E.PARENT_NODE_NUM AND ( A2.TREE_NODE_NUM = E.TREE_NODE_NUM OR EXISTS ( SELECT 'X' FROM PSTREENODE F WHERE A.TREE_NAME = F.TREE_NAME AND A.EFFDT = F.EFFDT AND A.SETID = F.SETID AND A.SETCNTRLVALUE = F.SETCNTRLVALUE AND A.TREE_BRANCH = F.TREE_BRANCH AND E.TREE_NODE_NUM = F.PARENT_NODE_NUM AND ( A2.TREE_NODE_NUM = F.TREE_NODE_NUM OR EXISTS ( SELECT 'X' FROM PSTREENODE G WHERE A.TREE_NAME = G.TREE_NAME AND A.EFFDT = G.EFFDT AND A.SETID = G.SETID AND A.SETCNTRLVALUE = G.SETCNTRLVALUE AND A.TREE_BRANCH = G.TREE_BRANCH AND F.TREE_NODE_NUM = G.PARENT_NODE_NUM AND ( A2.TREE_NODE_NUM = G.TREE_NODE_NUM OR EXISTS ( SELECT 'X' FROM PSTREENODE H WHERE A.TREE_NAME = H.TREE_NAME AND A.EFFDT = H.EFFDT AND A.SETID = H.SETID AND A.SETCNTRLVALUE = H.SETCNTRLVALUE AND A.TREE_BRANCH = H.TREE_BRANCH AND G.TREE_NODE_NUM = H.PARENT_NODE_NUM AND ( A2.TREE_NODE_NUM = H.TREE_NODE_NUM OR EXISTS ( SELECT 'X' FROM PSTREENODE I WHERE A.TREE_NAME = I.TREE_NAME AND A.EFFDT = I.EFFDT AND A.SETID = I.SETID AND A.SETCNTRLVALUE = I.SETCNTRLVALUE AND A.TREE_BRANCH = I.TREE_BRANCH AND H.TREE_NODE_NUM = I.PARENT_NODE_NUM AND ( A2.TREE_NODE_NUM = I.TREE_NODE_NUM OR EXISTS ( SELECT 'X' FROM PSTREENODE J WHERE A.TREE_NAME = J.TREE_NAME AND A.EFFDT = J.EFFDT AND A.SETID = J.SETID AND A.SETCNTRLVALUE = J.SETCNTRLVALUE AND A.TREE_BRANCH = J.TREE_BRANCH AND I.TREE_NODE_NUM = J.PARENT_NODE_NUM AND ( A2.TREE_NODE_NUM = J.TREE_NODE_NUM OR EXISTS ( SELECT 'X' FROM PSTREENODE K WHERE A.TREE_NAME = K.TREE_NAME AND A.EFFDT = K.EFFDT AND A.SETID = K.SETID AND A.SETCNTRLVALUE = K.SETCNTRLVALUE AND A.TREE_BRANCH = K.TREE_BRANCH AND J.TREE_NODE_NUM = K.PARENT_NODE_NUM AND ( A2.TREE_NODE_NUM = K.TREE_NODE_NUM )))))))))))))))))))))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name
2 TREE_EFFDT Date(10) DATE NOT NULL Effective date of the Tree
3 GB_GROUP_ID Character(15) VARCHAR2(15) NOT NULL Group Build ID.
4 GB_CHILD_GROUP_ID Character(15) VARCHAR2(15) NOT NULL Child Group ID