create table test3 (id Number, parent_id Number,effective_From date,effective_to date);

Insert into test3 values(1,2,'01-Apr-2016','31-May-2016');
Insert into test3 values(3,1,'01-Jan-2016','31-Mar-2016');
Insert into test3 values(2,1,'01-Jan-2016','31-Mar-2016');
Insert into test3 values(2,3,'01-Apr-2016','31-May-2016');
Insert into test3 values(5,2,'01-Jun-2016','30-Dec-2016');
Insert into test3 values(6,5,'01-Aug-2016','30-Sep-2016');
Insert into test3 values(7,6,'01-oct-2016','30-nov-2016');

with
     lower_level1 as (
       select parent_id, effective_From from test3
       where parent_id = :id
         and to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
             between effective_From and effective_to
       ),
     lower_level as (
     Select * From lower_level1
     Union
       select parent_id, effective_From from test3
       where parent_id = :id
         and effective_to =(Select max(effective_to) 
                              from test3  
                             where parent_id = :id
                               and effective_to < to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH') )
         and 0 = (select count(1) from lower_level1)             
       ),       
     data as (
       select distinct id, parent_id, level lvl,effective_From , effective_to
       from test3
      Where to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
                      between effective_From and effective_to       
       connect by     prior parent_id = id
                  and to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
                      between effective_From and effective_to
       start with     (parent_id, effective_From) in (select * from lower_level)
                   or (id, effective_From) in
                      (select id, effective_From from test3
                       where id = :id
                         and to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
                             between effective_From and effective_to
                         and not exists (select null from lower_level))
     ), 
     lmax as (select max(lvl) lmax from data)
   select id, parent_id, lmax-lvl+2 "LEVEL",effective_From , effective_to
   from data,lmax
   union all
   select parent_id, parent_id, 1,effective_From , effective_to
   from data
   where lvl = (select lmax from lmax)
   order by 3, 1