HR_DRPT_1A_VW(SQL View) |
Index Back |
|---|---|
Direct Reports By Dept TreeUsed internally by the HR Direct Reports class, this view returns all employees for which the user has access according to the user's RowSecClass, when the user's RowSecClass is joined to this view. Note: Fast Security must be implemented. This version of the view joins to the PERSON_NAME table to return employee name columns. |
| SELECT DISTINCT OPR.ROWSECCLASS , 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 FROM PS_PERSON_NAME A , PS_JOB JOB , PS_SJT_CLASS_ALL CLS , PSOPRDEFN OPR WHERE A.EMPLID = JOB.EMPLID AND JOB.HR_STATUS = 'A' 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) AND JOB.DEPTID=CLS.SCRTY_KEY2 AND JOB.SETID_DEPT=CLS.SCRTY_KEY1 AND (CLS.SCRTY_SET_CD = 'PPLJOB' AND CLS.SCRTY_TYPE_CD = '001' AND CLS.CLASSID = OPR.ROWSECCLASS ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  ROWSECCLASS |
Character(30) | VARCHAR2(30) NOT NULL | The class used to determine row level security |
| 2 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 3 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 4 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 5 | EFFSEQ |
Number(3,0) | SMALLINT NOT NULL | Effective Sequence |
| 6 | 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 |
| 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 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL | Position Number |
| 16 | POSITION_OVERRIDE | Character(1) | VARCHAR2(1) NOT NULL | Override Position Data |
| 17 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Supervisor ID |
| 18 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 19 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 20 | LAST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 21 | NAME_AC | Character(50) | VARCHAR2(50) NOT NULL | Alternate Character Name |
| 22 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 23 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 24 | 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 |