• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 12, 2020
  • Reading time:3 mins read
You are currently viewing SQL Query to fetch Flattened Organization Hierarchy Tree
SQL Query to fetch Flattened Organization Hierarchy Tree

In this post,we will try to see how to create a flattened tree query from the hierarchy trees available (org tree/position tree).

First we will fetch the Org and Parent Org from the tree node tables and then depending upon the number of levels that the customer is using, we will have to left outer join the same temporary table that many number of times and select the org and parent org from those tables.

SQL Query:

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.