Oracle Apps : Set Organization (Operating Unit) while working in a database session

-- ####################################
-- Notes
-- ####################################
-- All the queries below set the Organisation (read: Operating Unit) information for the session
--
-- Query 01 -- does the same by setting the user and responsibility for the session.
-- The calling code then derives the Organisation (Operating Unit, value in profile "MO: Operating Unit"
-- for that user/responsibility.

-- Query 02 -- does the same by directly setting the client info to the required 'Operating Unit'

-- Query 03 -- also does the same by directly setting the client info to the required 'Operating Unit'

-- Fyi - Operating units are defined in the HR_ALL_ORGANIZATION_UNITS table.

-- ####################################
-- Query 01
-- ####################################
-- The following call to apps_intialize function does not pass
-- the parameter "security_group_id", hence it is defaulted to
-- to zero (that is the setup Business Group, Business Group is the topmost level in the
-- Organization Definition Hierarchy. So generally the ORG_ID for the same is zero)

declare
    l_user_id      number;
    l_resp_id      number;
    l_resp_appl_id number;
begin
    -- get conversion user
    select fu.user_id
    into   l_user_id
    from   fnd_user fu
    where  fu.user_name = :p_user_name;
    -- get conversion responsibility
    select fr.responsibility_id,
           fr.application_id
    into   l_resp_id,
           l_resp_appl_id
    from   fnd_responsibility fr
    where  fr.responsibility_key = :p_responsibility_key;
    fnd_global.apps_initialize(
      user_id       => l_user_id,
      resp_id       => l_resp_id,
      resp_appl_id  => l_resp_appl_id);
end;
/

-- ####################################
-- Query 02
-- ####################################
-- To set the organisation for the session the following code can be used.

begin
  fnd_client_info.set_org_context('255');
end;
/


-- ####################################
-- Query 03
-- ####################################
-- To set the organisation for the session the following code can also be used.

exec dbms_application_info.set_client_info ('255');