PGM_GETTREE_PRJ

(SQL View)
 Index Back

Get child projects for a prog

This view will select all child projects for a program including other programs.

SELECT B.BUSINESS_UNIT , B.PROJECT_ID , %Substring(A.TREE_NODE, 1, 15) , A.TREE_NODE_NUM , A.TREE_LEVEL_NUM FROM PSTREENODE A , PS_PROJECT B , PS_BUS_UNIT_OPT_PC C WHERE %Substring(A.SETCNTRLVALUE, 1, 5) = B.BUSINESS_UNIT AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.TREE_NAME = C.PGM_EPT_NAME AND A.EFFDT = ( SELECT MAX(D.EFFDT) FROM PSTREEDEFN D WHERE D.SETCNTRLVALUE = A.SETCNTRLVALUE AND D.TREE_NAME = A.TREE_NAME AND D.EFFDT <= %CurrentDateIn ) AND EXISTS ( SELECT 'X' FROM PSTREENODE E , PS_PROJECT F WHERE E.SETCNTRLVALUE = A.SETCNTRLVALUE AND E.TREE_NAME = A.TREE_NAME AND E.EFFDT = A.EFFDT AND A.TREE_NODE_NUM BETWEEN E.TREE_NODE_NUM AND E.TREE_NODE_NUM_END AND E.TREE_NODE = B.PROJECT_ID AND A.SETCNTRLVALUE = F.BUSINESS_UNIT AND %Substring(A.TREE_NODE, 1, 15) = F.PROJECT_ID )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 PARENT_PROGRAM_ID Character(15) VARCHAR2(15) NOT NULL Parent Program ID
3 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
4 TREE_NODE_NUM Number(10,0) DECIMAL(10) NOT NULL Internal beginning node range number
5 TREE_LEVEL_NUM Number(3,0) SMALLINT NOT NULL Parent tree level if this is a part of the PSTREEBRANCH record