HR_EMPOSTOT6_VW

(SQL View)
 Index Back

Reporting to Empty Position

Count of all indirect reports for each managers that report up through an empty position.

SELECT a.supervisor_id , a.supervis_empl_rcd , a.emplid , a.empl_rcd , a.reports_to , COUNT(DISTINCT a1.emplid %Concat %Cast(a1.empl_rcd,number, character) ) FROM ps_hr_direct_rep_6 a , ps_hr_direct_rep_6 a1 , PS_JOB B , PS_JOB C WHERE a.supervisor_flag = 'Y' AND a.emplid = A1.supervisor_id AND a.empl_rcd = A1.supervis_empl_rcd AND C.PER_ORG IN ('EMP', 'CWR') AND A.EMPLID = C.EMPLID AND A.EMPL_RCD = C.EMPL_RCD AND A.JOB_EFFDT = C.EFFDT AND C.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JBC WHERE JBC.EMPLID = C.EMPLID AND JBC.EFFDT = C.EFFDT AND JBC.EMPL_RCD = C.EMPL_RCD) AND B.PER_ORG IN ('EMP', 'CWR') AND b.reports_to > ' ' AND a1.drill_down_flag = 'N' 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 , a.reports_to

# 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 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
6 INDR_COUNT Number(7,0) INTEGER NOT NULL Indirect Reports Count