HR_VWAS_SRCH_6

(SQL View)
 Index Back

Direct Reports Access Type 6

Access Type 6 - Partial Position/Department Manager

SELECT A.SUPERVISOR_ID , A.SUPERVIS_EMPL_RCD , A.EMPLID , A.EMPL_RCD , A.EMPL_STATUS , A.POSITION_NBR , A.REPORTS_TO , 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_6 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 B.SETID_JOBCODE = JC.SETID AND B.JOBCODE = JC.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCE, JC, %CurrentDateIn)

  • Related Language Record: HR_VWAS_SRCHL_6

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
    2 SUPERVISOR_ERN Number(3,0) SMALLINT NOT NULL This is used to hold the Employment Record Number of a supervisor.
    3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    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 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    7 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
    8 JOB_EFFDT Date(10) DATE Job Effective Date
    9 TREE_LEVEL_NUM Number(3,0) SMALLINT NOT NULL Parent tree level if this is a part of the PSTREEBRANCH record
    10 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

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