HR_DRPT_1B_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 does not return employee name columns. |
| SELECT DISTINCT OPR.ROWSECCLASS , JOB.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 , JOB.PER_ORG FROM PS_JOB JOB ,PS_SJT_CLASS_ALL CLS , PSOPRDEFN OPR WHERE 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 Rcd Nbr |
| 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 SetID |
| 10 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 11 | SETID_LOCATION | Character(5) | VARCHAR2(5) NOT NULL | Location SetID |
| 12 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 13 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL | Job Code SetID |
| 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 | 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 |