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;

Return to : Oracle Database, SQL, Application, Programming Tips