spool menus_&app
  set echo on
  SET VERIFY OFF;

  prompt
  prompt This script will copy Release 10.5 menus registered
         under the
  prompt application you specify to a new, custom application so
  prompt as to be preserved during Release 10.7 upgrade.
  prompt
  accept Old_Application_Name char prompt 'Enter Old Application Name: '
  accept New_Application_Name char prompt 'Enter New Application Name: '
  prompt

  prompt
  prompt Creating new "&&New_Application_Name" application...

  select greatest( max(application_id)+1, 20000 )
  from fnd_application;
  col application_name format a35 trunc

  select application_id,application_name
  from fnd_application
  where application_name = '&&Old_Application_Name';

  select application_id,application_name
  from fnd_application
  where application_id>20000;

  accept Old_Appid number prompt 'Enter Old Application ID: '
  accept New_Appid number prompt 'Enter New Application ID: '
  REM Insert the new application row

  -- USER_ID 0 is APPLSYS
  -- abbr needs to be changed!!!
  insert into fnd_application (
  APPLICATION_ID,
  APPLICATION_SHORT_NAME,
  APPLICATION_NAME,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY,
  CREATION_DATE,
  CREATED_BY,
  LAST_UPDATE_LOGIN,
  APPLICATION_ABBREV,
  APPLICATION_PREFIX,
  DESCRIPTION,
  BASEPATH)
  select &&new_appid,
  'K'||APPLICATION_SHORT_NAME,
  '&&New_APPLICATION_NAME',
  SYSDATE, 0,
  SYSDATE, 0,
  0,
  APPLICATION_ABBREV,
 'APP',
 '10.5 Copy of '||APPLICATION_NAME,
  BASEPATH
  from fnd_application@devl
  where application_name = '&&Old_Application_Name'
  and not exists
  (select 1 from fnd_application
  where application_name = '&&New_Application_Name');

  commit;

  prompt
  prompt Copying all "&&Old_Application_Name" menus...

  insert into fnd_menu
  (APPLICATION_ID, MENU_ID, MENU_NAME,
  USER_MENU_NAME,
  LAST_UPDATE_DATE, LAST_UPDATED_BY,
  CREATION_DATE, CREATED_BY,
  LAST_UPDATE_LOGIN,
  ROOT_MENU,
  DESCRIPTION)
  select
  &&new_appid, MENU_ID, MENU_NAME,
  USER_MENU_NAME,
  LAST_UPDATE_DATE, LAST_UPDATED_BY,
  CREATION_DATE, CREATED_BY,
  LAST_UPDATE_LOGIN,
  ROOT_MENU,
  DESCRIPTION
  from fnd_menu@devl
  where application_id=&&old_appid;

  insert into fnd_menu_entry
  (APPLICATION_ID, MENU_ID, MENU_ENTRY_SEQUENCE,
  PROMPT,
  ACTION_TYPE_ID, ACTION_APPLICATION_ID,
  ACTION_ID,
  LAST_UPDATE_DATE, LAST_UPDATED_BY,
  CREATION_DATE, CREATED_BY,
  LAST_UPDATE_LOGIN,
  DESCRIPTION,
  ACTION_ARGUMENTS, FORM_NAME, FORM_ID)
  select
  &&new_appid, MENU_ID, MENU_ENTRY_SEQUENCE,
  PROMPT,
  ACTION_TYPE_ID, ACTION_APPLICATION_ID,
  ACTION_ID,
  LAST_UPDATE_DATE, LAST_UPDATED_BY,
  CREATION_DATE, CREATED_BY,
  LAST_UPDATE_LOGIN,
  DESCRIPTION,
  ACTION_ARGUMENTS, FORM_NAME, FORM_ID
  from fnd_menu_entry@devl
  where application_id=&&old_appid;

  commit;

  prompt
  prompt Updating applicable sub-menus to use
  prompt "&&New_Application_Name" menus...

  update fnd_menu_entry
  set action_application_id=&&new_appid
  where action_type_id='M'
  and action_application_id=&&old_appid;

  prompt
  prompt Updating applicable responsibilities to use
  prompt "&&New_Application_Name" menus...

  update fnd_responsibility
  set menu_application_id = &&new_appid
  where menu_application_id = &&old_appid
  and last_updated_by<>1;

commit;

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips