To Set ORG ID for R12 Oracle

To fetch data from views and to get the same for the required Organization ID, we need to set the org_id in the SQL Developer.

For SQL Developer:

BEGIN 
MO_GLOBAL.SET_POLICY_CONTEXT('S', <org_id>); 
END; 

For SQL*PLUS:

SQL> exec mo_global.init('SQLAP'); exec mo_global.set_policy_context('S','&org_id');

For sqlplus to have same org context as given responsibility : 

DECLARE
  l_resp_id             NUMBER;
  l_user_id             NUMBER;
  l_security_profile_id NUMBER;
  l_org_id              NUMBER;

BEGIN
  SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name LIKE '&USER_NAME';
  SELECT responsibility_id
  INTO l_resp_id
  FROM FND_RESPONSIBILITY_VL
  WHERE responsibility_name LIKE '&responsibility_name';
  FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, <Application ID>);
  MO_GLOBAL.INIT('SQLAP');
END;

Sql Script to delete a value in Value Set

DECLARE

   l_err_msg   VARCHAR2 (500) := NULL;
   CURSOR flex
   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 = <Insert The 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.flex_value_meaning = <Insert The Value Set Meaning>;

BEGIN
   FOR i IN flex
   LOOP
      fnd_flex_values_pkg.delete_row (i.flex_value_id);
      COMMIT;
      DBMS_OUTPUT.put_line (i.flex_value_id || ' Deleted');
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      l_err_msg := SQLERRM;
      DBMS_OUTPUT.put_line ('in Exception ' || l_err_msg);
END;