Search This Blog

Saturday, July 13, 2013

Security profile in Oracle apps MOAC

Queries related to Security profile in Oracle apps MOAC

1.      If you do not know the security profile or operating unit profile option settings for your user, responsibility and application, you could use code similar to the following to get this information:

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;


begin

select user_id into l_user_id
from fnd_user
where user_name = upper('&user_name');

select responsibility_id into l_resp_id
from fnd_responsibility_vl
where responsibility_name = ('&resp_name');

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_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
  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);
end;
/




2.      The following SQL will fetch out the Security Profiles and Operating Unit Names assigned to them

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;


3.      The following SQL will fetch out the Security Profiles assigned in via the 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