WORKER_PROMPT

(SQL View)
 Index Back

Worker Name Prompt

Selects only Employees and Contingent Workers . Used for prompt views for Worker EMPLIDs where the field being prompted into contains EFFDT. This view will then return only the Active Workers for that records EFFDT. Do not make EMPL_RCD a Key or this view cannot be used to prompt for EMPLID.

SELECT A.EMPLID ,A.EFFDT ,A.HR_STATUS , A.EMPL_RCD ,B.NAME , B.LAST_NAME_SRCH , B.SECOND_LAST_SRCH , B.FIRST_NAME_SRCH , B.NAME_AC ,A.EMPL_STATUS ,A.PER_ORG ,A.BUSINESS_UNIT ,A.DEPTID ,A.JOBCODE ,A.LOCATION ,A.COMPANY ,A.POSITION_NBR ,A.SETID_DEPT ,A.SETID_JOBCODE ,A.SETID_LOCATION FROM PS_JOB A , PS_PERSON_NAME B WHERE A.PER_ORG IN ('EMP','CWR') AND A.EMPLID = B.EMPLID 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 PERSID Character(11) VARCHAR2(11) NOT NULL Person ID

    Prompt Table: PERSON_NAME

    2 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    3 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive
    4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
    5 NAME Character(50) VARCHAR2(50) NOT NULL Name
    6 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    7 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Name
    8 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
    9 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
    10 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
    11 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

    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 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID
    19 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code SetID
    20 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location SetID