EMPL_JOB_VW

(SQL View)
 Index Back

Employee Info For Dir Rpts

This view is used to retrieve employee job data for displaying job details on the direct reports pagelet.

SELECT DISTINCT A.EMPLID , A.EMPL_RCD , JC.JOBCODE , JC.DESCR , D.DESCRSHORT , A.POSITION_NBR FROM PS_JOB A , PS_JOBCODE_TBL JC , PS_DEPT_TBL D WHERE A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=%CurrentDateIn ) AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) AND JC.SETID = A.SETID_JOBCODE AND JC.JOBCODE = A.JOBCODE AND JC.EFFDT =( SELECT MAX(JC2.EFFDT) FROM PS_JOBCODE_TBL JC2 WHERE JC2.SETID=JC.SETID AND JC2.JOBCODE=JC.JOBCODE AND JC2.EFFDT<=%CurrentDateIn ) AND D.SETID = A.SETID_DEPT AND D.DEPTID = A.DEPTID AND D.EFFDT =( SELECT MAX(D2.EFFDT) FROM PS_DEPT_TBL D2 WHERE D2.SETID=D.SETID AND D2.DEPTID=D.DEPTID AND D2.EFFDT<=%CurrentDateIn )

  • Related Language Record: EMPL_INFO_VWLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    4 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    5 DEPTNAME Character(30) VARCHAR2(30) NOT NULL Department Name
    6 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number