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