HR_JOBCD_METR

(SQL View)
 Index Back

Job Code Incumbents View

This view selects currently active job codes. If the job code has incumbents (currently active jobs), then a row is returned for each incumbent. Rows are also returned for current/active job codes that do not have any incumbents.

SELECT A.SETID , A.JOBCODE , A.EFFDT , A.DESCR , A.DESCRSHORT , A.KEY_JOBCODE , B.EMPLID , B.EMPL_RCD , B.BUSINESS_UNIT , B.COMPANY , B.DEPTID , B.LOCATION , B.SETID_DEPT , B.SETID_LOCATION FROM PS_JOBCODE_TBL A , PS_JOB B WHERE A.EFF_STATUS = 'A' AND %EffdtCheck(JOBCODE_TBL J, A, %CurrentDateIn) AND A.SETID = B.SETID_JOBCODE AND A.JOBCODE = B.JOBCODE AND B.HR_STATUS = 'A' AND B.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_JOB C WHERE C.EMPLID = B.EMPLID AND C.EMPL_RCD = B.EMPL_RCD AND C.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE D.EMPLID = B.EMPLID AND D.EMPL_RCD = B.EMPL_RCD AND D.EFFDT = B.EFFDT) UNION SELECT A2.SETID , A2.JOBCODE , A2.EFFDT , A2.DESCR , A2.DESCRSHORT , A2.KEY_JOBCODE , ' ' , 0 , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' FROM PS_JOBCODE_TBL A2 , PS_JOB B2 WHERE A2.EFF_STATUS = 'A' AND %EffdtCheck(JOBCODE_TBL J2, A2, %CurrentDateIn) AND NOT EXISTS ( SELECT 'X' FROM PS_JOB B2 WHERE A2.SETID = B2.SETID_JOBCODE AND A2.JOBCODE = B2.JOBCODE AND B2.HR_STATUS = 'A' AND B2.EFFDT = ( SELECT MAX(C2.EFFDT) FROM PS_JOB C2 WHERE C2.EMPLID = B2.EMPLID AND C2.EMPL_RCD = B2.EMPL_RCD AND C2.EFFDT <= %CurrentDateIn) AND B2.EFFSEQ = ( SELECT MAX(D2.EFFSEQ) FROM PS_JOB D2 WHERE D2.EMPLID = B2.EMPLID AND D2.EMPL_RCD = B2.EMPL_RCD AND D2.EFFDT = B2.EFFDT))

  • Related Language Record: HR_JOBCODE_LANG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    2 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    3 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    5 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    6 KEY_JOBCODE Character(1) VARCHAR2(1) NOT NULL Key Job Code
    N=No
    Y=Yes
    7 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    8 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    9 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    10 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    11 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    12 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    13 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    14 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID