PROJECT_TEAM_VW

(SQL View)
 Index Back

Project Team Member View

View of Project Team members that includes the listed employees as well as the jobcode-expanded employees.

SELECT PT.BUSINESS_UNIT , PT.PROJECT_ID , PD.EMPLID , MIN(PT.TEAM_MEMBER_IND) , MIN(PT.PROJ_ROLE) , MIN(PT.START_DT) , MIN(PT.END_DT) , MIN(PT.EMAIL_NOTIFICATION) , MIN(PT.EMAILID) FROM PS_PROJECT_TEAM PT , PS_PERSON PD WHERE ( ( PT.TEAM_MEMBER_IND= 'E' AND PD.EMPLID = PT.TEAM_MEMBER ) OR ( PT.TEAM_MEMBER_IND= 'J' AND PD.EMPLID IN ( SELECT EMPLID FROM PS_JOB PJ , PS_SET_CNTRL_REC SCR WHERE PJ.EMPLID = PD.EMPLID AND SCR.SETCNTRLVALUE = PT.BUSINESS_UNIT AND SCR.RECNAME = 'JOBCODE_TBL' AND PJ.SETID_JOBCODE = SCR.SETID AND PJ.JOBCODE = PT.TEAM_MEMBER AND PJ.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = PJ.EMPLID AND EMPL_RCD = PJ.EMPL_RCD AND EMPL_STATUS = 'A' AND EFFDT <= %CurrentDateIn ) AND PJ.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = PJ.EMPLID AND EMPL_RCD = PJ.EMPL_RCD AND EMPL_STATUS = 'A' AND EFFDT = PJ.EFFDT ) ) ) ) GROUP BY PT.BUSINESS_UNIT, PT.PROJECT_ID, PD.EMPLID

  • Parent record: PROJECT

    • # 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/Grant

    Prompt Table: PROJECT_VW

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

    Prompt Table: PROJECT_TEAM_VW

    4 TEAM_MEMBER_IND Character(1) VARCHAR2(1) NOT NULL Team Member Indicator
    E=Employee
    J=Job Code

    Default Value: E

    5 PROJ_ROLE Character(15) VARCHAR2(15) NOT NULL Project Role
    6 START_DT Date(10) DATE Start Date
    7 END_DT Date(10) DATE end date
    8 EMAIL_NOTIFICATION Character(1) VARCHAR2(1) NOT NULL Email Notify for Status Change
    N=No
    Y=Yes

    Default Value: Y

    9 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address