HRTM_MGR_JOB_VW

(SQL View)
 Index Back

Direct Reports Job Data

View of current job information to be displayed in the bread crumbs of the My Team page.

SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.SETID_JOBCODE , 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: HRTM_MGR_JOBLVW

    • # 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 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    4 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: JOBCODE_TBL

    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