BIP – Query to extract department Tree

Use the following query to extract department tree and its child departments. Please pass correct department name before executing:

WITH org_tree
AS (
        SELECT /*+ materialize */
               DISTINCT *
        FROM (
               SELECT  (
                               SELECT haoufv_p.name
                                 FROM hr_all_organization_units_f_vl haoufv_p
                                WHERE haoufv_p.organization_id = potnv.parent_organization_id
                                  AND TRUNC(SYSDATE) BETWEEN haoufv_p.effective_start_date AND haoufv_p.effective_end_date
                        ) parent_org_name
                      ,(
                               SELECT haoufv_c.name
                                 FROM hr_all_organization_units_f_vl haoufv_c
                                WHERE haoufv_c.organization_id = potnv.organization_id
                                  AND TRUNC(SYSDATE) BETWEEN haoufv_c.effective_start_date AND haoufv_c.effective_end_date
                        ) child_org_name
                       ,potnv.tree_structure_code
                       ,potnv.parent_organization_id parent_org_id
                       ,potnv.organization_id child_org_id
                       ,LEVEL levelcount
               FROM per_dept_tree_node_v potnv
			       ,fnd_tree_version ftv
              WHERE potnv.tree_structure_code = 'PER_DEPT_TREE_STRUCTURE'
			    AND potnv.tree_code = 'Global100'
				AND potnv.tree_version_id = ftv.tree_version_id
				AND ftv.tree_code = potnv.tree_code
				AND ftv.status = 'ACTIVE' 
				AND TRUNC(SYSDATE) BETWEEN ftv.effective_start_date AND ftv.effective_end_date
			   START WITH potnv.parent_organization_id IS NULL 
			 CONNECT BY PRIOR potnv.organization_id = potnv.parent_organization_id
               )
        ORDER BY levelcount ASC
        )
,dept_tree
 AS (
        SELECT /*+ materialize */
              level1.child_org_name "level1"
             ,level2.child_org_name "level2"
             ,level3.child_org_name "level3"
             ,level4.child_org_name "level4"
         FROM org_tree level1
		     ,org_tree level2
		     ,org_tree level3
		     ,org_tree level4
			 ,hr_all_organization_units_f haouf
       WHERE level1.child_org_id = level2.parent_org_id
	     AND level2.child_org_id = level3.parent_org_id
	     AND level3.child_org_id = level4.parent_org_id
         AND level1.parent_org_name IS NULL
		 AND haouf.organization_id = level4.child_org_id
		 AND TRUNC(SYSDATE) BETWEEN haouf.effective_start_date AND haouf.effective_end_date
        )
SELECT *
FROM dept_tree