HR_EDRTOTCNT3VW(SQL View) |
Index Back |
|---|---|
Total direct reports countCount of all direct and indirect reports for each managers using access type 3 AND PER_ORG = EMP. No contingent workers. |
| SELECT a.supervisor_id , a.emplid , COUNT(*) FROM ps_hr_direct_rep_3 a , ps_hr_direct_rep_3 a1 , PS_JOB B WHERE a.drill_down_flag = 'Y' AND a1.drill_down_flag = 'Y' AND a.supervisor_flag = 'Y' AND PER_ORG ='EMP' AND a.emplid = A1.supervisor_id AND A1.EMPLID = B.EMPLID AND A1.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) GROUP BY a.supervisor_id ,a.emplid |
| # | 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 | INDR_COUNT | Number(7,0) | INTEGER NOT NULL | Indirect Reports Count |