HR_VWAS_SRCH_3

(SQL View)
 Index Back

Direct Reports Access Type 3

Access Type 3 - Departing Manager id

SELECT A.SUPERVISOR_ID , A.EMPLID , A.EMPL_RCD , A.EMPL_STATUS , A.POSITION_NBR , A.JOB_EFFDT , A.HR_DR_LEVEL , A.SUPERVISOR_FLAG , A.DRILL_DOWN_FLAG , B.HR_STATUS , B.PER_ORG , B.FULL_PART_TIME , B.SETID_DEPT , B.DEPTID , A.DEPT_EFFDT , B.SETID_LOCATION , B.LOCATION , B.SETID_JOBCODE , B.JOBCODE , C.NAME , C.NAME_DISPLAY , C.LAST_NAME_SRCH , C.NAME_AC , C.LAST_NAME , C.FIRST_NAME , C.NAME_DISPLAY_SRCH , JC.DESCR FROM PS_HR_DIRECT_REP_3 A , PS_JOB B , PS_PERSON_NAME C , PS_JOBCODE_TBL JC WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.EMPLID = C.EMPLID AND A.JOB_EFFDT = B.EFFDT AND B.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = B.EMPLID AND JB2.EFFDT = B.EFFDT AND JB2.EMPL_RCD = B.EMPL_RCD) AND A.SUPERVISOR_FLAG = 'Y' AND A.DRILL_DOWN_FLAG = 'Y' AND B.SETID_JOBCODE = JC.SETID AND B.JOBCODE = JC.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCE, JC, %CurrentDateIn)

  • Related Language Record: HR_VWAS_SRCHL_3

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
    2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    4 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
    5 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    6 JOB_EFFDT Date(10) DATE Job Effective Date
    7 TREE_LEVEL_NUM Number(3,0) SMALLINT NOT NULL Parent tree level if this is a part of the PSTREEBRANCH record
    8 HR_DRPT_INDIRECT Character(1) VARCHAR2(1) NOT NULL This Y/N flag is used in the Direct Reports module to indicate whether a given employee/job has indirect reports.

    Y/N Table Edit

    Default Value: N

    9 DRILL_DOWN_FLAG Character(1) VARCHAR2(1) NOT NULL Y/N field to determine if the row will appear in the dynamic drill down query. These rows appear in the getsupervisor query.
    10 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
    A=Active
    I=Inactive
    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
    12 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
    D=On Demand
    F=Full-Time
    P=Part-Time
    13 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    14 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    15 DEPT_EFFDT Date(10) DATE Department Table Effective Date
    16 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
    17 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    18 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
    19 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    20 NAME Character(50) VARCHAR2(50) NOT NULL Name
    21 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    22 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    23 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
    24 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    25 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    26 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.
    27 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description