Hi

I am using

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

Letz say i have a table with the following structure..
--------------------------------------------------
EMPNO ReportingTo(manager) Eff.Date
--------------------------------------------------
1000 1001 1-Aug-01
1001 1000 1-Aug-01
1002 1001 1-Aug-01
1003 1002 1-Aug-01
1004 1000 1-Aug-01
1005 1003 1-Aug-01

1000 1003 1-Sep-01
1001 1004 1-Sep-01

1005 1001 20-aug-01

1003 9999 1-Sep-01
1004 1000 1-Sep-01
1005 1002 1-Sep-01

1000 1004 8-Sep-01
1005 1001 9-Sep-01

9999 1-jan-01
------------------------------------------

Now i wanted to find the tree structure for an given employee on a given date.

i.e. list of employees reporting to the given employee on a GIVEN DATE..

If i give

emp = 1005 and
date <=01-sep-2001

then i should get the tree (level) of the given employee (1005) and the max record (based on the date condition 01-sep-2001 ) of the employee reporting to the given employee !!!

i.e.

1005 1002
1002 1001
1001 1004
1004 1000
1000 1003
1003 9999

Rgds