SQL Query to Retrieve Department Hierarchy with Project and Expense Codes

SELECT DISTINCT
    dhou.ATTRIBUTE2 AS Project_Code,
    dhou.ATTRIBUTE3 AS Expense_Code,
    dhou.name AS DEPT_NAME,
    dhou1.name AS PARENT_DEPARTMENT_NAME
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 dhou1.organization_id(+) = B.parent_pk1_value
    AND dhou.organization_id = B.pk1_start_value
-- AND dhou.name = 'ABC-GG-SS-REG'
START WITH dhou.name = 'ABC-BU-AU'
CONNECT BY PRIOR B.pk1_start_value = 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.