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