Making the most of a Navigation Menu
This utility allows you to re-use a Navigation Menu as a list on subpages.
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.