SQL Query to get the parent departments

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.

Posted by : Mohammad