DECLARE
l_menu_id NUMBER;
l_row_id VARCHAR2(20);
l_function_id NUMBER;
l_seq NUMBER;
l_prompt fnd_menu_entries_vl.prompt%TYPE;
l_description fnd_menu_entries_vl.description%TYPE;
l_fnc_cnt NUMBER := 0;
l_sm_cnt NUMBER := 0;
l_smy_cnt NUMBER := 0;
CURSOR c1 IS
SELECT
fm.user_menu_name||'-'|| fff.user_function_name menu_function_name,
fm.menu_id,
fme.function_id,
fme.entry_sequence,
fme.prompt,
fme.description
FROM
fnd_menus_vl fm,
fnd_menu_entries_vl fme,
fnd_form_functions_vl fff
WHERE
1 = 1
AND fm.menu_id = fme.menu_id
AND fme.function_id = fff.function_id
AND fm.user_menu_name||'-'|| fff.user_function_name IN (
'General Pref Menu-Manage Proxies Page',
'General Pref Menu-User Management - My Application Access'
);
CURSOR c2 IS
SELECT
fm.user_menu_name||'-'|| fm1.user_menu_name menu_submenu_name,
fm.menu_id,
fme.sub_menu_id,
fme.entry_sequence,
fme.prompt,
fme.description
FROM
fnd_menus_vl fm,
fnd_menus_vl fm1,
fnd_menu_entries_vl fme
WHERE
1 = 1
AND fm.menu_id = fme.menu_id
AND fme.sub_menu_id = fm1.menu_id
AND fm.user_menu_name||'-'|| fm1.user_menu_name IN (
'IEX: Collections Agent Responsibility Menu-TeleSales Collateral Menu',
'IEX: Collections Agent Responsibility Menu-TeleSales Contracts Tab Menu'
);
CURSOR c3 IS
SELECT
fm.user_menu_name||'-'|| fm1.user_menu_name menu_submenu_name,
fm.menu_id,
fme.sub_menu_id,
fme.entry_sequence,
fme.prompt,
fme.description
FROM
fnd_menus_vl fm,
fnd_menus_vl fm1,
fnd_menu_entries_vl fme
WHERE
1 = 1
AND fm.menu_id = fme.menu_id
AND fme.sub_menu_id = fm1.menu_id
AND fm.user_menu_name||'-'|| fm1.user_menu_name IN
( 'Setup:-iProcurement Administration: Home Page' );
BEGIN
dbms_output.put_line('Main Program start');
FOR i IN c1 LOOP
BEGIN
dbms_output.put_line('cursor start for menu_function:'|| i.menu_function_name);
fnd_menu_entries_pkg.update_row(
x_menu_id => i.menu_id,
x_entry_sequence => i.entry_sequence,
x_sub_menu_id => NULL,
x_function_id => i.function_id,
x_grant_flag => 'N', -- Grant Flag
x_prompt => i.prompt, -- Prompt
x_description => i.description,
x_last_update_date => sysdate,
x_last_updated_by => - 1,--fnd_global.user_id,
x_last_update_login => - 1--fnd_global.login_id
);
l_fnc_cnt := l_fnc_cnt + 1;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Inner Exception: ' || sqlerrm);
END;
COMMIT;
END LOOP;
dbms_output.put_line('totla function updated l_fnc_cnt:' || l_fnc_cnt);
FOR i IN c2 LOOP
BEGIN
dbms_output.put_line('cursor start for menu_submenu:'|| i.menu_submenu_name);
fnd_menu_entries_pkg.update_row(
x_menu_id => i.menu_id,
x_entry_sequence => i.entry_sequence,
x_sub_menu_id => i.sub_menu_id,
x_function_id => NULL,
x_grant_flag => 'N', -- Grant Flag
x_prompt => i.prompt, -- Prompt
x_description => i.description,
x_last_update_date => sysdate,
x_last_updated_by => - 1,--fnd_global.user_id,
x_last_update_login => - 1--fnd_global.login_id
);
l_sm_cnt := l_sm_cnt + 1;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Inner Exception: ' || sqlerrm);
END;
COMMIT;
END LOOP;
dbms_output.put_line('totla function updated l_sm_cnt:' || l_sm_cnt);
FOR i IN c3 LOOP
BEGIN
dbms_output.put_line('cursor start for Y menu_submenu:'|| i.menu_submenu_name);
fnd_menu_entries_pkg.update_row(
x_menu_id => i.menu_id,
x_entry_sequence => i.entry_sequence,
x_sub_menu_id => i.sub_menu_id,
x_function_id => NULL,
x_grant_flag => 'Y', -- Grant Flag
x_prompt => i.prompt, -- Prompt
x_description => i.description,
x_last_update_date => sysdate,
x_last_updated_by => - 1,--fnd_global.user_id,
x_last_update_login => - 1--fnd_global.login_id
);
l_smy_cnt := l_smy_cnt + 1;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Inner Exception: ' || sqlerrm);
END;
COMMIT;
END LOOP;
dbms_output.put_line('totla function updated l_smy_cnt:' || l_smy_cnt);
dbms_output.put_line('Main Program end');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Main Exception:' || sqlerrm);
END;
/
