PGM_PROG_SRCH

(SQL View)
Index Back

View of programs on EPT

This view returns all programs on the Enterprise Program Tree.

SELECT A.BUSINESS_UNIT , %Substring(B.TREE_NODE, 1, 15) , C.DESCR , A.PGM_EPT_NAME , B.EFFDT , B.TREE_NODE_NUM , B.TREE_NODE_NUM_END FROM PS_BUS_UNIT_OPT_PC A , PSTREENODE B , PS_PROJECT C WHERE A.BUSINESS_UNIT = B.SETCNTRLVALUE AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.TREE_NAME = A.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 = C.PROJECT_ID AND C.SUMMARY_PRJ = 'Y'

  • Related Language Record: PROJECT_LANG
  • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Default Value: OPR_DEF_TBL_PC.BUSINESS_UNIT

    Prompt Table: SP_BU_PC_NONVW

    2 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

    Prompt Table: SP_PROJS_NONVW

    3 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    4 PGM_EPT_NAME Character(18) VARCHAR2(18) NOT NULL The name of the Enterprise Program Tree.
    5 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    6 TREE_NODE_NUM Number(10,0) DECIMAL(10) NOT NULL Internal beginning node range number
    7 TREE_NODE_NUM_END Number(10,0) DECIMAL(10) NOT NULL Internal ending node range number