Extract Employee and Supervisor Based on Position Hierarchy

This SQL will give you employee and Supervisor as per position hierarchy .

/* Formatted on 2009/10/19 15:01 (Formatter Plus v4.8.8) */
SELECT LPAD (‘ ‘, 5 * LEVEL) || has.NAME hierarchy, LEVEL,
hap.NAME parent_name, pse.parent_position_id, has.NAME child_name,
pse.subordinate_position_id
FROM (SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV (‘LANG’)) hap,
(SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV (‘LANG’)) has,
per_pos_structure_elements pse
WHERE pse.business_group_id = 81
AND hap.position_id = pse.parent_position_id
AND has.position_id = pse.subordinate_position_id
start with pse.parent_position_id = –40979 Base Position Id (like CEO etc)
CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
AND PRIOR pse.business_group_id = pse.business_group_id
ORDER BY 4

Advertisements

One thought on “Extract Employee and Supervisor Based on Position Hierarchy

  1. Excellent Work. Is there a way we can add the names of the Supervisor(parent) and Employees(Child) to this query. A good to have would be the approval assignments and approval limits as well. This will complete the full position hierarchy query.

    Thanks,
    Asad.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s