Apex Tree Menu

Apex Tree Menu  

select case when connect_by_isleaf = 1 then 0

            when level = 1             then 1

            else                           -1

       end as status, 

       level, 

       "FUNCTION_LABEL" as title, 

       null as icon, 

       "FUNCTIONID" as value, 

       null as tooltip, 

       'http://202.142.183.11:9292/ords/f?p=&APP_ID.:'||nvl(pagno,1)||':'||:APP_SESSION||':::::' as link

from ( 

select * from  V_MENU_FUNCTIONS  where FUNCTIONID in (select FUNCTIONID from v_user_groups_functions f where f.USER_ID = 1 )

union

select null , menu_name, menu_id , '0','' from  sgi_menus mn where mn.menu_id in ( 

select distinct function_menu_id 

from V_MENU_FUNCTIONS m  , ( select * from v_user_groups_functions f where f.USER_ID = 1 ) u where m.functionid = u.functionid)

union

select null , menu_name , 0 , null,'' from sgi_menus where menu_id='0'

)

start with function_menu_id is null

connect by prior "FUNCTIONID" = "FUNCTION_MENU_ID"

order siblings by "FUNCTION_LABEL"