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
  • January 29, 2016 | 18 views
  • Comments