通过并发程序名称，反查所在Menu

来源：互联网发布：2017年十大网络用语编辑：程序博客网时间：2024/05/16 02:47

通过并发程序名称，反查所在Menu

create table cux_report_upgrade_menu_values(program_name varchar2(200),
request_group_name varchar2(200),
v_function_name varchar2(200),
v_menu_name1 varchar2(200),
v_menu_name2 varchar2(200),
v_menu_name3 varchar2(200),
v_menu_name4 varchar2(200),
v_menu_name5 varchar2(200) );

create table cux_report_upgrade(name varchar2(200));

declare

v_parameters varchar2(200);
v_program_name varchar2(200);
v_request_group_name varchar2(200);
v_function_name varchar2(200);
v_menu_name1 varchar2(200);
v_menu_name2 varchar2(200);
v_menu_name3 varchar2(200);
v_menu_name4 varchar2(200);
v_menu_name5 varchar2(200);
v_menu_id1 number;
v_menu_id2 number;
v_menu_id3 number;
v_menu_id4 number;
v_menu_id5 number;
cursor c is
select name from cux_report_upgrade;
begin
for v_c in c loop
begin
select '%' || request_group_code || '%'
into v_parameters
from FND_CONCURRENT_PROGRAMS_VL fcpv,
FND_REQUEST_GROUPS frg,
FND_REQUEST_GROUP_UNITS frgu
where fcpv.concurrent_program_name = V_C.NAME
and fcpv.concurrent_program_id = frgu.request_unit_id
and frgu.request_group_id = frg.request_group_id
and rownum = 1;
exception
when no_data_found then
null;
end;
BEGIN
select fcpv.concurrent_program_name,
frg.request_group_name,
fffv.user_function_name,
fmv.menu_name
into v_program_name,
v_request_group_name,
v_function_name,
v_menu_name1
from FND_CONCURRENT_PROGRAMS_VL fcpv,
FND_REQUEST_GROUPS frg,
FND_REQUEST_GROUP_UNITS frgu,
FND_FORM_FUNCTIONS_VL fffv,
FND_MENUS_VL fmv,
FND_MENU_ENTRIES_VL fmev
where fcpv.concurrent_program_name = v_c.name
and fcpv.concurrent_program_id = frgu.request_unit_id
and frgu.request_group_id = frg.request_group_id
and fffv.parameters like V_PARAMETERS
and fffv.function_id = fmev.function_id
and fmev.menu_id = fmv.menu_id
and rownum = 1;
EXCEPTION
when no_data_found then
NULL;
END;
begin
select menu_id
into v_menu_id1
from fnd_menus_vl
where menu_name = v_menu_name1;
select menu_id
into v_menu_id2
from fnd_menu_entries_vl
where sub_menu_id = v_menu_id1
and rownum = 1;
exception
when no_data_found then
v_menu_id2 := 0;

end;
if (v_menu_id2 <> 0) then
select menu_name
into v_menu_name2
from fnd_menus_vl
where menu_id = v_menu_id2;
begin
select menu_id
into v_menu_id3
from fnd_menu_entries_vl
where sub_menu_id = v_menu_id2
and rownum = 1;
exception
when no_data_found then
v_menu_id3 := 0;

end;
if (v_menu_id3 <> 0) then
select menu_name
into v_menu_name3
from fnd_menus_vl
where menu_id = v_menu_id3;
begin
select menu_id
into v_menu_id4
from fnd_menu_entries_vl
where sub_menu_id = v_menu_id3;
exception
when no_data_found then
v_menu_id4 := 0;
end;
if (v_menu_id4 <> 0) then
select menu_name
into v_menu_name4
from fnd_menus_vl
where menu_id = v_menu_id4;
begin
select menu_id
into v_menu_id5
from fnd_menu_entries_vl
where sub_menu_id = v_menu_id4;
exception
when no_data_found then
v_menu_id5 := 0;
end;
if (v_menu_id5 <> 0) then
select menu_name
into v_menu_name5
from fnd_menus_vl
where menu_id = v_menu_id5;
end if;
end if;
end if;
end if;
insert into cux_report_upgrade_menu_values values
(v_program_name,
v_request_group_name,
v_function_name,
v_menu_name1,
v_menu_name2,
v_menu_name3,
v_menu_name4,
v_menu_name5);
end loop;
end;