PGM_XREF_VW1

(SQL View)
 Index Back

View of Projects with Request

This is an interim view that is used to determine provide a cross-reference between Project and Project Request.

SELECT A.BUSINESS_UNIT , B.TREE_NAME , B.EFFDT , A.PROJECT_ID , A.PROJ_REQUEST_ID , A.PPK_PROJ_VERSION , B.TREE_NODE_NUM , B.TREE_NODE_NUM_END FROM PS_PROJECT A , PSTREENODE B , PS_BUS_UNIT_OPT_PC C WHERE A.BUSINESS_UNIT = B.SETCNTRLVALUE AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.TREE_NAME = C.PGM_EPT_NAME AND B.EFFDT = ( SELECT MAX(D.EFFDT) FROM PSTREENODE D WHERE D.SETCNTRLVALUE = B.SETCNTRLVALUE AND D.TREE_NAME = B.TREE_NAME AND D.EFFDT <= %CurrentDateIn ) AND B.TREE_NODE = A.PROJECT_ID AND A.PROJ_REQUEST_ID <> ' '

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
5 PROJ_REQUEST_ID Character(15) VARCHAR2(15) NOT NULL A unique identifier that represents a Project Request.
6 PPK_PROJ_VERSION Character(15) VARCHAR2(15) NOT NULL An identifier that represents a version of the Project Request.
7 TREE_NODE_NUM Number(10,0) DECIMAL(10) NOT NULL Internal beginning node range number
8 TREE_NODE_NUM_END Number(10,0) DECIMAL(10) NOT NULL Internal ending node range number