SELECT DISTINCT hpx.business_group_id,
hpx.entry_grade_id,
hpx.job_id,
hpx.location_id,
hpx.organization_id,
hpx.fte,
hpx.max_persons,
hpx.name position,
ppx.full_name,
hier.lev
FROM hr_positions_x hpx,
per_assignments_x pax,
per_people_x ppx,
( SELECT pse.parent_position_id, LEVEL lev
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 = :p_hierarchy_name
AND TRUNC (SYSDATE) BETWEEN ppsv.date_from
AND NVL (
ppsv.date_to,
TO_DATE (
'31-Dec-4712',
'DD-MON-YYYY')))
START WITH pse.subordinate_position_id =
(SELECT position_id
FROM hr_positions_x
WHERE name = :p_position_name)
CONNECT BY PRIOR pse.parent_position_id = pse.subordinate_position_id
ORDER BY LEVEL ASC) hier
WHERE hier.parent_position_id = hpx.position_id
AND pax.position_id = hier.parent_position_id
AND pax.person_id = ppx.person_id
No comments:
Post a Comment