Anton Nielsen
apex_debug

Follow

apex_debug

Follow
Making the most of a Navigation Menu

Making the most of a Navigation Menu

Anton Nielsen's photo
Anton Nielsen
·Dec 16, 2022·

2 min read

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;

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
    left outer join APEX_APPLICATION_BUILD_OPTIONS cbo -- child build option
      on (cbo.build_option_name = clist.build_option
        and cbo.application_id = plist.application_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 (
                p_build_option_id           => cbo.build_option_id,
                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.

 
Share this