HR_DRTOTCNT2_VW

(SQL View)
 Index Back

Total direct reports count

Count of all direct and indirect reports for each managers using access type 2

SELECT a.supervisor_id , a.emplid , COUNT(*) FROM ps_hr_direct_rep_2 a , ps_hr_direct_rep_2 a1 , PS_JOB B WHERE a.drill_down_flag = 'Y' AND a1.drill_down_flag = 'Y' AND a.supervisor_flag = 'Y' AND PER_ORG IN ('EMP', 'CWR') AND a.emplid = A1.supervisor_id AND A1.EMPLID = B.EMPLID AND A1.EMPL_RCD = B.EMPL_RCD 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