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
;
Like this:
Like Loading...
Related