WITH xx_hierarchy AS
-- -------------------------------------------------------------------------
-- Form XX position hierarchy, of parent and child position_id's
-- -------------------------------------------------------------------------
(
SELECT pse.parent_position_id        parent_pos_id
,      pse.subordinate_position_id   child_pos_id
,      (
       -- Count number of active Employee holders of each child position
       SELECT COUNT(*)
       FROM   per_assignments_x             pax
       ,      per_people_x                  ppx
       ,      per_person_type_usages_x      pptux
       ,      per_person_types              ppt
       ,      hr_all_organization_units     haou
       WHERE  1 = 1
       AND    pax.position_id = pse.subordinate_position_id
       AND    pax.primary_flag(+) = 'Y
       AND    pax.person_id = ppx.person_id
       AND    pptux.person_id = ppx.person_id
       AND    ppt.person_type_id = pptux.person_type_id
       AND    ppt.user_person_type = 'Employee'
       AND    haou.organization_id = pax.organization_id
       )    num_of_holders
FROM   per_pos_structure_elements    pse 
WHERE  1 = 1
AND    pse.pos_structure_version_id = (
                                      SELECT ppsv.pos_structure_version_id 
                                      FROM   per_position_structures     pps     
                                      ,      per_pos_structure_versions  ppsv 
                                      WHERE  pps.position_structure_id = ppsv.position_structure_id 
                                      AND    pps.name = 'XX Hierarchy'
                                      AND    SYSDATE BETWEEN ppsv.date_from AND NVL(ppsv.date_to,SYSDATE + 1)
                                      )
)
,
xx_subordinates AS
-- -------------------------------------------------------------------------
-- For each position, scan the hiearchy downwards, and calculate the total
-- number of  Employee holders of all subordinate positions
-- -------------------------------------------------------------------------
(
SELECT pp.position_id   parent_pos_id
,      pp.name          parent_pos_name
,      pj.name          parent_job_name
,      (
       SELECT SUM(xxh_sub.num_of_holders)
       FROM   xx_hierarchy   xxh_sub
       CONNECT BY PRIOR xxh_sub.child_pos_id = xxh_sub.parent_pos_id
       START WITH xxh_sub.parent_pos_id = pp.position_id
       )    AS count_subordinates
FROM   per_positions      pp
,      per_jobs           pj
WHERE  1 = 1
AND    pp.job_id = pj.job_id
)
-- -------------------------------------------------------------------------
-- List active Employees, their current position, and their total number of
-- subordinate employees
-- -------------------------------------------------------------------------
SELECT ppx.full_name
,      ppx.employee_number
,      haou.name                      org_name
,      xxs.parent_pos_name            pos_name
,      xxs.parent_job_name
,      NVL(xxs.count_subordinates,0)  count_subordinates
FROM   xx_subordinates                xxs
,      per_people_x                   ppx
,      per_assignments_x              pax
,      per_person_type_usages_x       pptux
,      per_person_types               ppt
,      hr_all_organization_units      haou
WHERE  1 = 1
AND    pax.person_id = ppx.person_id
AND    pax.primary_flag = 'Y'
AND    pax.position_id = xxs.parent_pos_id
AND    pptux.person_id = ppx.person_id
AND    ppt.person_type_id = pptux.person_type_id
AND    ppt.user_person_type = 'Employee'
AND    haou.organization_id = pax.organization_id
ORDER BY ppx.full_name
;