PC_EMP_JC_VW

(SQL View)
 Index Back

Jobcode for an Employee

View to get the right jobcode for an employee id. Uses current date as effectiive date. Uses the setid in the JOB table.

SELECT PD.EMPLID , J.JOBCODE , CNTRL.SETID , JCT.DESCR FROM PS_PERSONAL_DATA PD , PS_JOB J , PS_JOBCODE_TBL JCT , PS_SET_CNTRL_REC CNTRL WHERE J.EMPLID = PD.EMPLID AND CNTRL.RECNAME = 'JOBCODE_TBL' AND CNTRL.SETCNTRLVALUE = J.BUSINESS_UNIT AND J.EMPL_RCD = ( SELECT MIN(EMPL_RCD) FROM PS_JOB J1 WHERE J.EMPLID = J1.EMPLID AND J.EMPL_RCD = J1.EMPL_RCD AND J1.EMPL_STATUS = 'A' AND J1.EFFDT <= %CurrentDateIn ) 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 <= %CurrentDateIn ) 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.EFF_STATUS = 'A' AND JCT2.EFFDT <= %CurrentDateIn )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSONAL_DATA

2 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
3 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code SetID
4 DESCR Character(30) VARCHAR2(30) NOT NULL Description