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