How to find employee's organization structure

Oralce has provided CONNECT BY claused in SQL query which can be used to find data in a hierarchical structure.

To find Organization structure for an employee in PER_ALL_PEOPLE_F table following query can be used,

SELECT DISTINCT
B.FULL_NAME EMP_NAME
,DECODE(B.PERSON_TYPE_ID,6,'EMPLOYEE','TEMP') EMP_TYPE
,C.FULL_NAME SUPERVISOR_NAME
,LEVEL
FROM PER_ALL_ASSIGNMENTS_F A, PER_ALL_PEOPLE_F B, PER_ALL_PEOPLE_F C
WHERE A.PERSON_ID=B.PERSON_ID
AND SYSDATE BETWEEN B.EFFECTIVE_START_DATE AND B.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN C.EFFECTIVE_START_DATE AND C.EFFECTIVE_END_DATE
AND B.PERSON_TYPE_ID IN (6,103)
AND A.SUPERVISOR_ID = C.PERSON_ID
START WITH A.PERSON_id = &starting_person_id
CONNECT BY PRIOR A.PERSON_id = SUPERVISOR_id
ORDER BY LEVEL,SUPERVISOR_NAME

This would return organizational for the input employee identified by &starting_person_id. In other words, it would give the employee and supervisor relationship of the tree of which the starting node is &starting_person_id.

LEVEL: This is a oracle pseudo column for the CONNECT BY queries.