API to Delete Values in Value Set

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 = 'AQS_HR_JOB_TITLE_VS' -- 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 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;