create or replace package body pos_fnd_menu_cm_extract as -- -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- 메뉴를 가져오는 함수, 이함수는 자기 호출 형태로 depth를 내려간다. -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> procedure get_menu ( p_menu_id in number ,p_depth in number ) is -- -- ============================================================================================= -- 내부변수를 선언하는 부분입니다. 내부변수는 항상 "l_" 로 시작해야 합니다. -- ============================================================================================= l_tab varchar2(1000); l_depth number(10); -- -- ============================================================================================= -- 메뉴를 가져오는 커서 -- ============================================================================================= cursor c_menu (c_menu_id in number) is select a.prompt ,a.menu_id ,a.entry_sequence ,a.sub_menu_id ,a.function_id ,b.web_html_call ,b.form_id ,b.description ,decode(decode(b.form_id,NULL,'N','F'),'N',decode(b.web_html_call,NULL,'N','W'),'F') menu_type from fnd_menu_entries_vl a ,fnd_form_functions_vl b where a.function_id = b.function_id (+) and a.prompt is not null and a.menu_id = c_menu_id order by a.entry_sequence; -- begin -- l_depth := p_depth; -- -- 앞에 빈칸 띄기 for i in 1..l_depth loop l_tab := l_tab || ' +-- '; end loop; -- -- ============================================================================================= -- 메뉴를 가져와서 메뉴가 Sub_Menu 이면, 자기 호출을 한다. -- ============================================================================================= for rec in c_menu(p_menu_id) loop --dbms_output.put_line(rec.prompt||', '||rec.description); -- if (rec.sub_menu_id is not null) and (rec.function_id is not null) then g_index := g_index + 1; g_resp_content(g_index).mf_type := 'Menu'; g_resp_content(g_index).menu_fn_id := rec.function_id; g_resp_content(g_index).name := l_tab || rec.prompt; g_resp_content(g_index).description := rec.description; g_resp_content(g_index).content_group := 'Function'; g_resp_content(g_index).content_type := rec.menu_type; --dbms_output.put_line('A:'||g_index||': '||g_resp_content(g_index).name||', '||g_resp_content(g_index).description||', '||g_resp_content(g_index).content_group||', '||g_resp_content(g_index).content_type); else -- if rec.function_id is not null then if (rec.web_html_call is not null) or (rec.form_id is not null) then g_index := g_index + 1; g_resp_content(g_index).mf_type := 'Menu'; g_resp_content(g_index).menu_fn_id := rec.function_id; g_resp_content(g_index).name := l_tab || rec.prompt; g_resp_content(g_index).description := rec.description; g_resp_content(g_index).content_group := 'Function'; g_resp_content(g_index).content_type := rec.menu_type; --dbms_output.put_line('B:'||g_index||': '||g_resp_content(g_index).name||', '||g_resp_content(g_index).description||', '||g_resp_content(g_index).content_group||', '||g_resp_content(g_index).content_type); end if; end if; -- if rec.sub_menu_id is not null then g_index := g_index + 1; g_resp_content(g_index).mf_type := 'Menu'; g_resp_content(g_index).menu_fn_id := rec.sub_menu_id; g_resp_content(g_index).name := l_tab || rec.prompt; g_resp_content(g_index).description := rec.description; g_resp_content(g_index).content_group := 'Menu'; g_resp_content(g_index).content_type := 'M'; --dbms_output.put_line('C:'||g_index||' : sub_menu_id => '||rec.sub_menu_id||': '||g_resp_content(g_index).name||', '||g_resp_content(g_index).description||', '||g_resp_content(g_index).content_group||', '||g_resp_content(g_index).content_type); -- pos_fnd_menu_cm_extract.get_menu (p_menu_id => rec.sub_menu_id, p_depth => l_depth+1); end if; -- end if; -- if (rec.sub_menu_id is not null) and (rec.function_id is not null) then -- end loop; -- for rec in c_menu(p_menu_id) loop -- exception -- when others then -- dbms_output.put_line('<<<<<<<<<<<<<<<<<<<<<< user error messages >>>>>>>>>>>>>>>>>>>>>'); -- dbms_output.put_line(sqlerrm); -- dbms_output.put_line('<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>'); fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<< user error messages >>>>>>>>>>>>>>>>>>>>>'); fnd_file.put_line(fnd_file.log, sqlerrm); fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>'); raise_application_error(-21000, 'pos_fnd_menu_cm_extract.get_menu() raise above error.'); -- end get_menu; -- -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Concurrent Program을 가져오는 함수 -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> procedure get_cp ( p_request_group_id in number ) is -- -- ============================================================================================= -- 내부변수를 선언하는 부분입니다. 내부변수는 항상 "l_" 로 시작해야 합니다. -- ============================================================================================= l_name varchar2(1000); l_description varchar2(1000); l_content_type varchar2(10); l_context varchar2(30); l_exe_method_code_desc varchar2(100); l_application_short_name varchar2(20); -- NOT_EXISTS exception; -- -- ============================================================================================= -- Request Group을 찾아오는 커서 -- ============================================================================================= cursor c_request (c_request_group_id in number) is select a.request_unit_type ,decode(a.request_unit_type, 'P','Program','A','Application','S','Set') request_unit_type_desc ,a.unit_application_id ,a.request_unit_id from fnd_request_group_units a where a.request_group_id = c_request_group_id; -- -- ============================================================================================= -- Concurrent Program을 찾아 오는 커서 -- 여기서 execution_method_code는 'CP_EXECUTION_METHOD_CODE' Lookup을 참조하라. -- ============================================================================================= cursor c_program (c_concurrent_program_id in number) is select a.user_concurrent_program_name ,a.description ,b.execution_method_code ,decode(b.execution_method_code,'A','Spawned', 'B','Request Set Stage Function' ,'E','Perl Concurrent Program', 'F','FlexSql' ,'H','Host' , 'I','PL/SQL Stored Procedure' ,'J','Java Stored Procedure' , 'K','Java Concurrent Program' ,'L','SQL*Loader' , 'M','Multi Language Function' ,'P','Oracle Reports' , 'Q','SQL*Plus' ,'R','SQL*Report' , 'S','Immediate' ,'X','FlexRpt' , 'Z','Shutdown Callback ' ,'UnKnown') exe_method_code_desc from fnd_concurrent_programs_vl a ,fnd_executables b where a.executable_id = b.executable_id and a.executable_application_id = b.application_id and a.concurrent_program_id = c_concurrent_program_id; -- -- ============================================================================================= -- Request Group의 Type이 Application이면, 하위의 Concurrent Program을 모두 찾아온다. -- ============================================================================================= cursor c_application_name (c_application_id in number) is select a.application_name, a.description, a.application_short_name from fnd_application_vl a where a.application_id = c_application_id; -- cursor c_application_pgm (c_application_id in number) is select a.user_concurrent_program_name ,a.description ,b.execution_method_code from fnd_concurrent_programs_vl a ,fnd_executables b where a.executable_id = b.executable_id and a.executable_application_id = b.application_id and a.application_id = c_application_id; -- -- ============================================================================================= -- Request Group의 Type이 Request Set이면, Set의 하위 Concurrent Program을 모두 찾아온다. -- ============================================================================================= cursor c_request_set_name (c_request_set_id in number) is select a.user_request_set_name, a.description from fnd_request_sets_vl a where a.request_set_id = c_request_set_id; -- cursor c_request_set_pgm (c_request_set_id in number) is select b.concurrent_program_id from fnd_request_sets_vl a ,fnd_request_set_programs b where a.request_set_id = c_request_set_id and a.request_set_id = b.request_set_id order by b.request_set_stage_id, b.sequence; -- begin -- -- ============================================================================================= -- 해당 Responsibility가 가지고 있는 Concurrent Program을 모두 찾아 온다. -- ============================================================================================= for rec in c_request(p_request_group_id) loop -- -- ------------------------------------------------------------------------------------------- -- type이 'P' 이면 Concurrent Program을 의미한다. -- ------------------------------------------------------------------------------------------- if rec.request_unit_type = 'P' then -- open c_program(rec.request_unit_id); fetch c_program into l_name, l_description, l_content_type, l_exe_method_code_desc; if c_program%FOUND then -- l_context := 'Program'; -- Log에서만 사용 g_index := g_index + 1; -- g_conc_content(g_index).mf_type := 'Concurrent'; g_conc_content(g_index).name := l_name; g_conc_content(g_index).description := l_description; g_conc_content(g_index).content_group := 'Concurrent'; g_conc_content(g_index).content_type := l_content_type; -- else -- l_context := 'Program'; -- Log에서만 사용 g_index := g_index + 1; -- g_conc_content(g_index).mf_type := 'Concurrent'; g_conc_content(g_index).name := rec.request_unit_type_desc; g_conc_content(g_index).description := 'unit_application_id -> '||to_char(rec.unit_application_id)||', request_unit_id -> '||to_char(rec.request_unit_id); g_conc_content(g_index).content_group := 'Concurrent'; g_conc_content(g_index).content_type := rec.request_unit_type; -- end if; close c_program; -- end if; -- if rec.request_unit_type = 'P' then -- -- ------------------------------------------------------------------------------------------- -- type이 'A' 이면 Application을 의미하므로 , 하위의 Concurrent Program을 모두 찾아온다. -- ------------------------------------------------------------------------------------------- if rec.request_unit_type = 'A' then -- open c_application_name(rec.request_unit_id); fetch c_application_name into l_name, l_description, l_application_short_name; if c_application_name%FOUND then -- l_context := 'Application'; -- Log에서만 사용 g_index := g_index + 1; -- g_conc_content(g_index).mf_type := 'Concurrent'; g_conc_content(g_index).name := l_name; g_conc_content(g_index).description := l_description; g_conc_content(g_index).content_group := 'Concurrent'; g_conc_content(g_index).content_type := rec.request_unit_type_desc; -- for app_rec in c_application_pgm(rec.request_unit_id) loop -- l_context := 'Program'; -- Log에서만 사용 g_index := g_index + 1; -- g_conc_content(g_index).mf_type := 'Concurrent'; g_conc_content(g_index).name := ' +-- '||app_rec.user_concurrent_program_name; g_conc_content(g_index).description := app_rec.description; g_conc_content(g_index).content_group := 'Concurrent'; g_conc_content(g_index).content_type := app_rec.execution_method_code; -- end loop; -- else -- 못찾으면 사실은 에러이다. -- l_context := 'Application'; -- Log에서만 사용 g_index := g_index + 1; -- g_conc_content(g_index).mf_type := 'Concurrent'; g_conc_content(g_index).name := rec.request_unit_type_desc; g_conc_content(g_index).description := 'unit_application_id -> '||to_char(rec.unit_application_id)||', request_unit_id -> '||to_char(rec.request_unit_id); g_conc_content(g_index).content_group := 'Concurrent'; g_conc_content(g_index).content_type := 'N/A'; -- end if; close c_application_name; -- end if; -- if rec.request_unit_type = 'A' then -- -- ------------------------------------------------------------------------------------------- -- type이 'S' 이면 Request Set을 의미하므로 , 하위의 Concurrent Program을 모두 찾아온다. -- ------------------------------------------------------------------------------------------- if rec.request_unit_type = 'S' then -- open c_request_set_name(rec.request_unit_id); fetch c_request_set_name into l_name, l_description; if c_request_set_name%FOUND then -- l_context := 'Request Set'; -- Log에서만 사용 g_index := g_index + 1; -- g_conc_content(g_index).mf_type := 'Concurrent'; g_conc_content(g_index).name := l_name; g_conc_content(g_index).description := l_description; g_conc_content(g_index).content_group := 'Concurrent'; g_conc_content(g_index).content_type := rec.request_unit_type_desc; -- for app_rec in c_request_set_pgm(rec.request_unit_id) loop -- l_context := 'Program'; -- Log에서만 사용 g_index := g_index + 1; -- open c_program(app_rec.concurrent_program_id); fetch c_program into l_name, l_description, l_content_type, l_exe_method_code_desc; if c_program%NOTFOUND then raise NOT_EXISTS; end if; close c_program; -- g_conc_content(g_index).mf_type := 'Concurrent'; g_conc_content(g_index).name := ' +-- '||l_name; g_conc_content(g_index).description := l_description; g_conc_content(g_index).content_group := 'Concurrent'; g_conc_content(g_index).content_type := l_content_type; -- end loop; -- else -- 못찾으면 사실은 에러이다. -- l_context := 'Request Set'; -- Log에서만 사용 g_index := g_index + 1; -- g_conc_content(g_index).mf_type := 'Concurrent'; g_conc_content(g_index).name := rec.request_unit_type_desc; g_conc_content(g_index).description := 'unit_application_id -> '||to_char(rec.unit_application_id)||', request_unit_id -> '||to_char(rec.request_unit_id); g_conc_content(g_index).content_group := 'Concurrent'; g_conc_content(g_index).content_type := 'N/A'; -- end if; -- if c_request_set_name%FOUND then close c_request_set_name; -- end if; -- if rec.request_unit_type = 'S' then -- end loop; -- for rec in c_request(p_request_group_id) loop -- exception -- when NOT_EXISTS then fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<< user error messages >>>>>>>>>>>>>>>>>>>>>'); fnd_file.put_line(fnd_file.log, l_context||' ID does not exist in Request Group...'); fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>'); fnd_file.put_line(fnd_file.log, sqlerrm); raise_application_error(-21201, 'pos_fnd_menu_cm_extract.get_cp() raise RES_NOT_EXISTS exception.'); -- when others then fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<< user error messages >>>>>>>>>>>>>>>>>>>>>'); fnd_file.put_line(fnd_file.log, sqlerrm); fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>'); raise_application_error(-22000, 'pos_fnd_menu_cm_extract.get_cp() raise above error.'); -- end get_cp; -- -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- 메인 함수 -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> procedure main ( errbuf out varchar2 ,retcode out varchar2 ,p_responsibility_id in number ) is -- -- ============================================================================================= -- Define Local Variable -- ============================================================================================= l_menu_id number(15); l_request_group_id number(15); l_mf_type varchar2(100); l_menu_fn_id number(15); l_name varchar2(1000); l_description varchar2(1000); l_content_group varchar2(100); l_content_type varchar2(100); -- RES_NOT_EXISTS exception; -- -- ============================================================================================= -- 해당 Reponsibility의 menu와 Request Group을 가져오는 커서 -- ============================================================================================= cursor c_get (c_responsibility_id in number) is select a.menu_id, a.request_group_id from fnd_responsibility_vl a where a.responsibility_id = c_responsibility_id; -- begin -- g_resp_content.delete; g_conc_content.delete; -- ------------------------------------------------------------------------------------------- -- Menu와 Request Group을 가져온다. -- ------------------------------------------------------------------------------------------- open c_get(p_responsibility_id); fetch c_get into l_menu_id, l_request_group_id; if c_get%NOTFOUND then raise RES_NOT_EXISTS; end if; close c_get; -- -- ------------------------------------------------------------------------------------------- -- Menu Generation -- ------------------------------------------------------------------------------------------- g_index := 0; get_menu(p_menu_id => l_menu_id, p_depth => 0); -- dbms_output.put_line('g_resp_content -> '||g_resp_content.count); for i in 1..g_resp_content.count loop if g_resp_content(i).mf_type is not null then -- dbms_output.put_line(i||chr(9)||g_resp_content(i).name||chr(9)||g_resp_content(i).description||chr(9)||g_resp_content(i).content_group||chr(9)||g_resp_content(i).content_type); -- fnd_file.put_line(fnd_file.output, i||chr(9)||g_resp_content(i).name||chr(9)||g_resp_content(i).description||chr(9) -- ||g_resp_content(i).content_group||chr(9)||g_resp_content(i).content_type); l_mf_type := substr(g_resp_content(i).mf_type,1,100); l_menu_fn_id := g_resp_content(i).menu_fn_id; l_name := substr(g_resp_content(i).name,1,1000); l_description := substr(g_resp_content(i).description,1,1000); l_content_group := substr(g_resp_content(i).content_group,1,100); l_content_type := substr(g_resp_content(i).content_type,1,100); insert into b1_temp (resp_id, mf_type, main_menu_id, seq, menu_fn_id, name, description, content_group, content_type) values (p_responsibility_id, l_mf_type, l_menu_id, i, l_menu_fn_id, l_name, l_description, l_content_group, l_content_type); end if; end loop; -- -- ------------------------------------------------------------------------------------------- -- Request Group Generation -- ------------------------------------------------------------------------------------------- if l_request_group_id is not null then g_index := 0; get_cp(p_request_group_id => l_request_group_id); -- dbms_output.put_line('g_conc_content -> '||g_conc_content.count); for i in 1..g_conc_content.count loop if g_conc_content(i).mf_type is not null then -- dbms_output.put_line(i||chr(9)||g_conc_content(i).name||chr(9)||g_conc_content(i).description||chr(9)||g_conc_content(i).content_group||chr(9)||g_conc_content(i).content_type); -- fnd_file.put_line(fnd_file.output, i||chr(9)||g_conc_content(i).name||chr(9)||g_conc_content(i).description||chr(9) -- ||g_conc_content(i).content_group||chr(9)||g_conc_content(i).content_type); l_mf_type := substr(g_conc_content(i).mf_type,1,100); l_menu_fn_id := g_conc_content(i).menu_fn_id; l_name := substr(g_conc_content(i).name,1,1000); l_description := substr(g_conc_content(i).description,1,1000); l_content_group := substr(g_conc_content(i).content_group,1,100); l_content_type := substr(g_conc_content(i).content_type,1,100); insert into b1_temp (resp_id, mf_type, main_menu_id, seq, menu_fn_id, name, description, content_group, content_type) values (p_responsibility_id, l_mf_type, l_request_group_id, i, l_menu_fn_id, l_name, l_description, l_content_group, l_content_type); -- insert into b1_temp (mf_type, main_menu_id, seq, menu_fn_id, name, description, content_group, content_type) -- values (g_conc_content(i).mf_type, l_request_group_id, i, g_conc_content(i).menu_fn_id, g_conc_content(i).name, -- g_conc_content(i).description, g_conc_content(i).content_group, g_conc_content(i).content_type); end if; end loop; end if; -- commit; -- exception -- when RES_NOT_EXISTS then errbuf := sqlerrm; retcode := sqlcode; fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<< user error messages >>>>>>>>>>>>>>>>>>>>>'); fnd_file.put_line(fnd_file.log, sqlerrm); fnd_file.put_line(fnd_file.log, 'input id (responsibility_id does not exists in fnd_responsibility table. check please...'); fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>'); raise_application_error(-21101, 'pos_fnd_menu_cm_extract.main() raise RES_NOT_EXISTS exception.'); -- when others then errbuf := sqlerrm; retcode := sqlcode; fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<< user error messages >>>>>>>>>>>>>>>>>>>>>'); fnd_file.put_line(fnd_file.log, sqlerrm); fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>'); raise_application_error(-21100, 'pos_fnd_menu_cm_extract.main() raise above error.'); -- end main; -- -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Menu Generation -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> procedure run_menu ( p_menu_id in number ) is -- begin -- g_resp_content.delete; g_index := 0; get_menu(p_menu_id => p_menu_id, p_depth => 0); -- for i in 1..g_resp_content.count loop insert into b1_temp (mf_type, main_menu_id, seq, menu_fn_id, name, description, content_group, content_type) values (g_resp_content(i).mf_type, p_menu_id, i, g_resp_content(i).menu_fn_id, g_resp_content(i).name, g_resp_content(i).description, g_resp_content(i).content_group, g_resp_content(i).content_type); --dbms_output.put_line(i||', '||g_resp_content(i).name||', '||g_resp_content(i).description||', '||g_resp_content(i).content_group||', '||g_resp_content(i).content_type); end loop; -- exception -- when others then dbms_output.put_line('<<<<<<<<<<<<<<<<<<<<<< user error messages >>>>>>>>>>>>>>>>>>>>>'); dbms_output.put_line(sqlerrm); dbms_output.put_line('<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>'); dbms_output.put_line('pos_fnd_menu_cm_extract.run_menu() raise above error.'); -- end run_menu; -- end pos_fnd_menu_cm_extract;