Creating Custom Menus and inserting to base tables through API

Step1:  Create custom menus in source instance. Use the below API’s to move the menus and menu entries to the target instance. 

Step2: The custom menus can be passed to the below API’s to insert data to Oracle standard tables fnd_menus,fnd_menus_tl  and fnd_menu_entries                           
Ø fnd_menus_pkg.insert_row 
Ø fnd_menu_entries_pkg.insert_row

Step3:  Sample script with description to upload the Menus and menu entries are explained below
             
 Menus
 ———–
DECLARE
   l_new_menu_id       NUMBER;
   l_exist_menu_flag   NUMBER;
   v_rowid             VARCHAR2 (20);
BEGIN
   SELECT MAX (menu_id)
     INTO l_new_menu_id
     FROM apps.fnd_menus_vl
    WHERE menu_name = ‘XX_TEST_MENU’;
   DBMS_OUTPUT.put_line (‘Before IF ‘);
   IF (l_new_menu_id IS NOT NULL)
   THEN
      l_exist_menu_flag := 1;                          — menu already exists
      DBMS_OUTPUT.put_line (‘Menu already FOUND, Duplicates not allowed’);
   ELSE
      –create a new menu
      l_new_menu_id := apps.fnd_menus_s.NEXTVAL;
      DBMS_OUTPUT.put_line (‘Before calling Standard API ‘);
      apps.fnd_menus_pkg.insert_row
                               (x_rowid                  => v_rowid,
                                x_menu_id                => l_new_menu_id,
                                x_menu_name              => ‘XX_TEST_MENU’,
                                x_user_menu_name         => ‘XX_TEST_MENU’,
                                x_menu_type              => ‘Standard’,
                                x_description            => ‘Menu Created from Backend’,
                                x_creation_date          => SYSDATE,
                                x_created_by            => fnd_global.user_id,                                                                            x_last_update_date       => SYSDATE,
                                x_last_updated_by        => fnd_global.user_id,  –userid
                                x_last_update_login      => fnd_global.login_id,   
                               );
      COMMIT;
   END IF;
END;
   
Menu Entries
——————
DECLARE
   l_menu_enttry_seq   NUMBER;
   l_row_id            VARCHAR2 (20);
   l_function_id       NUMBER;
   l_seq               NUMBER;
BEGIN
   SELECT NVL (MAX (entry_sequence), 1)
     INTO l_seq
     FROM apps.fnd_menu_entries
    WHERE menu_id = 1026906;
   SELECT function_id
     INTO l_function_id
     FROM apps.fnd_form_functions
    WHERE function_name = ‘XXEMP’;
   BEGIN
      fnd_menu_entries_pkg.insert_row
                                  (x_rowid                  => l_row_id,
                                   x_menu_id                => 1026906,
                                   — Menu ID
                                   x_entry_sequence         => l_seq,
                                   — Sequence Number
                                   x_sub_menu_id            => NULL,
                                   — Sub menu ID
                                   x_function_id            => l_function_id,
                                   — Function ID
                                   x_grant_flag             => ‘Y’,                                   — Grant Flag
                                  x_prompt                 => ‘Function for Testing’, — Prompt
                                   x_description            => ‘Function for Testing’,
                                   — Description                                                 x_creation_date          => SYSDATE,
                                   — Creation Date
                                   x_created_by             => fnd_global.user_id,
                                   — Created by
                                   x_last_update_date       => SYSDATE,
                                   x_last_updated_by        => fnd_global.user_id,
                                   — Last Updated by
                                   x_last_update_login      => fnd_global.login_id
                                  — Last update login
                                  );
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SQLERRM || ‘ Is the error’);
   END;
END;
        
Step4:   Menus will be created in the Database successfully and is shown in 2.1

1.1 Screen Layout

  • January 29, 2016 | 19 views