PC_ACT_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 , PT.ACTIVITY_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_PROJ_ACT_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, PT.ACTIVITY_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
    2 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project/Grant
    3 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
    4 TEAM_MEMBER Character(11) VARCHAR2(11) NOT NULL Team Member
    5 TEAM_MEMBER_IND Character(1) VARCHAR2(1) NOT NULL Team Member Indicator
    E=Employee
    J=Job Code
    6 PROJ_ROLE Character(15) VARCHAR2(15) NOT NULL Project Role
    7 START_DT Date(10) DATE Start Date
    8 END_DT Date(10) DATE end date
    9 EMAIL_NOTIFICATION Character(1) VARCHAR2(1) NOT NULL Email Notify for Status Change
    N=No
    Y=Yes
    10 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address