PC_JOBCD_EMPVW1

(SQL View)
Index Back

Employees by Jobcode

View of distinct Employees by Jobcode.

SELECT DISTINCT J.SETID_JOBCODE , J.JOBCODE , J.EMPLID , JCT.DESCR , PD.NAME FROM PS_JOB J , PS_JOBCODE_TBL JCT , PS_PERSONAL_DATA PD WHERE JCT.SETID = J.SETID_JOBCODE AND JCT.JOBCODE = J.JOBCODE AND JCT.EFF_STATUS = 'A' AND JCT.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL JCT2 WHERE JCT.SETID = JCT2.SETID AND JCT.JOBCODE = JCT2.JOBCODE AND JCT2.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.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 J.EFFDT = J3.EFFDT ) AND J.EMPLID = PD.EMPLID AND J.EMPL_STATUS = 'A'

  • Related Language Record: PC_JOBCD_EMPLG1
  • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

    Default Value: OPR_DEF_TBL_FS.SETID

    Prompt Table: SP_SETID_NONVW

    2 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    5 NAME Character(50) VARCHAR2(50) NOT NULL Name