PGM_CST_EPT_VW2

(SQL View)
 Index Back

View of Projects by Program

This view selects all projects on the Enterprise Program Tree that is a child of a program.

SELECT A.BUSINESS_UNIT , A.PROJECT_ID , %Substring(B.TREE_NODE, 1, 15) , C.EFF_STATUS , C.SUMMARY_PRJ , B.TREE_LEVEL_NUM , A.TREE_LEVEL_NUM , A.TREE_NAME FROM PS_PGM_CST_EPT_VW1 A , PSTREENODE B , PS_PROJECT C WHERE A.BUSINESS_UNIT = B.SETCNTRLVALUE AND A.TREE_NAME = B.TREE_NAME AND A.EFFDT = B.EFFDT AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.TREE_NODE = C.PROJECT_ID AND B.TREE_NODE_NUM BETWEEN A.TREE_NODE_NUM AND A.TREE_NODE_NUM_END

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
3 TREE_NODE Character(20) VARCHAR2(20) NOT NULL Node name
4 PC_PROCESS_STATUS Character(1) VARCHAR2(1) NOT NULL The processing state of a project.
A=Active
I=Inactive
P=Pending
T=Template
5 SUMMARY_PRJ Character(1) VARCHAR2(1) NOT NULL An indicator to determine whether project is summary or detail.
N=Detail Project
Y=Program
6 TREE_LEVEL_NUM Number(3,0) SMALLINT NOT NULL Parent tree level if this is a part of the PSTREEBRANCH record
7 TREE_LEVEL_NUM2 Number(3,0) SMALLINT NOT NULL Parent tree level if this is a part of the PSTREEBRANCH record
8 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name