PC_EMPLJOB_VW

(SQL View)
 Index Back

Employees and current job

View for all employees and their current job

SELECT A.EMPLID , A.JOBCODE , B.DESCR FROM PS_JOB A LEFT OUTER JOIN PS_JOBCODE_TBL B ON B.JOBCODE = A.JOBCODE WHERE (A.EFFDT IS NULL OR A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT) AND B.SETID = ( SELECT C.SETID FROM PS_SET_CNTRL_REC C WHERE C.RECNAME = 'JOBCODE_TBL' AND C.SETCNTRLVALUE = A.BUSINESS_UNIT) AND (B.EFFDT IS NULL OR B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_JOBCODE_TBL B_ED WHERE B.SETID = B_ED.SETID AND B.JOBCODE = B_ED.JOBCODE AND B_ED.EFFDT <= %CurrentDateIn)))

  • Related Language Record: PC_EMPLJOB_LVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    3 DESCR Character(30) VARCHAR2(30) NOT NULL Description