Skip to main content

Command Palette

Search for a command to run...

Making the most of a Navigation Menu

This utility allows you to re-use a Navigation Menu as a list on subpages.

Updated
2 min read
Making the most of a Navigation Menu

Check out the details in the video here: https://www.youtube.com/watch?app=desktop&v=3qDzUmZoNOQ

1. You need a small package

create or replace package insum_menu_util is

--
--
-- simply a wrapper for apex_plugin_util.is_component_used
-- returning a Y or N instead of a boolean
function is_component_used_yn (
                p_build_option_id           IN NUMBER   DEFAULT NULL,
                p_authorization_scheme_id   IN VARCHAR2,
                p_condition_type            IN VARCHAR2,
                p_condition_expression1     IN VARCHAR2,
                p_condition_expression2     IN VARCHAR2,
                p_component                 IN VARCHAR2 DEFAULT NULL )
            return varchar2;

end insum_menu_util;
/
create or replace package body insum_menu_util is
--
--
function is_component_used_yn (
                p_build_option_id           IN NUMBER   DEFAULT NULL,
                p_authorization_scheme_id   IN VARCHAR2,
                p_condition_type            IN VARCHAR2,
                p_condition_expression1     IN VARCHAR2,
                p_condition_expression2     IN VARCHAR2,
                p_component                 IN VARCHAR2 DEFAULT NULL )
            return varchar2 is
begin
  return case when 
    apex_plugin_util.is_component_used (
    p_build_option_id           => p_build_option_id,
    p_authorization_scheme_id   => p_authorization_scheme_id,
    p_condition_type            => p_condition_type,
    p_condition_expression1     => p_condition_expression1,
    p_condition_expression2     => p_condition_expression2,
    p_component                 =>  p_component)
    then 'Y'
    else 'N'
    end;
end is_component_used_yn;

--
--
function is_component_used_yn_2 (
                p_build_option_name         IN VARCHAR2   DEFAULT NULL,
                p_authorization_scheme_id   IN VARCHAR2,
                p_condition_type            IN VARCHAR2,
                p_condition_expression1     IN VARCHAR2,
                p_condition_expression2     IN VARCHAR2,
                p_component                 IN VARCHAR2 DEFAULT NULL )
            return varchar2 is
l_build_option_id     number;
begin
  if p_build_option_name is not null then
    select build_option_id
      into l_build_option_id
      from apex_application_build_options  
      where application_id = v('APP_ID')
        and build_option_name = p_build_option_name; 
  end if;

  return case when 
    apex_plugin_util.is_component_used (
    p_build_option_id           => l_build_option_id,
    p_authorization_scheme_id   => p_authorization_scheme_id,
    p_condition_type            => p_condition_type,
    p_condition_expression1     => p_condition_expression1,
    p_condition_expression2     => p_condition_expression2,
    p_component                 =>  p_component)
    then 'Y'
    else 'N'
    end;
end is_component_used_yn_2;


end insum_menu_util;
/

2. And a dynamic list

I named the list "Navigation Menu Child Entries". If you watched APEX Instant Tips episode 95, I did find the multi-lingual solution and implemented it below.

select 1 the_level, 
       clist.entry_text  label,
       clist.ENTRY_TARGET   target,
       null is_current_list_entry,
       clist.ENTRY_IMAGE   image,
       clist.ENTRY_IMAGE_ATTRIBUTES   image_attribute,
       clist.ENTRY_IMAGE_ALT_ATTRIBUTE   image_alt_attribute,
       clist.ENTRY_ATTRIBUTE_01   attribute1,
       clist.ENTRY_ATTRIBUTE_02   attribute2,
       clist.ENTRY_ATTRIBUTE_03   attribute3,
       clist.ENTRY_ATTRIBUTE_04   attribute4,
       clist.ENTRY_ATTRIBUTE_05   attribute5,
       clist.ENTRY_ATTRIBUTE_06   attribute6,
       clist.ENTRY_ATTRIBUTE_07   attribute7,
       clist.ENTRY_ATTRIBUTE_08   attribute8,
       clist.ENTRY_ATTRIBUTE_09   attribute9,
       clist.ENTRY_ATTRIBUTE_10   attribute10
    from apex_application_list_entries plist 
    inner join apex_application_list_entries clist
      on clist.list_entry_parent_id = plist.list_entry_id   
    where 
          plist.application_id = :APP_TRANSLATION_ID
      and plist.list_name = 'Navigation Menu'
      and (
           plist.entry_target like  'f?p=&' || 'APP_ID' ||'.:' || :app_page_id || ':%'
        or plist.entry_target like  'f?p=' || :APP_ALIAS || ':' || :app_page_id || ':%'
        or plist.entry_target like  'f?p=' || lower(:APP_ALIAS)  || ':' || :app_page_id || ':%'
        or plist.entry_target like  'f?p=' || :APP_ID || ':' || :app_page_id || ':%'                 
        )    
      and insum_menu_util.is_component_used_yn_2 (
                p_build_option_name         => clist.build_option,
                p_authorization_scheme_id   => clist.authorization_scheme_id,
                p_condition_type            => clist.condition_type_code,
                p_condition_expression1     => clist.condition_expression1,
                p_condition_expression2     => clist.condition_expression2) = 'Y' 
    order by clist.display_sequence

Note: You may need to change this line

and plist.list_name = 'Navigation Menu'

replacing 'Navigation Menu' with the name of your menu.

H

It's very helpful.

N
Nat Asuah2y ago

Good day Antony and thanks for the good work. I have been doing apex for barely a month and your tips have been immeasurable to my progress so far. I have to implemented this tip but i don't know if i have missed something. I created the package successfully. Created a dynamic list. My navigation menu is 'navigation menu ". So no need to change anything in your code. Is not working at all. Could you give me few pointers as to how to mplement this tip? Thank you. Nat