Home » SQL & PL/SQL » SQL & PL/SQL » POSITIONAL HIERARCHY
icon1.gif  POSITIONAL HIERARCHY [message #235286] Fri, 04 May 2007 13:51 Go to next message
rbertrand
Messages: 45
Registered: November 2005
Member
Hello,

I've been assigned the task of reporting on the positional hiearchy. I've written the following sql:

select ppse.parent_position_id, ppse.subordinate_position_id
from per_pos_structure_elements ppse,
fnd_user fndu,
-- per_all_positions pap,
per_all_assignments_f paaf
where fndu.user_id = 1759
and fndu.employee_id = paaf.person_id
and paaf.position_id = ppse.parent_position_id
and paaf.assignment_status_type_id in (1,95,98)
and sysdate between paaf.effective_start_date and paaf.effective_end_date

In the sql, I am getting the person logged in's position and the positions that report to that position. It works fine until I get to a subordinate_position_id that is also a parent_position_id and has subordinates under it.

Is there a way to get these guys by modifying the sql above? If so, could someone shed some light my way??

Thanks very much! Embarassed Embarassed
Re: POSITIONAL HIERARCHY [message #235288 is a reply to message #235286] Fri, 04 May 2007 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at Hierarchical Queries and Hierarchical Query Examples.

Regards
Michel
Re: POSITIONAL HIERARCHY [message #235302 is a reply to message #235286] Fri, 04 May 2007 15:56 Go to previous message
rbertrand
Messages: 45
Registered: November 2005
Member
very good. Thanks for the tip!!
Previous Topic: Create csv from table or query
Next Topic: Row Locks and Physical blocks
Goto Forum:
  


Current Time: Wed Dec 07 14:41:41 CST 2016

Total time taken to generate the page: 0.09158 seconds