Query to get Organization trees
if your tree version is Active and row flattened you can use
--------------------------------------------
SELECT haou.name,
haou.organization_id,
pgtn_rf.distance,
pk1_value,
tree_version_id,
tree_code,
tree_structure_code,
is_leaf,
pk2_value,
pk3_value,
pk4_value,
pk5_value,
ancestor_pk1_value,
ancestor_pk2_value,
ancestor_pk3_value,
ancestor_pk4_value,
ancestor_pk5_value
FROM per_org_tree_node_rf pgtn_rf,
hr_all_organization_units_f_vl haou
WHERE pgtn_rf.ancestor_pk1_value = haou.organization_id
AND trunc(sysdate) BETWEEN haou.effective_start_date AND haou.effective_end_date
--------------------------------------------
If you want the data directly (without row flattening), check out the following tables/views:
PER_ORG_TREE_NODE
FND_TREE_VL
FND_TREE_VERSION_VL
as well as hr_all_organization_units_f_vl to get details about the organization itself.
x
