HRCD_PRI_JOB_VW

(SQL View)
 Index Back

Primary Job View - HR CD

Returns the most current active Primary Job information. If there is no active primary job then it returns the lowest empl_rcd from amoungst the secondary jobs.

SELECT JOB.EMPLID , JOB.EFFDT , JOB.EMPL_RCD , JOB.EFFSEQ , JOB.DEPTID , JOB.JOBCODE , JOB.POSITION_NBR , JOB.EMPL_STATUS , JOB.LOCATION , JOB.BUSINESS_UNIT , JOB.JOB_INDICATOR , JOB.COMPANY , JOB.PAYGROUP , JOB.TAX_LOCATION_CD , JOB.REG_TEMP , JOB.FULL_PART_TIME , JOB.HR_STATUS , JOB.SETID_DEPT , JOB.SETID_JOBCODE , JOB.SETID_LOCATION , JOB.POSITION_OVERRIDE , JOB.SUPERVISOR_ID , JOB.REPORTS_TO FROM PS_JOB JOB WHERE JOB.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = JOB.EMPLID AND EMPL_RCD = JOB.EMPL_RCD AND EFFDT <= %CurrentDateIn) AND JOB.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = JOB.EMPLID AND EMPL_RCD = JOB.EMPL_RCD AND EFFDT = JOB.EFFDT) AND JOB.EMPL_RCD = ( SELECT MIN(JOB2.EMPL_RCD) FROM PS_JOB JOB2 WHERE EMPLID = JOB.EMPLID AND EFFDT = JOB.EFFDT AND EFFSEQ = JOB.EFFSEQ) AND ((JOB.EMPL_STATUS IN('A','L','P','S','W') AND JOB.JOB_INDICATOR = 'P') OR (NOT EXISTS ( SELECT 'X' FROM PS_JOB JOB3 WHERE JOB3.EMPLID = JOB.EMPLID AND JOB3.EMPL_RCD <> JOB.EMPL_RCD AND JOB3.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = JOB3.EMPLID AND EMPL_RCD = JOB3.EMPL_RCD AND EFFDT <= %CurrentDateIn) AND JOB3.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = JOB3.EMPLID AND EMPL_RCD = JOB3.EMPL_RCD AND EFFDT = JOB3.EFFDT) AND JOB3.EMPL_STATUS IN('A','L','P','S','W') AND JOB3.JOB_INDICATOR = 'P') ) AND (JOB.EMPL_STATUS IN('A','L','P','S','W') AND JOB.JOB_INDICATOR <> 'P' AND JOB.EMPL_RCD = ( SELECT MIN(JOB4.EMPL_RCD) FROM PS_JOB JOB4 WHERE JOB4.EMPLID = JOB.EMPLID AND JOB4.EMPL_STATUS IN('A','L','P','S','W') AND JOB4.JOB_INDICATOR <> 'P')) )

  • Parent record: JOB_DATE_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 EFFDT Date(10) DATE Effective Date
    3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    5 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    6 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    7 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    8 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
    9 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    10 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    11 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
    12 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    13 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
    14 TAX_LOCATION_CD Character(10) VARCHAR2(10) NOT NULL Tax Location Code
    15 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
    R=Regular
    T=Temporary
    16 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
    D=On Demand
    F=Full-Time
    P=Part-Time
    17 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
    A=Active
    I=Inactive
    18 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    19 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
    20 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
    21 POSITION_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Override Position Data
    22 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
    23 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number