PGM_ORPHPRJ_LVW

(SQL View)
 Index Back

Lang view Projects not on EPT

This language view selects all active or pending projects not on the Enterprise Program Tree. These projects are also called orphans.

SELECT A.BUSINESS_UNIT , A.PROJECT_ID , L.LANGUAGE_CD , A.DESCR FROM PS_PROJECT A , PS_PGM_EPT_TREE_VW B , PS_PROJECT_LANG L WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.BUSINESS_UNIT = L.BUSINESS_UNIT AND A.PROJECT_ID = L.PROJECT_ID AND A.EFF_STATUS IN ('A', 'P') AND NOT EXISTS ( SELECT 'X' FROM PSTREENODE C WHERE C.SETCNTRLVALUE = B.BUSINESS_UNIT AND C.TREE_NAME = B.TREE_NAME AND C.EFFDT = B.EFFDT AND C.TREE_NODE = A.PROJECT_ID)

# 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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
4 DESCR Character(30) VARCHAR2(30) NOT NULL Description