ERPstuff - Oracle EBS Community

Posted: **Sat Jun 14, 2008 7:11 am**

Dear all [:)] ,

Plz , I want to ask about what is SQL statments for extract report from Application database : <span id='hl' style='background-color: yellow'><b>Number of Supervisor for each employee .</b></span id='hl'>

Ex :

A > B > C :

A : 3 Supervisors

D > E > F > G :

D : 4 Supervisors

Posted: **Sun Jun 15, 2008 3:05 am**

plz zz zzzz ):

Is there funcation recive one parameter - persone id - and return number of supervisors for this one ?

Posted: **Tue Jun 17, 2008 3:28 am**

I have tried to find a best solution but I've written a sample funcation

and I wanted to share with you by :

FUNCTION XX_GET_SUPERVISOR_LEVELS(P_PERSON_ID number) RETURN NUMBER IS

c number := -1;

super_id number ;

p number := P_PERSON_ID;

BEGIN

while p is not null LOOP

select paaf.SUPERVISOR_ID INTO super_id

from per_all_assignments_f paaf

where paaf.PERSON_ID = p

and sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE;

p := super_id;

c := c+ 1;

END LOOP;

RETURN c;

EXCEPTION

WHEN NO_DATA_FOUND THEN RETURN 0;

END;

Thank u all for help me [V] [:(!] [V]

ERPstuff - Oracle EBS Community

[ Number of Supervisor for each employee ]

[ Number of Supervisor for each employee ]