CURRENT_JOB_JPN

(SQL View)
 Index Back

Current Job for EMPs -

CURRENT_JOB is a view that combines an employee's personal/demographic data and current job assignment data from the JOB record. This record is distinct from CURRENT_JOB in that it does not include the keys EFFDT and EFFSEQ, as those fields were having undesirable affects on online pages.

SELECT A.EMPLID , A.EMPL_RCD , A.DEPTID , A.JOBCODE , A.POSITION_NBR , A.EMPL_STATUS , A.ACTION , A.ACTION_DT , A.ACTION_REASON , A.LOCATION , A.JOB_ENTRY_DT , A.DEPT_ENTRY_DT , A.POSITION_ENTRY_DT , A.SHIFT , A.REG_TEMP , A.FULL_PART_TIME , A.FLSA_STATUS , A.OFFICER_CD , A.COMPANY , A.PAYGROUP , A.EMPL_TYPE , A.HOLIDAY_SCHEDULE , A.STD_HOURS , A.EEO_CLASS , A.EMPL_CLASS , A.SAL_ADMIN_PLAN , A.GRADE , A.GRADE_ENTRY_DT , A.STEP , A.STEP_ENTRY_DT , A.GL_PAY_TYPE , A.ACCT_CD , A.EARNS_DIST_TYPE , A.COMP_FREQUENCY , A.COMPRATE , A.CHANGE_AMT , A.CHANGE_PCT , A.ANNUAL_RT , A.MONTHLY_RT , A.HOURLY_RT , A.ANNL_BENEF_BASE_RT , A.SHIFT_RT , A.SHIFT_FACTOR , A.CURRENCY_CD , A.BUSINESS_UNIT , %CurrentDateIn , A.ADDS_TO_FTE_ACTUAL , A.CLASS_INDC , A.FICA_STATUS_EE , A.FTE , A.PRORATE_CNT_AMT , A.ENCUMB_OVERRIDE , A.PAY_SYSTEM_FLG , A.SETID_DEPT , A.SETID_JOBCODE , A.SETID_LOCATION , A.SETID_SALARY , A.REG_REGION , RG.COUNTRY ,A.PER_ORG , A.JOB_INDICATOR , A.CONTRACT_NUM , A.SUPV_LVL_ID , A.APPT_TYPE FROM PS_JOB A , PS_REG_REGION_TBL RG WHERE A.PER_ORG IN ('EMP','CWR') AND 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) 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) AND RG.REG_REGION = A.REG_REGION

  • 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

    Prompt Table: PERSON

    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
    3 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: DEPT_TBL

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

    Prompt Table: JOBCODE_TBL

    5 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    6 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
    7 ACTION Character(3) VARCHAR2(3) NOT NULL Action

    Prompt Table: ACTION_TBL

    8 ACTION_DT Date(10) DATE Action Date
    9 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code

    Prompt Table: ACTN_REASON_TBL

    10 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

    Prompt Table: LOCATION_TBL

    11 JOB_ENTRY_DT Date(10) DATE Job Entry Date
    12 DEPT_ENTRY_DT Date(10) DATE Department Entry Date
    13 POSITION_ENTRY_DT Date(10) DATE Position Entry Date
    14 SHIFT Character(1) VARCHAR2(1) NOT NULL Regular Shift
    1=1
    2=2
    3=3
    A=Any
    C=Compressed
    N=Not Applicable
    R=Rotating
    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 FLSA_STATUS Character(1) VARCHAR2(1) NOT NULL FLSA Status field
    A=Administrative
    C=Computer Employee
    E=Executive
    H=Highly Compensated Employees
    M=Management
    N=Nonexempt
    O=Outside Salesperson
    P=Professional
    V=Nonexempt Alt Overtime
    X=No FLSA Required
    18 OFFICER_CD Character(1) VARCHAR2(1) NOT NULL Officer Code
    C=Chairman
    D=Director
    N=None
    O=Officer
    P=President
    S=Secretary
    T=Treasurer
    V=Vice President
    19 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Prompt Table: COMPANY_TBL

    20 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group

    Prompt Table: PAYGROUP_TBL

    21 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
    E=Exception Hourly
    H=Hourly
    N=Not Applicable
    S=Salaried

    Prompt Table: PAYGRP_EMPLTYPE

    22 HOLIDAY_SCHEDULE Character(6) VARCHAR2(6) NOT NULL Holiday Schedule

    Prompt Table: HOLIDAY_TBL

    23 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
    24 EEO_CLASS Character(1) VARCHAR2(1) NOT NULL EEO Classification
    E=Exclude from Count
    N=None of the Above
    P=Production Trainee
    W=White-Collar Trainee
    25 EMPL_CLASS Character(3) VARCHAR2(3) NOT NULL Employee Classification

    Prompt Table: EMPL_CLASS_TBL
    Set Control Field: REG_REGION

    26 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
    27 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade

    Prompt Table: SAL_GRADE_TBL

    28 GRADE_ENTRY_DT Date(10) DATE Grade Entry Date
    29 STEP Number(2,0) SMALLINT NOT NULL Step

    Prompt Table: SAL_STEP_TBL

    30 STEP_ENTRY_DT Date(10) DATE Step Entry Date
    31 GL_PAY_TYPE Character(6) VARCHAR2(6) NOT NULL General Ledger Pay Type
    32 ACCT_CD Character(25) VARCHAR2(25) NOT NULL Combination Code

    Prompt Table: ACCT_CD_TBL

    33 EARNS_DIST_TYPE Character(1) VARCHAR2(1) NOT NULL Earnings Distribution Type
    A=By Amount
    H=By Hours
    N=None
    P=By Percent
    34 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

    35 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate
    36 CHANGE_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Change Amount
    37 CHANGE_PCT Signed Number(8,3) DECIMAL(6,3) NOT NULL Change Percent
    38 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
    39 MONTHLY_RT Number(19,3) DECIMAL(18,3) NOT NULL Monthly Rate
    40 HOURLY_RT Number(19,6) DECIMAL(18,6) NOT NULL Hourly Rate
    41 ANNL_BENEF_BASE_RT Number(19,3) DECIMAL(18,3) NOT NULL Specifies a compensation amount used to calculate benefits and benefit deductions for an employee. The base compensation amount may include commissions and bonuses in addition to regular compensation. If no Annual Benefits Base Rate is entered, then benefits calculations are based on an employee's regular compensation.
    42 SHIFT_RT Number(19,6) DECIMAL(18,6) NOT NULL Shift Differential Rate
    43 SHIFT_FACTOR Number(5,3) DECIMAL(4,3) NOT NULL Shift Differential Factor
    44 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

    Prompt Table: CURRENCY_CD_TBL

    45 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    46 ASOFDATE Date(10) DATE As of Date
    47 ADDS_TO_FTE_ACTUAL Character(1) VARCHAR2(1) NOT NULL Adds to FTE Actual Count
    48 CLASS_INDC Character(1) VARCHAR2(1) NOT NULL Classified/Unclassified Indc
    B=All
    C=Classified
    T=Temporary
    U=Unclassified
    49 FICA_STATUS_EE Character(1) VARCHAR2(1) NOT NULL FICA Status-Employee
    E=Exempt
    H=ER FICA Exmpt
    M=Medicare only
    N=Subject
    50 FTE Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence
    51 PRORATE_CNT_AMT Character(1) VARCHAR2(1) NOT NULL Prorate Contract Change Amount
    C=Prorate Over Contract Period
    N=No Proration of Change Amt.
    P=Prorate over Payment Period
    52 ENCUMB_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Encumbrance Override
    53 PAY_SYSTEM_FLG Character(2) VARCHAR2(2) NOT NULL Payroll System
    GP=Global Payroll
    NA=Payroll for North America
    OT=Other
    PI=Payroll Interface
    54 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID
    55 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code SetID
    56 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location SetID
    57 SETID_SALARY Character(5) VARCHAR2(5) NOT NULL Salary SetID
    58 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
    59 REG_REGION_COUNTRY Character(3) VARCHAR2(3) NOT NULL Reg Region Country
    60 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
    61 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
    62 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
    63 SUPV_LVL_ID Character(8) VARCHAR2(8) NOT NULL Supervisor Level ID for use in capability grade system
    64 APPT_TYPE Character(1) VARCHAR2(1) NOT NULL This field tracks whether a row is a Main Appointment (which all rows outside Japan are), or an Additional Appointment, which is a feature only used by Japanese customers.
    0=Core Appointment
    1=Japan Kenmu Appointment
    2=French Public Sector Appt
    3=Staffing