HR_DR_EMP_VW

(SQL View)
 Index Back

Direct Reports Employee

Retrieves the current job and name information for all employees in job.

SELECT JB.EMPLID , JB.EMPL_RCD , JB.SUPERVISOR_ID , JB.EMPL_STATUS , JB.POSITION_NBR , JB.REPORTS_TO , JB.HR_STATUS , JB.FULL_PART_TIME , JB.SETID_DEPT , JB.DEPTID , JB.SETID_LOCATION , JB.LOCATION , JB.SETID_JOBCODE , JB.JOBCODE , B.NAME , B.NAME_DISPLAY , B.LAST_NAME_SRCH , B.NAME_AC , B.LAST_NAME , B.FIRST_NAME FROM PS_JOB JB , PS_NAMES B WHERE JB.EMPLID = B.EMPLID AND JB.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JB1 WHERE JB1.EMPLID = JB.EMPLID AND JB1.EMPL_RCD = JB.EMPL_RCD AND JB1.EFFDT <= %CurrentDateIn) AND JB.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = JB.EMPLID AND JB2.EFFDT = JB.EFFDT AND JB2.EMPL_RCD = JB.EMPL_RCD) AND B.NAME_TYPE = 'PRI' AND B.EFFDT = ( SELECT MAX(A.EFFDT) FROM PS_NAMES A WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = A.NAME_TYPE AND A.EFFDT <= %CurrentDateIn )

  • Related Language Record: HR_DR_EMP_VWL

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
    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 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
    7 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
    A=Active
    I=Inactive
    8 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
    D=On Demand
    F=Full-Time
    P=Part-Time
    9 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    10 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    11 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
    12 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    13 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
    14 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    15 NAME Character(50) VARCHAR2(50) NOT NULL Name
    16 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    17 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    18 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
    19 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    20 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name