HR_EMPL_JOB_VW

(SQL View)
 Index Back

Direct Reports Job Data

View of current job information to be displayed in the direct reports pagelet in the manager dashboard.

SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.EFFDT , JC.JOBCODE , JC.DESCR , JC.DESCR , A.JOB_INDICATOR FROM PS_JOB A , PS_JOBCODE_TBL JC 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 )

  • Related Language Record: HR_EMPL_JOB_LVW

    • # 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 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    5 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    6 JOBCODE_DESCR2 Character(30) VARCHAR2(30) NOT NULL Job Code Description
    7 JOB_INDICATOR Character(1) VARCHAR2(1) NOT NULL This Indicator is used on JOB to specify which employment record is considered to be the primary one for an employee. In case of Multiple Jobs per Employee, this property is being used to make a conscious decision in related features where only 1 Employment Record per Employee needs to be returned.
    N=Not Applicable
    P=Primary Job
    S=Secondary Job