JOB_CURR_VW1

(SQL View)
 Index Back

Current job data

Current effdt row for each empl_rcd

SELECT J.EMPLID , J.EMPL_RCD , J.EMPL_STATUS , J.HR_STATUS , J.JOB_INDICATOR , J.REG_REGION , J.BUSINESS_UNIT , J.COMPANY , J.SETID_DEPT , J.DEPTID , D.DESCR , J.SETID_JOBCODE , J.JOBCODE , JC.DESCR , J.POSITION_NBR , J.SETID_LOCATION , J.LOCATION , J.SUPERVISOR_ID , J.REPORTS_TO FROM PS_JOB J , PS_DEPT_TBL D , PS_JOBCODE_TBL JC WHERE J.EFFDT = ( SELECT MAX (J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND ((J1.EFFDT <= %CurrentDateIn) OR (J.EFFDT > %CurrentDateIn AND %CurrentDateIn < ( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD) ) )) AND J.EFFSEQ = ( SELECT MAX(J3.EFFSEQ) FROM PS_JOB J3 WHERE J3.EMPLID = J.EMPLID AND J3.EMPL_RCD = J.EMPL_RCD AND J3.EFFDT = J.EFFDT) AND D.SETID = J.SETID_DEPT AND D.DEPTID = J.DEPTID AND D.EFFDT = ( SELECT MAX (D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE D1.SETID = D.SETID AND D1.DEPTID = D.DEPTID AND D1.EFFDT <= %CurrentDateIn) AND JC.SETID = J.SETID_JOBCODE AND JC.JOBCODE = J.JOBCODE AND JC.EFFDT = ( SELECT MAX (JC1.EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.SETID = JC.SETID AND JC1.JOBCODE = JC.JOBCODE AND JC1.EFFDT <= %CurrentDateIn)

  • Related Language Record: JOB_CURR_LW1

    • # 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 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
    A=Active
    D=Deceased
    L=Leave of Absence
    P=Leave With Pay
    Q=Retired With Pay
    R=Retired
    S=Suspended
    T=Terminated
    U=Terminated With Pay
    V=Terminated Pension Pay Out
    W=Short Work Break
    X=Retired-Pension Administration
    4 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
    A=Active
    I=Inactive
    5 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
    6 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
    7 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    8 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    9 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    10 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    11 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    12 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
    13 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    14 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    15 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    16 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
    17 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    18 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
    19 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number