PGM_GETPROG_RES

(SQL View)
 Index Back

View for program resources

This view returns all the labor resources for projects under a program.

SELECT DISTINCT PT.BUSINESS_UNIT , B.PROJECT_ID , PT.TEAM_MEMBER , PD.NAME , PD.NAME_PREFIX , PD.CITY , PD.STATE , PD.COUNTRY FROM PS_PROJECT_TEAM PT , PS_PERSONAL_DATA PD ,PSTREENODE A , PS_PROJECT B , PS_BUS_UNIT_OPT_PC C WHERE PT.TEAM_MEMBER_CLASS = 'L' AND PT.TEAM_MEMBER_IND = 'E' AND PT.TEAM_MEMBER = PD.EMPLID AND PT.PROJECT_ID = %Substring(A.TREE_NODE, 1, 15) AND %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 AND F.SUMMARY_PRJ = 'N')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Default Value: PROJECT.BUSINESS_UNIT

Prompt Table: SP_BU_PC_NONVW

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

Prompt Table: PROJECT_VW

3 TEAM_MEMBER Character(30) VARCHAR2(30) NOT NULL Team Member

Prompt Table: PC_TEAM_EMP_VW

4 NAME Character(50) VARCHAR2(50) NOT NULL Name
5 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
1=Mr.
2=Mrs.
3=Ms.
6 CITY Character(30) VARCHAR2(30) NOT NULL City
7 STATE Character(6) VARCHAR2(6) NOT NULL State
8 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country