PC_ACTEAM_JC_VW

(SQL View)
 Index Back

View of act team member's jobc

List of activity team members jobcode. All employees in the team are listed. The employee has to be active in the JOB record. The jobcode as defined in the JOB record must be effective.

SELECT PT.BUSINESS_UNIT , PT.PROJECT_ID , PT.ACTIVITY_ID , PT.TEAM_MEMBER , CNTRL.SETID , J.JOBCODE , JCT.DESCR FROM PS_PROJ_ACT_TEAM PT , PS_JOB J , PS_JOBCODE_TBL JCT , PS_PROJ_ACTIVITY PA , PS_SET_CNTRL_REC CNTRL WHERE PT.TEAM_MEMBER_CLASS = 'L' AND PT.TEAM_MEMBER_IND = 'E' AND PA.BUSINESS_UNIT = PT.BUSINESS_UNIT AND PA.PROJECT_ID = PT.PROJECT_ID AND PA.ACTIVITY_ID = PT.ACTIVITY_ID AND J.EMPLID = PT.TEAM_MEMBER AND CNTRL.RECNAME = 'JOBCODE_TBL' AND CNTRL.SETCNTRLVALUE = J.BUSINESS_UNIT AND J.EMPL_RCD = ( SELECT MIN(J0.EMPL_RCD) FROM PS_JOB J0 WHERE J.EMPLID = J0.EMPLID AND J0.EFFDT= ( SELECT MAX(J4.EFFDT) FROM PS_JOB J4 WHERE J4.EMPLID = J0.EMPLID AND J4.EMPL_RCD = J0.EMPL_RCD AND J4.EFFDT <= PA.START_DT) AND J0.EFFSEQ = ( SELECT MAX(J5.EFFSEQ) FROM PS_JOB J5 WHERE J5.EMPLID = J0.EMPLID AND J5.EMPL_RCD = J0.EMPL_RCD AND J5.EFFDT = J0.EFFDT) AND J0.EMPL_STATUS = 'A') AND J.EFFDT = ( SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J.EMPLID = J2.EMPLID AND J.EMPL_RCD = J2.EMPL_RCD AND J2.EFFDT <= PA.START_DT ) AND J.EFFSEQ = ( SELECT MAX(J3.EFFSEQ) FROM PS_JOB J3 WHERE J.EMPLID = J3.EMPLID AND J.EMPL_RCD = J3.EMPL_RCD AND J.EFFDT = J3.EFFDT ) AND J.EMPL_STATUS = 'A' AND JCT.JOBCODE = J.JOBCODE AND JCT.EFFDT = ( SELECT MAX(JCT2.EFFDT) FROM PS_JOBCODE_TBL JCT2 WHERE JCT.SETID = JCT2.SETID AND JCT.JOBCODE = JCT2.JOBCODE AND JCT2.EFFDT <= PA.START_DT ) AND JCT.EFF_STATUS = 'A'

# 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 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
4 TEAM_MEMBER Character(30) VARCHAR2(30) NOT NULL Team Member
5 SETID Character(5) VARCHAR2(5) NOT NULL SetID

Prompt Table: SP_SETID_NONVW

6 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
7 DESCR Character(30) VARCHAR2(30) NOT NULL Description