Thursday 28 January 2016

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


2 comments:

  1. Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    Big Data and Hadoop Training In Hyderabad

    ReplyDelete
  2. Thank you for sharing such a nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information.

    Oracle Fusion HCM Online Training
    Oracle Fusion HCM Training

    ReplyDelete