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;

/