MOAC (Multiple Organization Access Control) in Oracle E-Business Suite Release 12

With the release of EBS R12, Oracle has released some new features on how data is accessed through the applications and standard programs by the use of Multiple Organization Access Control (MOAC). MOAC allows you to create a security group which can contain many operating units and assign that to the User’s responsibility. All the forms that process OU striped data now allow you to pick an OU to work in from a list that contains all the OU you have access to. You will also find all the OU based reports have a parameter added for OU.

Setting up MOAC in Oracle EBS

The setup is straight forward. You can define a security profile in the HR Security Profile form, adding Operating Units to it, and then you must run Security List Maintenance program before you can assign the security profile to the profile option MO: Security Profile for a responsibility.
MOAC is initialized when you open a Form, OA page or a Report. The first MOAC call checks if the profile “MO: Security Profile” has a value. If Yes, then the list of operations units to which access is allowed is fetched and the list of values (LOV) is populated. Then default value of the LOV is set to the operating unit specified in “MO: Default Operating Unit”. This is how MOAC works in Oracle Release 12 when the value of “MO: Security Profile” is set.
When the profile “MO: Security Profile” does not have a value MOAC switches to the 11i single organization mode. As in 11i, the profile “MO: Operating Unit” is checked and the operating unit is initialized to the one defined in it.

The important point to note here is that the profile “MO: Operating Unit” is ignored when the profile “MO: Security Profile” is set. This enables us to use both R12 MOAC behavior and 11i behavior simultaneously in R 12. You can also choose to completely use one of them.

 

How Does the R12 MOAC Defaulting Rules Work?

  1. If the profile option “MO: Security Profile” is not set, then “MO:
    Operating Unit” value is used as the default Operating Unit even if “MO:
    Default Operating Unit” profile is set to a different value.
  2. If the profile option “MO: Security Profile” is set and gives access to
    one Operating Unit, the default Operating Unit will return this value even if
    “MO: Default Operating Unit” is set to a different value.
  3. If the profile option “MO: Security Profile” is set and gives access to
    multiple Operating Units, then the profile value “MO: Default Operating Unit”
    if set is validated against the list of Operating Units in “MO: Security
    Profile”. If the Operating Unit is included in the security profile then it
    is returned as the default value. Otherwise there is no Operating Unit
    default. Moreover, if the Profile Option “MO: Default Operating Unit” is not
    set, then there is no default Operating Unit.

 

How Does This Impact You?

With R12 the views owned by “apps” schema are replaced by synonyms to the base tables. These synonyms have security policies attached to them to provide the proper org_ID(s) in the where clause to retrieve the data (the database rewrites the SQL statement to include the conditions set by the security policy). You can read up on virtual private database (VPD) and its features in the Oracle Metalink note mentioned at top of the article. If your report or program uses these synonyms then you will want to choose the proper value for the Operating Unit Mode field. However, if your report or program does use the base tables then this field is not as important.

To understand which applications have multiple organizations access control feature enabled you can query a new table “FND_MO_PRODUCT_INIT). If the product that is enabled has a “Y” for status field then that product can use the MOAC feature.

 

Pre R12 Multi-Org Features

  • Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).
  • A view in the APPS schema provides the Multi-Org filtering based on the following statement in the where clause.   SUBSTRB(USERENV (’CLIENT_INFO’), 1, 10)

 

R12 Multi-Org Features

  • Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).
  • A synonym in the APPS schema provides the Multi-Org filtering based the Virtual Private Database feature of the Oracle 10G DB Server.

Pre-R12 you could set your SQL session context for multi-org with the following:
BEGIN
dbms_application_info.set_client_info(101);
END;

  • In this example 101 is the ORG_ID for the Operating Unit or you could have used FND_GLOBAL.APPS_INITIALIZE to set your context.

In R12 you can set your SQL session context for a single OU with the following:
BEGIN
execute mo_global.set_policy_context(’S’,101);
END;

  • The ‘S’ means Single Org Context
  • 101 is the ORG_ID you want set

Also In R12 you can set your SQL session context for multiple OUs with the following:
BEGIN
execute mo_global.set_org_access(NULL,111,‘ONT’);
END;

  • 111 is the Security Profile you want to use
  • ‘ONT’ is the application short name associated with the responsibility you will be using to find the security profiles:

The following SQL will dump 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;