We are looking to pull the Organization ID for each hierarchical level, specifically for Level 1, Level 2, and Level 3. Please see the attached screenshot for reference, and I’ve also included my current SQL query below.
Could you assist me in refining the SQL query to pull the Organization ID for these levels? Your expertise would be greatly appreciated.
WITH DEPT_HRCHY
AS (
SELECT PD.ORGANIZATION_ID
,(
SELECT PDS.NAME
FROM PER_ORG_TREE_NODE_CF CF
,HR_ALL_ORGANIZATION_UNITS PDS
,FND_TREE_VERSION VERS
WHERE PDS.ORGANIZATION_ID = CF.DEP31_PK1_VALUE
AND TREE.TREE_STRUCTURE_CODE = 'PER_ORG_TREE_STRUCTURE'
AND CF.DEP21_PK1_VALUE = TREE.PK1_START_VALUE
AND CF.TREE_CODE = VERS.tree_code
AND CF.TREE_VERSION_ID = VERS.TREE_VERSION_ID
AND CF.TREE_STRUCTURE_CODE = VERS.TREE_STRUCTURE_CODE
AND VERS.STATUS = 'ACTIVE'
AND rownum = 1
) ENTERPRISE
,(
SELECT PDS.NAME
FROM PER_ORG_TREE_NODE_CF CF
,HR_ALL_ORGANIZATION_UNITS PDS
,FND_TREE_VERSION VERS
WHERE PDS.ORGANIZATION_ID = CF.DEP30_PK1_VALUE
AND TREE.TREE_STRUCTURE_CODE = 'PER_ORG_TREE_STRUCTURE'
AND CF.DEP21_PK1_VALUE = TREE.PK1_START_VALUE
AND CF.DISTANCE >= 1
AND CF.TREE_CODE = VERS.tree_code
AND CF.TREE_VERSION_ID = VERS.TREE_VERSION_ID
AND CF.TREE_STRUCTURE_CODE = VERS.TREE_STRUCTURE_CODE
AND VERS.STATUS = 'ACTIVE'
AND rownum = 1
) LEVEL_1
,(
SELECT PDS.NAME
FROM PER_ORG_TREE_NODE_CF CF
,HR_ALL_ORGANIZATION_UNITS PDS
,FND_TREE_VERSION VERS
WHERE PDS.ORGANIZATION_ID = CF.DEP29_PK1_VALUE
AND TREE.TREE_STRUCTURE_CODE = 'PER_ORG_TREE_STRUCTURE'
AND CF.DEP21_PK1_VALUE = TREE.PK1_START_VALUE
AND CF.DISTANCE >= 2
AND CF.TREE_CODE = VERS.tree_code
AND CF.TREE_VERSION_ID = VERS.TREE_VERSION_ID
AND CF.TREE_STRUCTURE_CODE = VERS.TREE_STRUCTURE_CODE
AND VERS.STATUS = 'ACTIVE'
AND rownum = 1
) LEVEL_2
,(
SELECT PDS.NAME
FROM PER_ORG_TREE_NODE_CF CF
,HR_ALL_ORGANIZATION_UNITS PDS
,FND_TREE_VERSION VERS
WHERE PDS.ORGANIZATION_ID = CF.DEP28_PK1_VALUE
AND TREE.TREE_STRUCTURE_CODE = 'PER_ORG_TREE_STRUCTURE'
AND CF.DEP21_PK1_VALUE = TREE.PK1_START_VALUE
AND CF.DISTANCE >= 3
AND CF.TREE_CODE = VERS.tree_code
AND CF.TREE_VERSION_ID = VERS.TREE_VERSION_ID
AND CF.TREE_STRUCTURE_CODE = VERS.TREE_STRUCTURE_CODE
AND VERS.STATUS = 'ACTIVE'
AND rownum = 1
) LEVEL_3
FROM HR_ALL_ORGANIZATION_UNITS PD
,PER_ORG_TREE_NODE TREE
WHERE 1 = 1
AND PD.ORGANIZATION_ID = TREE.PK1_START_VALUE
AND TREE.tree_code = 'BEE Organization Tree'
AND TREE.TREE_STRUCTURE_CODE = 'PER_ORG_TREE_STRUCTURE'
)
SELECT DISTINCT A.*
FROM dept_hrchy A
WHERE A.LEVEL_1 IS NOT NULL
---AND A.ORGANIZATION_ID = NVL(:P_ORGANIZATION_ID,A.ORGANIZATION_ID)
ORDER BY A.enterprise
,A.LEVEL_1
,A.LEVEL_2
,A.LEVEL_3
