WORKER_CURR_VW

(SQL View)
 Index Back

Current Worker Status

Selects only Employees and Contingent Workers and provides their current JOB information. Only the Current Job Row is returned. - No future only rows are returned.

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.EXPECTED_RETURN_DT ,A.EXPECTED_END_DATE ,B.PROBATION_DT ,B.ORG_INSTANCE_ERN ,A.SETID_DEPT ,A.SETID_JOBCODE ,A.SETID_LOCATION FROM PS_JOB A , PS_PER_ORG_ASGN B WHERE A.PER_ORG IN ('EMP','CWR') AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND 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 Record

    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 EXPECTED_RETURN_DT Date(10) DATE Expected Return Date
    20 EXPECTED_END_DATE Date(10) DATE Expected End Date of a job.
    21 PROBATION_DT Date(10) DATE Probation Date
    22 ORG_INSTANCE_ERN Number(3,0) SMALLINT NOT NULL Indicates which EMPL_RCD (ERN) is considered the Organizational Instance for this particular ERN. An Organizational Instance defines an Instance of Employment or an Instance of Contingent Workforce. Hire, Termination, and Service dates are captured at the Organiational Instance level.
    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