To Set R12 oracle SQL Session Apps Context

SQL to get ORG_ID’s and OUs:

select ORGANIZATION_ID, 
          NAME
from HR_OPERATING_UNITS;

Pre-R12: To set SQL session context

BEGIN
  dbms_application_info.set_client_info(ORG_ID); -- #ORG_ID   
END;

OR

FND_GLOBAL.APPS_INITIALIZE

OR

exec dbms_application_info.set_client_info(ORG_ID); --#ORG_ID

R12: To set SQL session context for Single OU:

BEGIN
   execute mo_global.set_policy_context('S',ORG_ID);
END;

-- ‘S’ means Single Org Context
-- #ORG_ID

To set SQL session context for Multiple OU’s:

BEGIN
  execute mo_global.set_org_access(NULL,64,‘ONT');
END;

-- 64 is the Security Profile one wants to use
-- ‘ONT’ is the application short name associated with the responsibility

To get Security Profiles and OU Names as assigned:

select psp.SECURITY_PROFILE_NAME,
         psp.SECURITY_PROFILE_ID,
         hou.NAME,
         hou.ORGANIZATION_ID
from PER_SECURITY_PROFILES psp,
        PER_SECURITY_ORGANIZATIONS pso,
        HR_OPERATING_UNITS hou
where pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
    and pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

SQL to get Security Profiles assigned through Profile Options:

select fnd_profile.value_specific('XLA_MO_SECURITY_PROFILE_LEVEL', :user_id, :resp_id, :appl_id)
 from dual;

--   Where:
--                user_id: FND_USER.USER_ID
--                resp_id: FND_RESPONSIBILITY_TL.RESPONSIBILITY_ID
--                appl_id: FND_APPLICATIONS.APPLICATION_ID

GOOD REFS:
MOS Note:  420787.1 Oracle Applications Multiple Organizations Access Control for Custom Code
Oracle Doc: ‘Oracle Applications Multiple Organizations’ Implementation Guide Release 12 Part No. B31183-02