API to Load Values into Value Sets

----------------------------Local Variables---------------------------
   l_enabled_flag             VARCHAR2 (2);
   l_summary_flag             VARCHAR2 (2);
   l_who_type                 FND_FLEX_LOADER_APIS.WHO_TYPE;
   l_user_id                  NUMBER                := FND_GLOBAL.USER_ID;
   l_login_id                 NUMBER                := FND_GLOBAL.LOGIN_ID;
   l_value_set_name           FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_NAME%TYPE;
   l_value_set_value          FND_FLEX_VALUES.FLEX_VALUE%TYPE;

   l_value_set_name             :='VALUE_SET_NAME';
   l_value_set_value            :='VALUE_SET_VALUE';
   l_enabled_flag               := 'Y';
   l_summary_flag               := 'N';
   l_who_type.created_by        := l_user_id;
   l_who_type.creation_date     := SYSDATE;
   l_who_type.last_updated_by   := l_user_id;
   l_who_type.last_update_date  := SYSDATE;
   l_who_type.last_update_login := l_login_id;

                  (p_upload_phase               => 'BEGIN',
                   p_upload_mode                => NULL,
                   p_custom_mode                => 'FORCE',
                   p_flex_value_set_name        => l_value_set_name,
                   p_parent_flex_value_low      => NULL,
                   p_flex_value                 => l_value_set_value,
                   p_owner                      => NULL,
                   p_last_update_date           => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                   p_enabled_flag               => l_enabled_flag,
                   p_summary_flag               => l_summary_flag,
                   p_start_date_active          => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                   p_end_date_active            => NULL,
                   p_parent_flex_value_high     => NULL,
                   p_rollup_flex_value_set_name => NULL,
                   p_rollup_hierarchy_code      => NULL,
                   p_hierarchy_level            => NULL,
                   p_compiled_value_attributes  => NULL,
                   p_value_category             => 'VALUE_SET_NAME',
                   p_attribute1                 => '40912',
                   p_attribute2                 => NULL,
                   p_attribute3                 => NULL,
                   p_attribute4                 => NULL,
                   p_attribute5                 => NULL,
                   p_attribute6                 => NULL,
                   p_attribute7                 => NULL,
                   p_attribute8                 => NULL,
                   p_attribute9                 => NULL,
                   p_attribute10                => NULL,
                   p_attribute11                => NULL,
                   p_attribute12                => NULL,
                   p_attribute13                => NULL,
                   p_attribute14                => NULL,
                   p_attribute15                => NULL,
                   p_attribute16                => NULL,
                   p_attribute17                => NULL,
                   p_attribute18                => NULL,
                   p_attribute19                => NULL,
                   p_attribute20                => NULL,
                   p_attribute21                => NULL,
                   p_attribute22                => NULL,
                   p_attribute23                => NULL,
                   p_attribute24                => NULL,
                   p_attribute25                => NULL,
                   p_attribute26                => NULL,
                   p_attribute27                => NULL,
                   p_attribute28                => NULL,
                   p_attribute29                => NULL,
                   p_attribute30                => NULL,
                   p_attribute31                => NULL,
                   p_attribute32                => NULL,
                   p_attribute33                => NULL,
                   p_attribute34                => NULL,
                   p_attribute35                => NULL,
                   p_attribute36                => NULL,
                   p_attribute37                => NULL,
                   p_attribute38                => NULL,
                   p_attribute39                => NULL,
                   p_attribute40                => NULL,
                   p_attribute41                => NULL,
                   p_attribute42                => NULL,
                   p_attribute43                => NULL,
                   p_attribute44                => NULL,
                   p_attribute45                => NULL,
                   p_attribute46                => NULL,
                   p_attribute47                => NULL,
                   p_attribute48                => NULL,
                   p_attribute49                => NULL,
                   P_ATTRIBUTE50                => NULL,
                   p_flex_value_meaning         => l_value_set_value,
                   p_description                => NULL

         DBMS_OUTPUT,PUT_LINE('Error is ' || SUBSTR (SQLERRM, 1, 1000));

How to Create Category and Category Set in Oracle Inventory?


Categories are the method by which the items in inventory can be separated logically and functionally for planning, purchasing and other activities.You can use categories and category sets to group your items for various reports and programs. A category is a logical classification of items that have similar characteristics. A category set is a distinct grouping scheme and consists of categories. The flexibility of category sets allows you to report and inquire on items in a way that best suits your needs. This article will describe how to create categories and category set in oracle inventory.

Suppose we need a category called ‘INV_COLORS’. We can define multiple colors in this category and then assign this category to an item.


  1. Item1 —- Black
  2. Item2 —- Red
  3. Item3 —- Green
  4. Item4 —- Orange

1] First we need to create a value set to hold these colors.

Navigation > Setup: Flexfields: Validation: Sets

Validation type Select: Independent

2] Next we need to enter our values in the INVENTORY_COLOR valueset


Navigation -> Setup: Flexfields: Validation: Values

Save and close the Screen.

3] Now we need to create a KFF Structure

Navigation Setup: Flexfields: Key: Segments

Create the structure name: In the “Code” field enter INV_COLORS

4] Click on the “Segments” button.

  • Enter the “Number” field: 10
  • Enter the Name field: Color
  • Enter the “Window Prompt”: Color (This value will appear on the screen)
  • Enter the “Column” field: Segment1 (you can choose any column)

Save and exit the form.

5] Check the Freeze flex field Definition, the following warning will appear.

Click OK.

6] The “Compile” button is now available to be selected. Click on the compile button.

Click Ok

Close the form.

7] Go to View -> Request and Verify that the new Category flexfield compiled successfully.

8] The new structure is ready for use. Now let’s create a category.

Navigation : Setup: Items: Categories: Category Codes

  • Enter the structure name: INV_COLORES
  • Enter the category: BLACK
  • (Note the form does not provide an LOV for the categories. You will need to use edit symbol at the top of the page or “ e “ to bring up the lov)
  • Enter the description.

9] Next we create our category set.

Navigation Setup: Items: Categories: Category Sets

  • Fill in the category set Name: INV_COLORS_SET
  • The description: Inventory color set
  • The Flex Structure: INV_COLORS
  • The Controlled: Org Level
  • Default Category: BLACK

10] After creating the category set, we can assign it to any items.

There are few category APIs that will be discussed in upcoming posts. Till then GOOD BYE………!! Have a nice day!

Reference: How to create a Category Set and Assign Items to Categories [MOS ID 423551.1]

Queries for Value Sets

Queries for Value Sets

Value Sets based on table:
This Query gives details of value sets that are based on a oracle application tables.
select ffvs.flex_value_set_id ,
    ffvs.flex_value_set_name ,
    ffvs.description set_description ,
    ffvt.value_column_name ,
    ffvt.meaning_column_name ,
    ffvt.id_column_name ,
    ffvt.application_table_name ,
FROM fnd_flex_value_sets ffvs ,
    fnd_flex_validation_tables ffvt
WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id;
Independent Value set Details:
This query gives details of independent FND Value sets i.e. Values are static and these are not derived from any application table.
SELECT ffvs.flex_value_set_id ,
    ffvs.flex_value_set_name ,
    ffvs.description set_description ,
    ffv.flex_value_id ,
    ffv.flex_value ,
    ffvt.flex_value_meaning ,
    ffvt.description value_description
FROM fnd_flex_value_sets ffvs ,
    fnd_flex_values ffv ,
    fnd_flex_values_tl ffvt
    ffvs.flex_value_set_id     = ffv.flex_value_set_id
    and ffv.flex_value_id      = ffvt.flex_value_id
    AND ffvt.language          = USERENV('LANG');
Dibyajyoti Koch
Have a nice Day!

Query to find Parameters and Value Sets associated with a Concurrent Program

Query to find Parameters and Value Sets associated with a Concurrent Program

The following query will fetch the Parameter List and associated Value Sets of a Concurrent Program.


        fcpl.user_concurrent_program_name "Concurrent Program Name",
        fcp.concurrent_program_name "Short Name",
        fdfcuv.column_seq_num "Column Seq Number",
        fdfcuv.end_user_column_name "Parameter Name",
        fdfcuv.form_left_prompt "Prompt",
        fdfcuv.enabled_flag " Enabled Flag",
        fdfcuv.required_flag "Required Flag",
        fdfcuv.display_flag "Display Flag",
        fdfcuv.flex_value_set_id "Value Set Id",
        ffvs.flex_value_set_name "Value Set Name",
        flv.meaning "Default Type",
        fdfcuv.DEFAULT_VALUE "Default Value"

        fnd_concurrent_programs fcp,
        fnd_concurrent_programs_tl fcpl,
        fnd_descr_flex_col_usage_vl fdfcuv,
        fnd_flex_value_sets ffvs,
        fnd_lookup_values flv

        fcp.concurrent_program_id = fcpl.concurrent_program_id
        AND    fcpl.user_concurrent_program_name = :conc_prg_name
        AND    fdfcuv.descriptive_flexfield_name = '$SRS$.'
                 || fcp.concurrent_program_name
        AND    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
        AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
        AND    flv.lookup_code(+) = fdfcuv.default_type
        AND    fcpl.LANGUAGE = USERENV ('LANG')
        AND    flv.LANGUAGE(+) = USERENV ('LANG')

ORDER BY fdfcuv.column_seq_num;