We can use the below query to get the Position Hierarchy of a Position traversing up to the top level.
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.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM