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