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 ,a.JOB_INDICATOR 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 Record
    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 Set ID
    24 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
    25 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
    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
    27 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