Pages

Recently Viewed

Sunday, January 22, 2023

SQL to Get Departnment Hierarcy in Single line in Fusion

SELECT
h_child.organization_id
,LISTAGG(haou.name , '|') WITHIN GROUP (Order by distance) DeptHierarchy
FROM 
per_dept_tree_node_rf    pgtn_rf
,hr_all_organization_units haou
,hr_all_organization_units h_child
WHERE
pgtn_rf.ancestor_pk1_value = haou.organization_id
and pgtn_rf.pk1_value = h_child.organization_id  
and pgtn_rf.tree_code  ='XXXX_DEPT_STRUCTURE'
and NVL(:P_START_DATE,trunc(SYSDATE))  between haou.effective_start_date and haou.effective_end_date
and EXISTS(
SELECT 1
FROM
FND_TREE_VERSION ftv
WHERE
ftv.tree_code = pgtn_rf.tree_code
AND pgtn_rf.tree_version_id = ftv.tree_version_id
AND ftv.status = 'ACTIVE'
AND SYSDATE  between TRUNC(ftv.effective_start_date) AND TRUNC(ftv.effective_end_date)
)

No comments:

Post a Comment