HCR_SUP_ID_VW

(SQL View)
 Index Back

Supervisor/Manager Name Prompt

Selects only Employees and Contingent Workers . Used for prompt views for Supervisor/Manager EMPLIDs where the field being prompted into contains EFFDT. This view will then return the Active as well as Inactive Supervisors 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.EMPL_RCD ,B.NAME ,B.LAST_NAME_SRCH ,B.SECOND_LAST_SRCH ,B.FIRST_NAME_SRCH ,B.NAME_AC ,A.PER_ORG ,A.BUSINESS_UNIT ,A.DEPTID ,A.JOBCODE ,A.HR_STATUS 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)

  • Related Language Record: HCR_SUP_ID_VWL

  • 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_NAME

    2 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    4 NAME Character(50) VARCHAR2(50) NOT NULL Name
    5 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    6 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
    7 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
    8 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
    9 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

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

    Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

    Prompt Table: BUSUNIT_HR_VW

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

    Prompt Table: DEPT_TBL

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

    Prompt Table: JOBCODE_TBL

    13 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive