Script/API to Delete Values in Value Set

In general, once you have set up and begun to use a flexfield, you should never change anything about its structure or its value sets (other than defining, enabling, and disabling values, shorthand aliases, and cross-validation and security rules). But many times in real time scenarios we need to add, modify or delete some or many values from existing value set.

Thus, below script helps you to delete required value from existing value set.

*********************************************************************

DECLARE
  l_err_msg VARCHAR2 (500) := NULL;
  CURSOR flex_values
  IS
    SELECT ffv.flex_value_id,
      ffv.flex_value
    FROM fnd_flex_value_sets ffvs,
      fnd_flex_values ffv,
      fnd_flex_values_tl ffvt
    WHERE 1                            = 1
    AND UPPER(flex_value_set_name)     = UPPER('XX_GROUP_BY_CLAUSE')--Pass Your Value Set Name
    AND ffv.flex_value_set_id          = ffvs.flex_value_set_id
    AND ffvt.flex_value_id             = ffv.flex_value_id
    AND ffvs.flex_value_set_id         = ffv.flex_value_set_id
    AND UPPER(ffvt.flex_value_meaning) = UPPER('Item Category') --Pass Your Value which you wish to delete from value set
      ;
BEGIN
  FOR i IN flex_values
  LOOP
    fnd_flex_values_pkg.delete_row (i.flex_value_id);
    COMMIT;
    DBMS_OUTPUT.put_line ('Flex Value '||'"'||i.flex_value_id ||'-'||i.flex_value|| '"'||' Deleted');
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  l_err_msg := SQLERRM;
  DBMS_OUTPUT.put_line ('In Exception ' || l_err_msg);
END;
*********************************************************************

HAPPY LEARNING…!!!