HR_DR_ECNT3_VW

(SQL View)
 Index Back

Direct Reports Count

count of reports for each manager 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 a.hr_dr_level = 1 AND a.supervisor_flag = 'Y' AND a.emplid = A1.supervisor_id AND a1.drill_down_flag = 'Y' AND A1.hr_dr_level = 1 AND b.per_org = 'EMP' 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 DR_COUNT Number(4,0) SMALLINT NOT NULL Direct Reports Count