JOB_ALL_CURR_VW

(SQL View)
 Index Back

Curent Job - All Persons

Selects all Persons in JOB and shows their Current information for each EMPLID/EMPL_RCD pair. This view will include Employees, Contingent Workers, and Persons of Interest. Use the PER_ORG field to determine which organzational relationship they have. Use EMPL_STATUS to determine their current status.

SELECT A.EMPLID ,A.EMPL_RCD ,A.EFFDT ,A.EFFSEQ ,A.EMPL_STATUS ,A.HR_STATUS ,A.PER_ORG ,A.ACTION ,A.ACTION_REASON ,A.ACTION_DT ,A.REG_REGION ,A.BUSINESS_UNIT ,A.DEPTID ,A.JOBCODE ,A.LOCATION ,A.COMPANY ,A.POSITION_NBR ,A.PAY_SYSTEM_FLG ,A.PAYGROUP ,A.REG_TEMP ,A.FULL_PART_TIME ,A.SETID_DEPT ,A.SETID_JOBCODE ,A.SETID_LOCATION FROM PS_JOB A WHERE A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JOB2 WHERE A.EMPLID = JOB2.EMPLID AND A.EMPL_RCD = JOB2.EMPL_RCD AND JOB2.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = A.EMPLID AND JOB3.EMPL_RCD = A.EMPL_RCD AND JOB3.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

    Prompt Table: PERSON

    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

    Default Value: 0

    3 EFFDT_NOKEY Date(10) DATE Used in Records /Views where the EFFDT won't be a key
    4 EFFSEQ_NOKEY Number(3,0) SMALLINT NOT NULL Field used in views where EFFSEQ won't be a key
    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 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

    Default Value: POI

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

    Prompt Table: ACTION_TBL

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

    Prompt Table: ACTN_REASON_TBL

    10 ACTION_DT Date(10) DATE Action Date

    Default Value: %date

    11 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region

    Default Value: OPR_DEF_TBL_HR.REG_REGION

    Prompt Table: REG_STANDARD_VW

    12 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

    Prompt Table: BUSUNIT_HR_VW

    13 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: DEPT_TBL

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

    Prompt Table: JOBCODE_TBL

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

    Prompt Table: LOCATION_TBL

    16 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Prompt Table: COMPANY_TBL

    17 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

    Prompt Table: POSN_DATA_VW

    18 PAY_SYSTEM_FLG Character(2) VARCHAR2(2) NOT NULL Payroll System
    GP=Global Payroll
    NA=Payroll for North America
    OT=Other
    PI=Payroll Interface
    19 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
    20 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
    R=Regular
    T=Temporary
    21 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
    D=On Demand
    F=Full-Time
    P=Part-Time
    22 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID
    23 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code SetID
    24 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location SetID