平凡的幸福(備站）

--選單樹狀結構
Declare

v_check number :=0;

v_space varchar2(200) :='';

i number :=0;

v_top_responsibility varchar2(50) := <權限模組名稱> ;

cursor cur1 is
select a.menu_id,b.sub_menu_id,b.prompt
from fnd_menus_vl a,FND_MENU_ENTRIES_VL b
where a.menu_id=b.menu_id
and a.menu_name='FM_NAVIGATE_10G'
and B.sub_MENU_ID not in (select action_id from FND_RESP_FUNCTIONS a,
fnd_application_vl b,
fnd_responsibility_vl c
where a.application_id=b.application_id
and c.responsibility_id=a.responsibility_id
and c.responsibility_key = v_top_responsibility
and rule_type='M') ;

r1 cur1%rowtype;

Procedure Get_function (v_level number,v_sub_menu number ) is --傳入層級，子選單ID

Cursor Cur2 is
select sub_menu_id,prompt
from FND_MENU_ENTRIES_VL where menu_id=v_sub_menu
and function_id not in (select action_id from FND_RESP_FUNCTIONS a,
fnd_application_vl b,
fnd_responsibility_vl c
where a.application_id=b.application_id
and c.responsibility_id=a.responsibility_id
and c.responsibility_key =v_top_responsibility
and rule_type='F') ;

r2 cur2%rowtype;

Begin

--dbms_output.put_line('aaaa');

v_space :='';

for i in 0..v_level
loop
v_space :=' '||v_space ;
end loop;

For r2 in Cur2 Loop

select count(1) into v_check
from FND_MENU_ENTRIES_VL where sub_menu_id is not null and menu_id=v_sub_menu
and function_id not in (select action_id from FND_RESP_FUNCTIONS a,
fnd_application_vl b,
fnd_responsibility_vl c
where a.application_id=b.application_id
and c.responsibility_id=a.responsibility_id
and c.responsibility_key = v_top_responsibility
and rule_type='F');


If v_check > 0 then

insert into tmp_ddd(a,b) values (v_level,v_space|| r2.prompt);
Get_function (v_level+1,r2.sub_menu_id);

else

insert into tmp_ddd(a,b) values (v_level,v_space || r2.prompt);
--dbms_output.put_line('Top Item_Number:' ||r2.prompt);

end if;

End Loop;

End;

Begin

for r1 in cur1 loop
insert into tmp_ddd(a,b) values (0,r1.prompt);
Get_function (1,r1.sub_menu_id);
end loop;

commit;

End;