How to get Position Hierarchy in SQL Query?
How to get Position Hierarchy in SQL Query?

 1,011 total views

We can use the below query to get the Position Hierarchy of a Position traversing up to the top level.

SQL Query:

SELECT hap.POSITION_CODE
 ,hap.position_id
 ,hap.name
 ,(SELECT name
  FROM HR_ALL_POSITIONS
  WHERE POSITION_ID = pphf.PARENT_POSITION_ID
  ) Parent_Position_NAME
 ,decode(hap.ACTIVE_STATUS, 'A', 'Active', 'I', 'Inactive') ACTIVE_Status
 ,pj.manager_level
 ,pj.name
FROM HR_ALL_POSITIONS hap
 ,PER_JOBS_F_VL PJ
 ,PER_POSITION_HIERARCHY_F pphf
WHERE hap.POSITION_ID = pphf.POSITION_ID
 AND hap.job_id = pj.job_id
 AND sysdate BETWEEN pphf.effective_start_date
  AND pphf.effective_end_date START
WITH pphf.position_id = :INPUT_POSITION_ID CONNECT BY PRIOR pphf.PARENT_POSITION_ID = pphf.POSITION_ID 

If you have any questions, please feel free to reach out to me by posting in comments section.

If you are interested in learning Fusion Technical tools go through this post

If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply