PRI_JOB_VW

(SQL View)
 Index Back

Primary Job View - HR

PRIMARY_JOB_VW is used to return the most current Primary Job information at a certain Effective Date for an Employee. The goal of this view is to find the most current active primary job row of an employee. If this employee does not have an active primary row the resultset can contain inactive rows. If more than just one job instance is defined as primary the resultset may consist of more than one row per EE. (1) This view returns the 'active' job with the lowest employment record number where the Job_Indicator is equal to "P"; (2) If no row found under (1), it will return the active job with the lowest empl_rcd#; (3) If no row found under (2), it will return the lowest empl_rcd# where the Job_Indicator is equal to "P"; (4) If no row found under (3), it will return the lowest empl_rcd#.

SELECT J.EMPLID , J.EFFDT , J.EMPL_RCD , J.EFFSEQ , J.DEPTID , J.JOBCODE , J.POSITION_NBR , J.EMPL_STATUS , J.LOCATION , J.BUSINESS_UNIT , J.JOB_INDICATOR , J.COMPANY , J.PAYGROUP , J.TAX_LOCATION_CD , J.REG_TEMP , J.FULL_PART_TIME , J.HR_STATUS , J.SETID_DEPT , J.SETID_JOBCODE , J.SETID_LOCATION , J.POSITION_OVERRIDE , J.SUPERVISOR_ID , J.REPORTS_TO , J.HIRE_DT , J.LAST_HIRE_DT FROM PS_JOB J WHERE J.EMPL_RCD = COALESCE( ( SELECT MIN(A.EMPL_RCD) FROM PS_JOB A WHERE A.EMPLID = J.EMPLID AND A.EFFDT = ( SELECT MAX (A1.EFFDT) FROM PS_JOB A1 WHERE A1.EMPLID = A.EMPLID AND A1.EMPL_RCD = A.EMPL_RCD AND ((A1.EFFDT <= %CurrentDateIn) OR (A.EFFDT > %CurrentDateIn AND %CurrentDateIn < ( SELECT MIN(A2.EFFDT) FROM PS_JOB A2 WHERE A2.EMPLID = A.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD)))) AND A.EFFSEQ = ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A3.EMPLID = A.EMPLID AND A3.EMPL_RCD = A.EMPL_RCD AND A3.EFFDT = A.EFFDT) AND A.HR_STATUS = 'A' AND A.JOB_INDICATOR = 'P') , ( SELECT MIN(B.EMPL_RCD) FROM PS_JOB B WHERE B.EMPLID = J.EMPLID AND B.EFFDT = ( SELECT MAX (B1.EFFDT) FROM PS_JOB B1 WHERE B1.EMPLID = B.EMPLID AND B1.EMPL_RCD = B.EMPL_RCD AND ((B1.EFFDT <= %CurrentDateIn) OR (B.EFFDT > %CurrentDateIn AND %CurrentDateIn < ( SELECT MIN(B2.EFFDT) FROM PS_JOB B2 WHERE B2.EMPLID = B.EMPLID AND B2.EMPL_RCD = B.EMPL_RCD)))) AND B.EFFSEQ = ( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B3.EMPLID = B.EMPLID AND B3.EMPL_RCD = B.EMPL_RCD AND B3.EFFDT = B.EFFDT) AND B.HR_STATUS = 'A') , ( SELECT MIN(C.EMPL_RCD) FROM PS_JOB C WHERE C.EMPLID = J.EMPLID AND C.EFFDT = ( SELECT MAX (C1.EFFDT) FROM PS_JOB C1 WHERE C1.EMPLID = C.EMPLID AND C1.EMPL_RCD = C.EMPL_RCD AND ((C1.EFFDT <= %CurrentDateIn) OR (C.EFFDT > %CurrentDateIn AND %CurrentDateIn < ( SELECT MIN(C2.EFFDT) FROM PS_JOB C2 WHERE C2.EMPLID = C.EMPLID AND C2.EMPL_RCD = C.EMPL_RCD)))) AND C.EFFSEQ = ( SELECT MAX(C3.EFFSEQ) FROM PS_JOB C3 WHERE C3.EMPLID = C.EMPLID AND C3.EMPL_RCD = C.EMPL_RCD AND C3.EFFDT = C.EFFDT) AND C.JOB_INDICATOR = 'P') , ( SELECT MIN(D.EMPL_RCD) FROM PS_JOB D WHERE D.EMPLID = J.EMPLID ) ) AND J.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT = J.EFFDT)

  • 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
    24 HIRE_DT Date(10) DATE First Start Date
    25 REHIRE_DT Date(10) DATE Rehire Date