Query:
This example is if the hierarchy is at 4 levels, add new columns if you have more levels by adjusting the distance value
SELECT
per_org_l1.name AS Level1_name,
per_org_l2.name AS Level2_name,
(SELECT per_org_l3.name
FROM per_org_tree_node_rf org_tree_l3,
hr_organization_units_f_tl per_org_l3,
fnd_languages fnd_lang_l3
WHERE 1=1
AND org_tree_l3.tree_structure_code = ‘PER_ORG_TREE_STRUCTURE’
AND org_tree_l3.tree_code = ‘XYZ HR Hierarchy’
AND org_tree_l3.distance = 2
AND org_tree_l3.pk1_value = org_tree.pk1_value
AND per_org_l3.language = fnd_lang_l3.language_code
AND trunc(sysdate) BETWEEN nvl(per_org_l3.effective_start_date, sysdate – 1) AND nvl(per_org_l3.effective_end_date, sysdate +
1)
AND per_org_l3.organization_id (+) = to_number(org_tree_l3.ancestor_pk1_value)
AND nvl(per_org_l3.language, ‘US’) = ‘US’) as Level3_name,
(SELECT per_org_l4.name
FROM per_org_tree_node_rf org_tree_l4,
hr_organization_units_f_tl per_org_l4,
fnd_languages fnd_lang_l4
WHERE 1=1
AND org_tree_l4.tree_structure_code = ‘PER_ORG_TREE_STRUCTURE’
AND org_tree_l4.tree_code = ‘XYZ HR Hierarchy’
AND org_tree_l4.distance = 3
AND org_tree_l4.pk1_value = org_tree.pk1_value
AND per_org_l4.language = fnd_lang_l4.language_code
AND trunc(sysdate) BETWEEN nvl(per_org_l4.effective_start_date, sysdate – 1) AND nvl(per_org_l4.effective_end_date, sysdate +
1)
AND per_org_l4.organization_id (+) = to_number(org_tree_l4.ancestor_pk1_value)
AND nvl(per_org_l4.language, ‘US’) = ‘US’) AS Level4_name
FROM
per_org_tree_node_rf org_tree,
hr_organization_units_f_tl per_org_l1,
fnd_languages fnd_lang,
hr_organization_units_f_tl per_org_l2,
fnd_languages fnd_lang_parent
WHERE
1 = 1
AND org_tree.tree_structure_code = ‘PER_ORG_TREE_STRUCTURE’
AND org_tree.tree_code = ‘XYZ HR Hierarchy’
AND org_tree.distance = 1
AND per_org_l1.language = fnd_lang.language_code
AND trunc(sysdate) BETWEEN per_org_l1.effective_start_date AND per_org_l1.effective_end_date
AND per_org_l1.organization_id = to_number(org_tree.pk1_value)
AND per_org_l1.language = ‘US’
AND per_org_l2.language = fnd_lang_parent.language_code
AND trunc(sysdate) BETWEEN nvl(per_org_l2.effective_start_date, sysdate – 1) AND nvl(per_org_l2.effective_end_date, sysdate +
1)
AND per_org_l2.organization_id (+) = to_number(org_tree.ancestor_pk1_value)
AND nvl(per_org_l2.language, ‘US’) = ‘US’
keshav chittipolu 