Need help to pull Organization ID per Hierarchy Level

Rhai

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

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/23917/need-help-to-pull-organization-id-per-hierarchy-level

Hi,

Can you please elaborate your query a bit more? I can see you are able to pull the Ids already.

Also, check if the query on below link suffice your requirement to show org id for each row:

https://fusionhcmconsulting.com/2021/12/bip-query-to-extract-department-tree/

Thanks.

Rhai

Hi @MandeepGupta,
The query only pulls Enterprise, Organization ID for Level 3, Organization name for Level 1, organization name for level 2 and Organization Name for Level 3. the Screenshot below that I provided is that's what we wanted to pull.

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/61355#Comment_61355

Can you try below?

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_org_tree_node_v potnv
,fnd_tree_version ftv
WHERE potnv.tree_structure_code = 'PER_ORG_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"
,level1.child_org_id
,level2.child_org_name "level2"
,level2.child_org_id
,level3.child_org_name "level3"
,level3.child_org_id
,level4.child_org_name "level4"
,level4.child_org_id
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

Update the org tree name and try running.

Thanks.