SQL Query to Retrieve Department Tree Hierarchy in Oracle HCM

SELECT DISTINCT 
    B.tree_code, 
    B.tree_node_id, 
    B.pk1_start_value, 
    B.last_update_date, 
    dhou.name  AS DEPT_NAME, 
    B.pk2_start_value, 
    B.pk3_start_value, 
    B.pk4_start_value, 
    B.pk5_start_value, 
    B.pk1_end_value, 
    B.pk2_end_value, 
    B.pk3_end_value, 
    B.pk4_end_value, 
    B.pk5_end_value, 
    B.parent_pk1_value, 
    dhou1.name AS PARENT_DEPARTMENT_NAME, 
    B.parent_pk2_value, 
    B.parent_pk3_value, 
    B.parent_pk4_value, 
    B.parent_pk5_value 
FROM   
    fnd_tree_version A, 
    per_dept_tree_node B, 
    hr_all_organization_units_vl dhou, 
    hr_all_organization_units_vl dhou1 
WHERE  
    A.status = 'ACTIVE' 
    AND A.tree_version_id = B.tree_version_id 
    AND dhou.organization_id = B.pk1_start_value 
    AND dhou1.organization_id(+) = B.parent_pk1_value

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.