We can use the connect by prior function provided by Oracle to traverse the Dept security Tree . Suppose an Organization has 7 levels in Dept tree i.e. 1 to 7 .
We want to traverse the dept tree from level 3 to level 6.
Also we know the deptid at the level 3 say 'LVL3-DPT' .
We can use the following SQL to traverse the whole tree (3-6) starting from 'LVL3-DPT'. Only child of 'LVL3-DPT' will be seen in sql output. The starting deptid has to be used with "START WITH " clause.
SELECT TR.TREE_NODE , TR.TREE_LEVEL_NUM
FROM PSTREENODE TR
WHERE TR.TREE_NAME = 'DEPT_SECURITY'
AND TR.SETID = $SETID
AND TR.EFFDT = (SELECT MAX(TR_ED.EFFDT) FROM PSTREENODE TR_ED
WHERE TR.SETID = TR_ED.SETID AND TR.TREE_NAME = TR_ED.TREE_NAME
AND TR_ED.EFFDT <= TO_DATE (SYSDATE, 'DD/MM/YY'))
AND LEVEL <= (6 - 3 + 1)
CONNECT BY PRIOR TR.TREE_NODE = TR.PARENT_NODE_NAME
START WITH TR.TREE_NODE = 'LVL3-DPT'
