HR_DR_CNT5_VW

(SQL View)
 Index Back

Direct Reports Count

count of reports for each manager using access type 5

SELECT a.supervisor_id , a.supervis_empl_rcd , a.emplid , a.empl_rcd , COUNT(DISTINCT a1.emplid %Concat %Cast(a1.empl_rcd,number, character) ) FROM ps_hr_direct_rep_5 a , ps_hr_direct_rep_5 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 a.empl_rcd = A1.supervis_empl_rcd AND a1.drill_down_flag = 'Y' AND A1.hr_dr_level = 1 AND b.per_org IN ('EMP','CWR') 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.supervis_empl_rcd ,a.emplid ,a.empl_rcd

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
2 SUPERVISOR_ERN Number(3,0) SMALLINT NOT NULL This is used to hold the Employment Record Number of a supervisor.
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
5 DR_COUNT Number(4,0) SMALLINT NOT NULL Direct Reports Count