Sometimes we create values inside valuesets that we may not need during code migration. As a developer, we might want to delete that value from that valueset during development, that way we are not migrating unnecessary information to higher instances.

Let’s create a test value set.

And create a couple of test values to the valueset.

SQL code to derive the values of the valueset from backend:

select * 
from fnd_flex_values
where flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets
where flex_value_set_name = 'XX_TEST_VALUESET');

Now, below is the code to delete value from the valueset:

declare 

l_flex_value_id  fnd_flex_values.flex_value_id%TYPE; 
l_flex_value     fnd_flex_values.flex_value%TYPE;

begin 

SELECT ffv.flex_value_id, ffv.flex_value
INTO   l_flex_value_id, l_flex_value
        FROM fnd_flex_value_sets ffvs,
             fnd_flex_values ffv,
             fnd_flex_values_tl ffvt
       WHERE     flex_value_set_name = 'XX_TEST_VALUESET' -- 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'
             and flex_value = 'Test Value 2';
             
      dbms_output.put_line ('l_flex_value_id is: '||l_flex_value_id||' and l_flex_value is :'||l_flex_value);

      fnd_flex_values_pkg.delete_row (l_flex_value_id);
      COMMIT;
      
      dbms_output.put_line (l_flex_value||' value deleted succesfully...');
                   
exception when others
then 

dbms_output.put_line ('Error during execution: '||SQLERRM); 

end;

Valueset after the script is run:

Note: Make sure this script is used only in a development instance and not in any controlled instances. APPS access is required to run the above script. Please use it carefully.