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"

Custom Navigation Menu in Apex

 Custom Navigation Menu in Apex

select level, function_label label, 

case when pagno is null then

'javascript:void(null)'

else

'f?p=&APP_ID.:'||pagno||':'||:APP_SESSION||':::::'

end as  target,

function_label is_current

 from ( 

        select  function_menu_id, functionid,  function_label  , pagno , function_label is_current ,  null

               from    

        (

        select * from ( 

        select m.*  --, get_apex_page(m.FUNCTIONID) apex_doc_no 

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

        where m.functionid = u.functionid

        and m.function_menu_id is not null

        order by m.function_label

        )

        union

        select * from (

        select null , menu_name, menu_id , '0' ,   null nn  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 = :USER_ID ) u

        where m.functionid = u.functionid

        and m.function_menu_id is not null)

        order by menu_name

        )

        union 

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

        )--where function_menu_id is not null

    )      

        connect by  prior functionid = function_menu_id start with function_menu_id is null

        order siblings by function_label