set oracle_home=C:OracleMiddlewareOracle_FRHome1
set forms_path=Q:fmb11g;P:fmw11olb;P:fmw11tpl;P:fmw11pll
set path=C:OracleMiddlewareOracle_FRHome1bin;C:OracleMiddlewareOracle_FRHome1forms;%path%
set tns_admin=y:
SET NLS_LENGTH_SEMANTICS=CHAR
SET NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
REM Convert the MMb to XML
call frmf2xml OVERWRITE=YES [your_generation_destination][your_menu_name].mmb
REM Move the xml to a destination which is accessible with utl_file
del [your_utl_file_directory][your_menu_name]_orig_mmb.xml
move [your_generation_destination][your_menu_name]_mmb.xml [your_utl_file_directory]your_menu_name]_orig_mmb.xml
C:OracleMiddlewareOracle_FRHome1binsqlplus username/pw@db @[your_script_location]modifymenuXml.sql
move P:Issue_Tracker[your_menu_name]_mmb.xml [your_generation_destination][your_menu_name]_mmb.xml
call frmxml2f OVERWRITE=YES PRINTTREE=YES USERID=username/pw@db [your_generation_destination][your_menu_name]_mmb.xml
SET NLS_LENGTH_SEMANTICS=CHAR
SET NLS_LANG=AMERICAN_AMERICA.AL32UTF8
C:OracleMiddlewareOracle_FRHome1binFRMCMP module_type=MENU batch=YES window_state=MINIMIZE %1=%2 %3=%4 %5=%6
SET HEADING OFF
SET PAGESIZE 500
SET LINESIZE 240
SET FEEDBACK OFF
SET VERIFY OFF
SET DEFINE OFF
SET SERVEROUTPUT ON SIZE 1000000
REM create table temp_convert_menu
REM ( string varchar2(2000)
REM , line number);
REM create table temp_convert_menu_doubles
REM ( string varchar2(30)
REM , CONSTRAINT "menu_doubles_PK" PRIMARY KEY ("STRING"));
SPOOL P:Issue_Trackeradaptxml.txt
DECLARE
v_file_handle_in UTL_FILE.FILE_TYPE;
v_file_handle_out UTL_FILE.FILE_TYPE;
v_line VARCHAR2(2000);
v_new_line VARCHAR2(2000);
v_name VARCHAR2(2000);
v_name_start_pos number;
v_name_end_pos number;
v_index VARCHAR2(2000);
v_index_start_pos number;
v_index_end_pos number;
v_counter number := 0;
v_linenumber number := 0;
l_within_sdp000m boolean := false;
l_within_window_help boolean := false;
l_within_admin boolean := false;
l_sdpapp_written boolean := false;
l_ofgnavl65_written boolean := false;
v_loopcounter number := 0;
BEGIN
-- We use a temporary table to store the lines
delete from temp_convert_menu;
delete from temp_convert_menu_doubles;
DBMS_OUTPUT.ENABLE(1000000);
v_file_handle_in := UTL_FILE.Fopen('[your_dba_directory]','[your_menu_name]_orig_mmb.xml','R',32767);
v_file_handle_out := UTL_FILE.Fopen('[your_dba_directory]','[your_menu_name]_mmb.xml','W',max_linesize => 32767);
-- Loop through all line encountered in the original
LOOP
UTL_FILE.get_line(v_file_handle_in, v_line);
IF instr(v_line,'<Menu Name="SDP000M"') > 0 THEN
l_within_sdp000m := true;
END IF;
IF instr(v_line,'<MenuItem Name="WINDOW"')> 0
OR instr(v_line ,'<MenuItem Name="HELP"') > 0 THEN
l_within_window_help := true;
END IF;
IF instr(v_line,'<MenuItem Name="ADMINISTRATION"') > 0 THEN
l_within_admin := true;
END IF;
IF v_line like '%</Menu>%' THEN
delete from temp_convert_menu_doubles;
END IF;
v_name_start_pos := instr(v_line,'"',1,1);
v_name_end_pos := instr(v_line,'"',1,2);
v_name := substr(v_line,v_name_start_pos+1,v_name_end_pos-v_name_start_pos-1);
v_name := substr(v_name,1,30);
IF v_name is not null
and v_line like '%<MenuItem Name=%'
and v_name not like 'SEPARATOR%'
and instr(v_name,'______') = 0 THEN
BEGIN
insert into temp_convert_menu_doubles(string) values (v_name);
EXCEPTION
when dup_val_on_index THEN
v_loopcounter := v_loopcounter + 1;
v_name := substr(v_name,1,25)||lpad(v_loopcounter,5,'0');
insert into temp_convert_menu_doubles(string) values (v_name);
END;
END IF;
v_new_line := substr(v_line,1,v_name_start_pos)||v_name||substr(v_line,v_name_end_pos,2000);
IF l_sdpapp_written and instr(v_line,'LibraryLocation="sdpapp.pll"') > 0 THEN
null;
ELSIF l_ofgnavl65_written and instr(v_line,'LibraryLocation="ofgnavl65.pll"') > 0 THEN
null;
ELSIF not l_within_window_help THEN
UTL_FILE.PUT_line(v_file_handle_out, v_new_line);
ELSE
v_linenumber := v_linenumber + 1;
insert into temp_convert_menu(string, line)
values
(v_new_line, v_linenumber);
END IF;
IF l_within_window_help and instr(v_line,'</MenuItem>') > 0 THEN
l_within_window_help := false;
END IF;
IF instr(v_line,'LibraryLocation="sdpapp.pll"') > 0 THEN
l_sdpapp_written := true;
END IF;
IF instr(v_line,'LibraryLocation="ofgnavl65.pll"') > 0 THEN
l_ofgnavl65_written := true;
END IF;
IF l_within_admin and instr(v_line,'</MenuItem>') > 0 THEN
l_within_admin := false;
l_within_sdp000m := false;
FOR REC_LOOP in ( SELECT string as text_string
FROM temp_convert_menu
order by line)
LOOP
UTL_FILE.PUT_line(v_file_handle_out, rec_loop.text_string);
END LOOP;
END IF;
IF v_line like '%</Module>%' THEN
UTL_FILE.FCLOSE(v_file_handle_in);
exit;
END IF;
END LOOP;
--
UTL_FILE.FCLOSE(v_file_handle_out);
commit;
END;
/
SPOOL OFF
exit
