Pages

Search This Blog

Friday, February 5, 2010

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.

No comments:

Post a Comment

Followers

Contributors