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