SQL query to get the child department of a parent department from the department hierarchy in BIP — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

SQL query to get the child department of a parent department from the department hierarchy in BIP

I have written below query where I have hardcoded Organization id for which I want to fetch the child departments from department hierarchy in BIP report but the query is traversing upward in department hierarchy like getting the parent department of hardcoded Organization id

SELECT distinct
pdt.organization_id Inner_Child_Orgid
,pdt.parent_organization_id parent_organization_id
,LEVEL LVL
,MIN(LEVEL) OVER() AS MINVAL
FROM PER_DEPT_TREE_NODE_V pdt
--,hr_all_organization_units_vl hrao
where 1=1

and PDT.TREE_STRUCTURE_CODE = 'PER_DEPT_TREE_STRUCTURE'
start with pdt.ORGANIZATION_ID = 300000253796110
CONNECT BY PRIOR pdt.PARENT_ORGANIZATION_ID = pdt.ORGANIZATION_ID

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!