CREATE table INFORMATION
(
  ID             number(8) not null,
  DIVISIONMGR    VARCHAR2(20),
  REGIONMGR      VARCHAR2(20),
  LOCALMGR       VARCHAR2(20)
);
insert into INFORMATION values ('1', 'DIVISIONMGR1','RegionMGR1','LOCALMGR1');
insert into INFORMATION values ('2', 'DIVISIONMGR1','RegionMGR1','LOCALMGR2');
insert into INFORMATION values ('3', 'DIVISIONMGR1','RegionMGR1','LOCALMGR3');
insert into INFORMATION values ('4', 'DIVISIONMGR2','REGIONMGR2','LOCALMGR4');
insert into INFORMATION values ('5', 'DIVISIONMGR2','RegionMGR2','LOCALMGR5');
insert into INFORMATION values ('6', 'DIVISIONMGR2','RegionMGR2','LOCALMGR3');
insert into INFORMATION values ('7', 'DIVISIONMGR2','REGIONMGR3','LOCALMGR6');
....
....

ID    DIVISIONMGR     REGIONMGR           LOCALMGR
--------------------------------------------------
1     DIVISIONMGR1   RegionMGR1        LOCALMGR1
2     DIVISIONMGR1   RegionMGR1        LOCALMGR2
3     DIVISIONMGR1   RegionMGR1        LOCALMGR3
4     DIVISIONMGR2   REGIONMGR2        LOCALMGR4
5     DIVISIONMGR2   REGIONMGR2        LOCALMGR5
6     DIVISIONMGR2   REGIONMGR2        LOCALMGR3  (( -- Here some times a LOCALMGR can be belongs to two different REGIONMGR's)                    
7     DIVISIONMGR5   REGIONMGR3        LOCALMGR6
8     DIVISIONMGR5   REGIONMGR3        LOCALMGR5
9     DIVISIONMGR5   REGIONMGR3        LOCALMGR7
 
....
...

 I am not sure how to define a LEVEL if same LOCALMGR belongs to SAME REGIONMGR's. Please advise.

ID    DIVISIONMGR      LEVEL ( Here I need values for Level. As I don't know how get.. just mention null for all)
----------------------------------------------------------
1     DIVISIONMGR1      
2     DIVISIONMGR1      
3     DIVISIONMGR1      
4     DIVISIONMGR2      
5     DIVISIONMGR2      
6     DIVISIONMGR2                        
7     DIVISIONMGR5      
8     DIVISIONMGR5      
9     DIVISIONMGR5      
10    RegionMGR1         
11    RegionMGR1        
12    RegionMGR1
13    REGIONMGR2        
14    REGIONMGR2
15    REGIONMGR2
16    REGIONMGR3 
17    REGIONMGR3
18    REGIONMGR3  
19    LOCALMGR1
20    LOCALMGR2
21    LOCALMGR3       
22    LOCALMGR5
23    LOCALMGR4     
24    LOCALMGR7      
25    LOCALMGR5       
26    LOCALMGR6     
27    LOCALMGR3
....
...

create sequence inf_sq
 start with 1
 increment by 1
 minvalue 1
 maxvalue 2000;

CREATE table INFORMATION
(
  ID             number(8) default inf_seq.nextval,
  DIVISIONMGR    VARCHAR2(20),
  REGIONMGR      VARCHAR2(20),
  LOCALMGR       VARCHAR2(20)
);

ID     MGR    LEVEL 
--------------------- ( by mistake I mentioned DIVISIONMGR)

with data(ID,MGR)
 as ( select id,manager from <tab> t1
       ---

  not getting logic to write the query...

1     DIVISIONMGR1   RegionMGR1        LOCALMGR1
2     DIVISIONMGR1   RegionMGR1        LOCALMGR2
3     DIVISIONMGR1   RegionMGR1        LOCALMGR3
4     DIVISIONMGR2   REGIONMGR2        LOCALMGR4
5     DIVISIONMGR2   REGIONMGR2        LOCALMGR5
6     DIVISIONMGR2   REGIONMGR2        LOCALMGR3  (( -- Here some times a LOCALMGR can be belongs to two different REGIONMGR's)                    
7     DIVISIONMGR5   REGIONMGR3        LOCALMGR6
8     DIVISIONMGR5   REGIONMGR3        LOCALMGR5
9     DIVISIONMGR5   REGIONMGR3        LOCALMGR7
 




ID    DIVISIONMGR      LEVEL 
----------------------------------------------------------
1     DIVISIONMGR1      0
2     DIVISIONMGR1      0
3     DIVISIONMGR1      0
4     DIVISIONMGR2      0
5     DIVISIONMGR2      0
6     DIVISIONMGR2      0                 
7     DIVISIONMGR5      0
8     DIVISIONMGR5      0
9     DIVISIONMGR5      0
10    RegionMGR1        1  ( As REGIONMGR1 is belongs to DIVISIONmgr1. here DIVISIONMGR1 'ID' is '1'from ID columns)
11    RegionMGR1        1
12    RegionMGR1        1
13    REGIONMGR2        4  ( As RegionMgr2 is belongs to DIvisionmgr2. But the ID of DIVISIONMGR2 is '4'from ID columns)
14    REGIONMGR2        4
15    REGIONMGR2        4
16    REGIONMGR3        7  ( As RegionMgr3 is belongs to DIvisionmgr3. But the ID of DIVISIONMGR3 is '7'from ID columns)
17    REGIONMGR3        7
18    REGIONMGR3        7
19    LOCALMGR1         10 ( As LOCALMGR1 belongs to REGIONMGR1 and ID of Regionmgr1 is '10' from ID columns)
20    LOCALMGR2         10
21    LOCALMGR3         10
22    LOCALMGR5         13 ( LOCALMGR5 belongs to REGIONMGR2 and ID of REGINMGR2 is '13' from ID column) the logic follows for all..
23    LOCALMGR4     
24    LOCALMGR7      
25    LOCALMGR5       
26    LOCALMGR6     
27    LOCALMGR3

....
...

 PARENT-->CHILDRENS
                 |
                 |
             GRANDCHILDREN

 
    5                                   3  
    |                                   |                                
    |                                   |
 ------------                     ------------------------
 |          |                     |                      |
 |          |                     |                      |  
 7          2                     1                      10                   
                                  |                       |
                                  |                       |
                                  6                   --------------
                                                      |      |     |
                                                      |      |     |
                                                      8      9     4

+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |   3 |
|  2 |   5 |
|  3 |   0 |
|  4 |   10 |
|  5 |   0 |
|  6 |   1 |
|  7 |   5 |
|  8 |   10 |
|  9 |   10 |
| 10 |   3 |
+----+-----------+

WITH DATA (NAME,id)
       AS (select '5',1  from dual union all
           select '3',2  from dual union all
           select '<>',3  from dual union all
           select '<>',4  from dual union all
           select '<>',5  from dual union all

          etc..

SQL> SELECT * FROM v$version;

ID            Name
-------------------
| a1       | 1  |
| b2       | 2  |
| c3       | 3  |
| a1.d4    | 4  |
| a1.e5    | 5  |
| a1.d4.f6 | 6  |
| a1.d4.g7 | 7  |
| a1.e5.h8 | 8  |
| a2.i9    | 9  |
| a2.i9.j10| 10 |
etc..
etc...

| Name     | ID | PARENT_ID |
-----------------------------
| a1       | 1  |  null      |
| b2       | 2  |  null      |
| c3       | 3  |  null      |
| d4       | 4  |  1         |
| e5       | 5  |  1         |
| f6       | 6  |  4         |
| g7       | 7  |  4         |
| h8       | 8  |  5         |
| i9       | 9  |  2         |
| j10      | 10 |  9         |
...
---

WITH DATA (NAME,id)
       AS (select 'a1',1  from dual union all
           select 'b2',2  from dual union all
           select 'c3',3  from dual union all
           select 'a1.d4',4  from dual union all
           select 'a1.e5',5  from dual union all
           select 'a1.d4.f6',6  from dual union all
           select 'a1.d4.g7',7  from dual union all
           select 'a1.e5.h8',8  from dual union all
           select 'a2.i9',9  from dual union all
           SELECT 'a2.i9.j10',10 FROM dual)
    ,relation AS (SELECT NAME, substr(NAME,instr(NAME,'.',-1)+1) stripped_name
                       , substr(name,instr(name,'.',-1,2) + 1, instr(name,'.',-1) - instr(name,'.',-1,2) - 1) AS PARENT_name
                       , id
                    FROM DATA)
SELECT CHILD.NAME
      ,CHILD.ID
      ,parent.id as parent_id
  FROM            relation CHILD
  LEFT OUTER JOIN relation PARENT ON (CHILD.parent_name = PARENT.stripped_name)
order by child.id;

WITH data (name,id) AS
 (SELECT 'a1',1            FROM dual UNION ALL
  SELECT 'a2',2            FROM dual UNION ALL
  SELECT 'c3',3            FROM dual UNION ALL
  SELECT 'a1.d4',4         FROM dual UNION ALL
  SELECT 'a1.e5',5         FROM dual UNION ALL
  SELECT 'a1.d4.f6',6      FROM dual UNION ALL
  SELECT 'a1.d4.g7',7      FROM dual UNION ALL
  SELECT 'a1.e5.h8',8      FROM dual UNION ALL
  SELECT 'a2.i9',9         FROM dual UNION ALL
  SELECT 'a2.i9.j10',10    FROM dual UNION ALL
  SELECT 'a1.e5.h8.k11',11 FROM dual 
  )
 ,relation AS 
 (SELECT name, substr(name,instr(name,'.',-1)+1) stripped_name,
         substr(name,instr(name,'.',-1,2) + 1, instr(name,'.',-1) - instr(name,'.',-1,2) - 1)  parent_name,
         id
    FROM data)  
  ,ancestors (aname, aid) AS 
 (SELECT name, id  
    FROM data
   WHERE INSTR(name,'.')=0),
 cte (       name, aname,   parent_name,        child_name,     id,   lv) AS
 (SELECT    aname, aname,           '-',             aname,    aid,    1
    FROM ancestors
   UNION ALL      
  SELECT rel.name, aname, ct.child_name, rel.stripped_name, rel.id, lv+1
    FROM relation rel
    JOIN cte ct ON (ct.child_name = rel.parent_name ))
SELECT * 
  FROM cte;

NAME   		ANAME   PARENT_NAME CHILD_NAME  ID 	LV
----------------------------------------------------------
a1		a1		-	a1	1	1
a2		a2		-	a2	2	1
c3		c3		-	c3	3	1
a1.e5		a1		a1	e5	5	2
a1.d4		a1		a1	d4	4	2
a2.i9		a2		a2	i9	9	2
a1.e5.h8	a1		e5	h8	8	3
a1.d4.g7	a1		d4	g7	7	3
a1.d4.f6	a1		d4	f6	6	3
a2.i9.j10	a2		i9	j10	10	3
a1.e5.h8.k11	a1		h8	k11	11	4

WITH data (name,id) AS
 (SELECT 'a1',1            FROM dual UNION ALL
  SELECT 'a2',2            FROM dual UNION ALL
  SELECT 'c3',3            FROM dual UNION ALL
  SELECT 'a1.d4',4         FROM dual UNION ALL
  SELECT 'a1.e5',5         FROM dual UNION ALL
  SELECT 'a1.d4.f6',6      FROM dual UNION ALL
  SELECT 'a1.d4.g7',7      FROM dual UNION ALL
  SELECT 'a1.e5.h8',8      FROM dual UNION ALL
  SELECT 'a2.i9',9         FROM dual UNION ALL
  SELECT 'a2.i9.j10',10    FROM dual UNION ALL
  SELECT 'a1.e5.h8.k11',11 FROM dual 
  )
,relation as ...

SELECT 'a1',1            FROM dual UNION ALL
  SELECT 'a2',2            FROM dual UNION ALL
  SELECT 'c3',3            FROM dual UNION ALL
  SELECT 'a1.d4',4         FROM dual UNION ALL
...

remaining "Relation as" logic I am able to understand.

SELECT 'a1',1            FROM dual UNION ALL
  SELECT 'a2',2            FROM dual UNION ALL
  SELECT 'c3',3            FROM dual UNION ALL
  SELECT 'a1.d4',4         FROM dual UNION ALL
select ...

(select row_number() over (order by id) rn,
        id,
        parent_id
   from data
  start with parent_id is null
connect by prior id = parent_id
)
select id name,
       rn id,
       (select rn from base where id = x.parent_id) "LEVEL"
  from base x
order by rn

select distinct 

DIV_NAME,
DIV_MGR,
REGI_NAME,
REGI_MGR,
TEAM_NAME,
TEAM_MGR,
PRIM_REPR,
fst_name,
last_name,
sp.name,
login,
count(*) over (partition by sc.row_id) dup_Positions

from CONTACT sc

left outer  join POSITION sp on sp.pr_emp_id=sc.row_id
where PRIM_REPR  is not null
or DIV_MGR is not null
or DIV_NAME is not null
or REGI_NAME is not null
or REGI_MGR is not null
or TEAM_NAME is not null
or TEAM_MGR is not null
order by 
DIV_NAME,
DIV_MGR,
REGI_NAME,
REGI_MGR,
TEAM_NAME,
TEAM_MGR,
PRIM_REPR,
last_name

                
                          MICHEL  ( Divison manager)  --- His Level is "0"
                            |
                            |
                         ==========
                         |         |
                       SCOTT       TIGER  ( Region Managers) ---> ( these two guys levels are "1")
                       |              |
                       |              |
                   ==========       SYSTEM   ( these 3 guys are team managers)
                   |        |
                  SYS       Admin  

ID  Employee              Division_Name  Division_MGR  Region_Mgr   Team_Mgr   Level
-------------------------------------------------------------------------------------
1   MICHEL                 Agency         MICHEL        SCOTT        SYS       0
2   SCOTT                  Agency         MICHEL        SCOTT       ADMIN      1
3   TIGER                  Agency         MICHEL        TIGER       SYSTEM     1 
4   SYS                    Agency         MICHEL        SCOTT       SYS        2      
5   ADMIN                  Agency         MICHEL        SCOTT       ADMIN      2
6   SYSTEM                 Agency         MICHEL        TIGER       SYSTEM     2

etc...