JOB_CURR_ALL_VW

(SQL View)
 Index Back

Current Job for All Persons

JOB_CURR_ALL_VW is a view of some of the fields of the current Job record. It also changed EFFDT to ACTION_DT. Includes Future Only rows.

SELECT A.EMPLID ,A.EMPL_RCD ,A.EFFDT ,A.BUSINESS_UNIT ,A.EMPL_STATUS ,A.HR_STATUS ,A.DEPTID ,A.JOBCODE ,A.LOCATION ,A.POSITION_NBR ,A.ACTION ,A.ACTION_REASON ,A.COMP_FREQUENCY ,A.COMPRATE ,A.CURRENCY_CD ,A.SAL_ADMIN_PLAN ,A.GRADE ,A.COMPANY ,A.PAY_SYSTEM_FLG ,A.PAYGROUP ,A.REG_TEMP ,A.FULL_PART_TIME ,A.SETID_DEPT ,A.SETID_JOBCODE ,A.SETID_LOCATION ,A.PER_ORG FROM PS_JOB A WHERE A.EFFDT = ( SELECT MAX (C.EFFDT) FROM PS_JOB C WHERE C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND ((C.EFFDT <= %CurrentDateIn) OR (A.EFFDT > %CurrentDateIn AND %CurrentDateIn < ( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = A.EMPLID AND J2.EMPL_RCD = A.EMPL_RCD) ) )) AND A.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE D.EMPLID = A.EMPLID AND D.EMPL_RCD = A.EMPL_RCD AND D.EFFDT = A.EFFDT)

  • Parent record: PER_ORG_ASGN

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Default Value: NEW

    Prompt Table: PERSON

    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
    3 ACTION_DT Date(10) DATE Action Date
    4 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

    Prompt Table: BUS_UNIT_TBL_HR

    5 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
    6 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
    A=Active
    I=Inactive
    7 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: DEPT_TBL

    8 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: JOBCODE_TBL

    9 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    10 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

    Prompt Table: POSITION_DATA

    11 ACTION Character(3) VARCHAR2(3) NOT NULL Action

    Default Value: HIR

    Prompt Table: ACTION_TBL

    12 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code

    Prompt Table: ACTN_REASON_TBL

    13 COMP_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Compensation Frequency
    A=Annual
    B=Biweekly
    C=Contract
    D=Daily
    H=Hourly
    M=Monthly
    S=Semimonthly
    W=Weekly

    Prompt Table: COMP_FREQ_VW

    14 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate
    15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

    Default Value: OPR_DEF_TBL_HR.EXCHNG_TO_CURRENCY

    Prompt Table: CURRENCY_CD_TBL

    16 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
    17 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade
    18 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    19 PAY_SYSTEM_FLG Character(2) VARCHAR2(2) NOT NULL Payroll System
    GP=Global Payroll
    NA=Payroll for North America
    OT=Other
    PI=Payroll Interface
    20 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
    21 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
    R=Regular
    T=Temporary
    22 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
    D=On Demand
    F=Full-Time
    P=Part-Time
    23 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID
    24 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code SetID
    25 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location SetID
    26 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
    CWR=Contingent Worker
    EMP=Employee
    POI=Person of Interest