SQL Query to fetch Flattened Organization Hierarchy Tree

WITH ORG_TREE
AS (
	SELECT /*+ materialize */
		DISTINCT *
	FROM (
		SELECT (
				SELECT p.name
				FROM hr_all_organization_units_f_vl p
				WHERE p.organization_id = ot.parent_organization_id
					AND rownum = 1
				) parent_org_name
			,(
				SELECT c.name
				FROM hr_all_organization_units_f_vl c
				WHERE c.organization_id = ot.organization_id
					AND rownum = 1
				) org_name
			,(
				SELECT c.attribute_number1
				FROM hr_all_organization_units_f_vl c
				WHERE c.organization_id = ot.organization_id
					AND rownum = 1
				) org_legacy
			,ot.tree_structure_code
			,ot.parent_organization_id parent_org_id
			,ot.organization_id org_id
			,LEVEL levelcount
		FROM per_org_tree_node_v ot
		WHERE ot.tree_structure_code = 'PER_ORG_TREE_STRUCTURE'
			AND ot.tree_code = 'LPER_ORG_TREE' START
		WITH ot.parent_organization_id IS NULL CONNECT BY PRIOR ot.organization_id = ot.parent_organization_id
		)
	ORDER BY levelcount ASC
	)
	,flattened_tree
AS (
	SELECT /*+ materialize */
		lev01.ORG_NAME LVL1_ORG
		,lev02.ORG_NAME LVL2_ORG
		,lev03.ORG_NAME LVL3_ORG
		,lev04.ORG_NAME LVL4_ORG
		,lev05.ORG_NAME LVL5_ORG
		,lev06.ORG_NAME LVL6_ORG
		,lev07.ORG_NAME LVL7_ORG
	FROM ORG_TREE lev01
	LEFT OUTER JOIN ORG_TREE lev02 ON lev01.org_id = lev02.parent_org_id
	LEFT OUTER JOIN ORG_TREE lev03 ON lev02.org_id = lev03.parent_org_id
	LEFT OUTER JOIN ORG_TREE lev04 ON lev03.org_id = lev04.parent_org_id
	LEFT OUTER JOIN ORG_TREE lev05 ON lev04.org_id = lev05.parent_org_id
	LEFT OUTER JOIN ORG_TREE lev06 ON lev05.org_id = lev06.parent_org_id
	LEFT OUTER JOIN ORG_TREE lev07 ON lev06.org_id = lev07.parent_org_id
	WHERE lev01.PARENT_ORG_NAME IS NULL
	)
SELECT * FROM flattened_tree

If you have any questions, please feel free to reach out to me by posting in comments section.

If you are interested in learning Fusion Technical tools go through this post

If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.