HR_DR_VW_2(SQL View) |
Index Back |
|---|---|
Direct Reports Access Type 2Access Type 2 - Supervisor Id This table contains is the direct reports reporting structure based on the "Supervisor Id" field on the Job record to determine reporting relationships. Note that this method does not differentiate between multiple concurrent jobs that the supervisor might hold. |
| 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 , 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 FROM PS_HR_DIRECT_REP_2 A , PS_JOB B , PS_NAMES C 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 C.NAME_TYPE = 'PRI' AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_NAMES C1 WHERE C1.EMPLID = C.EMPLID AND C1.NAME_TYPE = C.NAME_TYPE AND C1.EFFDT <= %CurrentDateIn ) |
| # | 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 | SETID_LOCATION | Character(5) | VARCHAR2(5) NOT NULL | Location Set ID |
| 16 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 17 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL | Job Code Set ID |
| 18 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 19 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 20 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 21 | LAST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 22 | NAME_AC | Character(50) | VARCHAR2(50) NOT NULL | Alternate Character Name |
| 23 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 24 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |