PLSQL Script to Set MOAC Context

PLSQL Script to Set MOAC Context:

declare

  l_user_id fnd_user.user_id%type;

  l_resp_id fnd_responsibility.responsibility_id%type;

  l_appl_id fnd_application.application_id%type;

  l_appl_short_name fnd_application_vl.application_short_name%type;

  l_ou_value fnd_profile_option_values.profile_option_value%type;

  l_sp_value fnd_profile_option_values.profile_option_value%type;

  l_prg varchar2(3) := '000';

begin

select user_id into l_user_id

from fnd_user

where user_name = upper('&user_name');

l_prg  := '010';

select responsibility_id into l_resp_id

from fnd_responsibility_vl

where responsibility_name = ('&resp_name');

l_prg  := '020';

select application_id, application_short_name into l_appl_id, l_appl_short_name

from fnd_application_vl

where application_short_name = upper('&appl_short_name');

l_prg  := '030';

l_ou_value := fnd_profile.value_specific(

  'ORG_ID',l_user_id, l_resp_id, l_appl_id);

l_sp_value := fnd_profile.value_specific(

  'XLA_MO_SECURITY_PROFILE_LEVEL', l_user_id, l_resp_id, l_appl_id);

dbms_output.put_line('MO: Operating Unit: '||l_ou_value);

dbms_output.put_line('MO: Security Profile: '||l_sp_value);

if l_sp_value is null and l_ou_value is null then

  dbms_output.put_line('No operating unit or security profile information 

    found');

else

 l_prg  := '040';

  mo_global.set_org_access(l_ou_value, l_sp_value, l_appl_short_name);

  

end if;

exception when others then

  dbms_output.put_line('Error: '||sqlerrm);

  dbms_output.put_line('Exception at: '||l_prg );

end;

/

Apps Initialization script R12

Introduction:

Many times before executing standard APIs or querying from org stripped views, you need to initialize apps context.


Script:

set serveroutput on;
declare
l_user_name varchar2(240) := 'SERU'; -- Replace this with Oracle Apps user name
l_resp_name varchar2(240) := 'INV';   -- Responsibility Name
l_prod_short_name varchar2(240) := 'INV';  -- Product Short Name
l_resp_appl_id number;
l_user_id number;
l_resp_id number;
begin

-- Get the user id
begin
select user_id
 into l_user_id
 from fnd_user
where user_name=l_user_name;
exception
 when no_data_found then
  dbms_output.put_line('user '|| l_user_name ||' not found');
  return;
 when others then
 dbms_output.put_line('exception :'||sqlerrm);
 return;
end;

-- Get the resp id and resp application id
begin
select application_id, Responsibility_id
into l_resp_appl_id, l_resp_id
from fnd_responsibility_vl
where responsibility_name like l_resp_name||'%';
exception
 when no_data_found then
  dbms_output.put_line('Responsibility '|| l_resp_name ||' not found');
  return;
when others then
 dbms_output.put_line('exception :'||sqlerrm);
 return;
end;


-- call apps init
FND_GLOBAL.APPS_INITIALIZE(USER_ID => l_user_id,RESP_ID => l_resp_id,RESP_APPL_ID=>l_resp_appl_id);

-- set mo global context.
--mo_global.set_org_context(P_ORG_ID_CHAR=> '',P_SP_ID_CHAR=> null,P_APPL_SHORT_NAME=>'');
mo_global.set_org_context(l_prod_short_name);
--mo_global.set_policy_context ('S', v_org_id);

end;

/