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;

SQL Query to Extract Employee Contacts

SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = ‘CONTACT’
AND hl.lookup_code(+) = pcr.contact_type