Delete ValueSet Values - API

DECLARE

  l_err_msg VARCHAR2(500) := NULL;

  CURSOR c1 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 flex_value_set_name = ':myvalueset' -- Value Set Name

       AND ffv.flex_value=:flex_val

       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 ffvt.language = 'US'

       AND ffv.enabled_flag = 'Y'

       AND ffv.summary_flag = 'N';

BEGIN

  FOR i IN c1

  LOOP

    fnd_flex_values_pkg.delete_row(i.flex_value_id);

    COMMIT;

    DBMS_OUTPUT.put_line(i.flex_value_id || ' Deleted  Successfully !!!!');

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

    l_err_msg := SQLERRM;

    DBMS_OUTPUT.put_line('Exception: ' || l_err_msg);

 

END;

Below statement used to deleted DFFS

DECLARE

  l_application_id               NUMBER := 0;

  l_descriptive_flexfield_name   VARCHAR2(100) := 'FND_FLEX_VALUES';

  l_descriptive_flex_context_cod VARCHAR2(100) := 'context code';

BEGIN

  --FND_DESCRIPTIVE_FLEXS_PKG –this package is for DFF

  --FND_DESCR_FLEX_CONTEXTS_PKG –this package is for DFF Context

  --FND_DESCR_FLEX_COL_USAGE_PKG –this package is for DFF Column useage

  --When creating a new DFF Context, it will check the DFF Column usage if the context is already used.

  --so when deleting a DFF Context, both the context and column usage should be deleted.

  FOR c IN (SELECT application_column_name

              FROM fnd_descr_flex_column_usages

             WHERE application_id = l_application_id

               AND descriptive_flexfield_name = l_descriptive_flexfield_name

               AND descriptive_flex_context_code =

                   l_descriptive_flex_context_cod) 

  LOOP

fnd_descr_flex_col_usage_pkg.delete_row(x_application_id => l_application_id,

             x_descriptive_flexfield_name => l_descriptive_flexfield_name,

             x_descriptive_flex_context_cod=> l_descriptive_flex_context_cod,

             x_application_column_name      => c.application_column_name);

  END LOOP;

  begin

    fnd_descr_flex_contexts_pkg.delete_row(0,

                                           l_descriptive_flexfield_name,

                                           l_descriptive_flex_context_cod);

  end;

  commit;

end;