PC_TM_EMP_JC_VW

(SQL View)
 Index Back

View of team member's jobcode

List of project 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.TEAM_MEMBER , PTS.SCHED_NUM , CNTRL.SETID , J.JOBCODE , JCT.DESCR FROM PS_PROJECT_TEAM PT , PS_PROJ_TEAM_SCHED PTS , PS_JOB J , PS_JOBCODE_TBL JCT , PS_SET_CNTRL_REC CNTRL WHERE PT.TEAM_MEMBER_CLASS = 'L' AND PT.TEAM_MEMBER_IND = 'E' AND PT.BUSINESS_UNIT = PTS.BUSINESS_UNIT AND PT.PROJECT_ID = PTS.PROJECT_ID AND PT.TEAM_MEMBER_CLASS = PTS.TEAM_MEMBER_CLASS AND PT.TEAM_MEMBER_IND = PTS.TEAM_MEMBER_IND AND PT.TEAM_MEMBER = PTS.TEAM_MEMBER 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 <= PTS.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(EFFDT) FROM PS_JOB J2 WHERE J.EMPLID = J2.EMPLID AND J.EMPL_RCD = J2.EMPL_RCD AND J2.EMPL_STATUS = 'A' AND J2.EFFDT <= PTS.START_DT ) AND J.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB J3 WHERE J.EMPLID = J3.EMPLID AND J.EMPL_RCD = J3.EMPL_RCD AND J3.EMPL_STATUS = 'A' AND J.EFFDT = J3.EFFDT ) AND JCT.JOBCODE = J.JOBCODE AND JCT.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL JCT2 WHERE JCT.SETID = JCT2.SETID AND JCT.JOBCODE = JCT2.JOBCODE AND JCT2.EFFDT <= PTS.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 TEAM_MEMBER Character(30) VARCHAR2(30) NOT NULL Team Member
4 SCHED_NUM Number(5,0) INTEGER NOT NULL Team sequence number
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