Prints the whole menu tree for a given responsibility

SET SERVEROUTPUT ON size 1000000
SET LINESIZE 20000
SPOOL c:/submenu_menu.txt

declare
-- this method prints the whole menu tree for a given responsibility
-- if you want to run for a different responsibility change the
-- l_resp name

l_resp_name varchar2(100) := 'NCR Call-1 Agent';
l_menu_id number;
l_main_menu varchar2(100);

procedure dfs(p_menu_id in varchar2, p_level in number) is

cursor get_submenus is
select e.entry_sequence, m.menu_name, m.user_menu_name, e.sub_menu_id, e.function_id, f.function_name, e.prompt, f.web_html_call, f.user_function_name, e.grant_flag
from fnd_menus_vl m, fnd_menu_entries_vl e, fnd_form_functions_vl f
where e.sub_menu_id = m.menu_id(+)
and e.function_id = f.function_id(+)
and e.menu_id = p_menu_id
order by 1;

l_spaces varchar2(30) := '';
granted varchar2(30);

begin
-- spacing
for i in 1..p_level loop
l_spaces := l_spaces || '..';
end loop;

--loop through menus
for c in get_submenus loop
granted := '';
if c.grant_flag = 'Y' then granted := ' [granted]'; end if;
if c.sub_menu_id is null then
dbms_output.put_line(l_spaces || 'FUNCTION ' || nvl(c.prompt, '[hidden]') || granted || ' ' || c.function_name || ' (' || c.user_function_name || ')');
-- dbms_output.put_line(l_spaces || '...........src=' || c.web_html_call);
else
dbms_output.put_line(l_spaces || 'MENU (' || p_level || ') ' || nvl(c.prompt, '[hidden]') || granted || ' ' || c.menu_name || ' (' || c.user_menu_name || ')');
end if;
dfs(c.sub_menu_id, p_level+1);
end loop;
end;
begin
select menu_id into l_menu_id
from fnd_responsibility_vl where responsibility_name = l_resp_name;
select menu_name into l_main_menu from fnd_menus where menu_id = l_menu_id;
dbms_output.put_line('MAIN MENU ' || l_main_menu);
dfs(l_menu_id, 1);
end;
spool off;