HR_DRPT_4A_VW(SQL View) |
Index Back |
|---|---|
Direct Reports by "Reports To"Used internally by the HR Direct Reports class, this view returns all employee/jobs that report to a position, when the supervisor's position is "joined" to this view. This view can only be used to successfully return all employees when Full Position Management has been implemented. This version of the view joins to the PERSON_NAME table to return employee name columns. |
| SELECT A.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JOB.EFFSEQ , JOB.EMPL_STATUS , JOB.HR_STATUS , JOB.FULL_PART_TIME , JOB.SETID_DEPT , JOB.DEPTID , JOB.SETID_LOCATION , JOB.LOCATION , JOB.SETID_JOBCODE , JOB.JOBCODE , JOB.POSITION_NBR , JOB.POSITION_OVERRIDE , JOB.SUPERVISOR_ID , A.NAME , A.NAME_DISPLAY , A.LAST_NAME_SRCH , A.NAME_AC , A.LAST_NAME , A.FIRST_NAME , JOB.PER_ORG , JOB.REPORTS_TO FROM PS_PERSON_NAME A , PS_JOB JOB WHERE A.EMPLID = JOB.EMPLID AND (JOB.POSITION_NBR > ' ' OR REPORTS_TO > ' ') AND JOB.EFFSEQ=( SELECT MAX(JOB3.EFFSEQ) FROM PS_JOB JOB3 WHERE JOB.EMPLID=JOB3.EMPLID AND JOB.EMPL_RCD=JOB3.EMPL_RCD AND JOB.EFFDT=JOB3.EFFDT) |
| # | 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 Rcd Nbr |
| 3 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 4 | EFFSEQ |
Number(3,0) | SMALLINT NOT NULL | Effective Sequence |
| 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 | HR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
HR Status
A=Active I=Inactive |
| 7 | FULL_PART_TIME | Character(1) | VARCHAR2(1) NOT NULL |
Full/Part Time
D=On Demand F=Full-Time P=Part-Time |
| 8 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department SetID |
| 9 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 10 | SETID_LOCATION | Character(5) | VARCHAR2(5) NOT NULL | Location SetID |
| 11 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 12 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL | Job Code SetID |
| 13 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 14 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL | Position Number |
| 15 | POSITION_OVERRIDE | Character(1) | VARCHAR2(1) NOT NULL | Override Position Data |
| 16 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Supervisor ID |
| 17 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 18 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 19 | LAST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 20 | NAME_AC | Character(50) | VARCHAR2(50) NOT NULL | Alternate Character Name |
| 21 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 22 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 23 | 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 |
| 24 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL | Reports To Position Number |