select case when connect_by_isleaf = 1 then 0
            when level = 1             then 1
            else                           -1
       end as status, 
       level, 
       label||': '||name as title, 
       case when item_type = 'P' then 'fa-file-text-o'
            when item_type = 'S' then 'fa-caret-square-o-right'
            when item_type = 'T' then 'fa-minus-square-o'
       else null
       end as icon, 
       id as value, 
       case when tooltip is not null then name||' - '||tooltip||'% complete'
            else name
       end as tooltip,
       case when item_type = 'P' then 
               apex_util.prepare_url('f?p='||:app_id||':7:'||:app_session||':T:::P7_PROJ_ID:'||id, p_triggering_element => 'apex.jQuery("#tree")')
            when item_type = 'T' then
               apex_util.prepare_url('f?p='||:app_id||':9:'||:app_session||':T:::P9_PROJ_ID,P9_TASK_ID:'||link, p_triggering_element => 'apex.jQuery("#tree")')
            when item_type = 'S' then 
               apex_util.prepare_url('f?p='||:app_id||':10:'||:app_session||':T:::P10_PROJ_ID,P10_ROWID:'||link, p_triggering_element => 'apex.jQuery("#tree")')
       end as link 
 from (
select 'P' item_type,
       t.label label,
       to_char(a.PROJ_ID) id,
       null parent,
       a.project_name name,
       a.status tooltip,
       null link
  from eba_demo_tree_projects a, (select wwv_flow_lang.system_message('PROJECT') label from dual) t
 where upper(t.label||': '||a.project_name) like '%' || upper(:P2_SEARCH) || '%'
union all
select 'T' item_type,
       u.label label,
       to_char(b.proj_id)||'-'||to_char(b.task_id) id,
       to_char(b.proj_id) parent,
       b.task_name name,
       null tooltip,
       b.proj_id||','||b.task_id link
  from eba_demo_tree_task b, (select wwv_flow_lang.system_message('TASK') label from dual) u
union all
select 'S' item_type,
       v.label label,
       to_char(t.proj_id)||'-'||to_char(c.task_id)||'-'||to_char(c.sub_id) id,
       to_char(t.proj_id)||'-'||to_char(c.task_id) parent,
       c.sub_name name,
       null tooltip,
       c.proj_id||','||c.rowid link
  from eba_demo_tree_subtask c
  join eba_demo_tree_task t on c.task_id = t.task_id, (select wwv_flow_lang.system_message('SUBTASK') label from dual) v
union all
 select 'M'
      , 'level4'
      , '6-3-8-1'
      , '6-3-8'
      , 'to make sure it works'
      , null
      , null
   from dual
)
where :P2_TOGGLE_EMPTY = 1
start with parent is null
connect by prior id = parent
order siblings by name