Thursday 28 January 2016

Creating Custom Value Sets and inserting to base tables through API

Step1:  Create custom valuesets in source instance. Use the below API’s to move the value sets to the target instance. 

Step2: The custom value sets  can be passed to the below API’s to insert data to Oracle standard tables fnd_flex_valuesets          
                       
Ø  fnd_flex_val_api.create_valueset_independent f
Ø  fnd_flex_val_api.create_valueset_dependent
Ø  fnd_flex_val_api.create_valueset_table
Ø fnd_flex_val_api.create_valueset_special
Ø fnd_flex_val_api.create_valueset_none

Step3:  Sample script with description to upload the  Independent/Dependent/Table Value sets are explained below
Independent Value Set
-------------------------------
DECLARE
   value_set_name            VARCHAR2 (200);
   description               VARCHAR2 (200);
   security_available        VARCHAR2 (200);
   enable_longlist           VARCHAR2 (200);
   format_type               VARCHAR2 (200);
   maximum_size              NUMBER;
   PRECISION                 NUMBER;
   numbers_only              VARCHAR2 (200);
   uppercase_only            VARCHAR2 (200);
   right_justify_zero_fill   VARCHAR2 (200);
   min_value                 VARCHAR2 (200);
   max_value                 VARCHAR2 (200);
   v_session_mode            VARCHAR2 (20)   := 'customer_data';
   x                         VARCHAR2 (200);
   v_msg                     VARCHAR2 (2000);
BEGIN
   fnd_flex_val_api.set_session_mode (v_session_mode);   
   value_set_name := 'CUST_VAL_SET_DEMO';
   description := 'Custom Valueset';   
   security_available := 'N';
   enable_longlist := 'N';
   format_type := 'C';
   maximum_size := 30;
   PRECISION := 1;
   numbers_only := 'N';
   uppercase_only := 'N';
   right_justify_zero_fill := 'N';
   min_value := '1';
   max_value := '30';
   apps.fnd_flex_val_api.create_valueset_independent
                                                    (value_set_name,
                                                     description,
                                                     security_available,
                                                     enable_longlist,
                                                     format_type,
                                                     maximum_size,
                                                     PRECISION,
                                                     numbers_only,
                                                     uppercase_only,
                                                     right_justify_zero_fill,
                                                     min_value,
                                                     max_value
                                                    );
EXCEPTION
   WHEN OTHERS
   THEN
      v_msg := fnd_flex_val_api.MESSAGE;
      DBMS_OUTPUT.put_line (v_msg);
      DBMS_OUTPUT.put_line (SQLERRM);
END;
COMMIT ;
Dependent Value Set
----------------------------
DECLARE
   value_set_name            VARCHAR2 (200);
   parent_value_set_name     VARCHAR2 (200);
   description               VARCHAR2 (200);
   security_available        VARCHAR2 (200);
   enable_longlist           VARCHAR2 (200);
   format_type               VARCHAR2 (200);
   maximum_size              NUMBER;
   PRECISION                 NUMBER;
   numbers_only              VARCHAR2 (200);
   uppercase_only            VARCHAR2 (200);
   right_justify_zero_fill   VARCHAR2 (200);
   min_value                 VARCHAR2 (200);
   max_value                 VARCHAR2 (200);
   v_session_mode            VARCHAR2 (20)   := 'customer_data';
   x                         VARCHAR2 (200);
   v_msg                     VARCHAR2 (2000);
BEGIN
   fnd_flex_val_api.set_session_mode (v_session_mode);
   value_set_name := 'CUST_DEPENDENT_VAL_SET';
   parent_value_set_name := 'CUST_VAL_SET_DEMO';
   description := 'Dependent Value Set';
   security_available := 'N';
   enable_longlist := 'N';
   format_type := 'C';
   maximum_size := 30;
   PRECISION := 1;
   numbers_only := 'N';
   uppercase_only := 'N';
   right_justify_zero_fill := 'N';
   min_value := NULL;
   max_value := NULL;
   IF (apps.fnd_flex_val_api.valueset_exists (value_set_name))
   THEN
      DBMS_OUTPUT.put_line ('Value set exists.. Deleting it...');
      apps.fnd_flex_val_api.delete_valueset (value_set_name);
   END IF;
   IF NOT (apps.fnd_flex_val_api.valueset_exists (value_set_name))
   THEN
      DBMS_OUTPUT.put_line ('Value set doesn''t exists.. Creating it...');
      apps.fnd_flex_val_api.create_valueset_dependent
                                                    (value_set_name,
                                                     description,
                                                     security_available,
                                                     enable_longlist,
                                                     format_type,
                                                     maximum_size,
                                                     PRECISION,
                                                     numbers_only,
                                                     uppercase_only,
                                                     right_justify_zero_fill,
                                                     min_value,
                                                     max_value,                                                     parent_value_set_name,
                                                     'DEFAULT',
                                                     'Default'
                                                    );
      COMMIT;
      IF (apps.fnd_flex_val_api.valueset_exists (value_set_name))
      THEN
         DBMS_OUTPUT.put_line ('Value set:' || value_set_name
                               || ' got created'
                              );
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      v_msg := fnd_flex_val_api.MESSAGE;
      DBMS_OUTPUT.put_line (v_msg);
      DBMS_OUTPUT.put_line (SQLERRM);
END;
Table Value Sets
BEGIN
   fnd_flex_val_api.set_session_mode ('customer_data');         /*Mandatory*/
   fnd_flex_val_api.create_valueset_table
                    (value_set_name               => 'XX_VALUESET',
                     description                  => 'createdfrombackend',
                     security_available           => 'N',
                     enable_longlist              => 'N',
                     format_type                  => 'Char',
                     maximum_size                 => 20,
                     PRECISION                    => NULL,
                     numbers_only                 => 'N',
                     uppercase_only               => 'N',
                     right_justify_zero_fill      => 'N',
                     min_value                    => NULL,
                     max_value                    => NULL,
                     table_application            => 'Application Object Library',
                     table_appl_short_name        => 'AOL',
                     table_name                   => 'FND_LOOKUP_VALUES FND',
                     allow_parent_values          => 'N',
                     value_column_name            => 'FND.LOOKUP_CODE',
                     value_column_type            => 'Char',
                     value_column_size            => 40,
                     meaning_column_name          => NULL,
                     meaning_column_type          => NULL,
                     meaning_column_size          => 40,
                     id_column_name               => 'FND.MEANING',                                                                                   id_column_type               => 'Char',
                     id_column_size               => 40,
                     where_order_by               => 'where lookup_type=''XXTEST_LOOKUP''',
                     additional_columns           => NULL
                    );
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;
Step4:   Value sets will be created in the Database successfully and is shown in 2.1
2.1 Screenshot for Value sets created through Backend
Independent Value Sets

Dependent Value Set


Table Value Sets


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