MOAC – Oracle Apps ORG_ID, Multi Org Concept

Its quite obvious that an organization may have its operations in different locations of the globe which we call as operating units in-terms of oracle ERP.

Multi-Org Architecture Prior to Release 12

• To accommodate data of different operating units in one table, Oracle has comeup with a column ORG_ID. For example PO_HEADERS_ALL.org_id is column which stores operating unit ID.
• Where as the table PO_HEADERS_ALL is stored in PO Schema and a VIEW PO_HEADERS is present in APPS Schema which would only return rows which corresponded to the current operating unit’s organization ID.
• A view in the APPS schema provides the Multi-Org filtering based on the statement below in the where clause.

  SUBSTRB(USERENV ('CLIENT_INFO'), 1, 10)

• When logging into the applications, the CLIENT_INFO value was set to the appropriate operating unit organization ID for the session based on the profile option setting for “MO: Operating Unit”.
• In SQL*Plus, CLIENT_INFO could be set to point to a particular operating unit using

  execute dbms_application_info.set_client_info('');

Multi-Org Architecture in Release 12

In release 12 a more flexible architecture has been put in place to support Multi-Org Access Control (MOAC). This architecture allows users to define security profiles so that users may access data for more than one operating unit within a single responsibility.

To accomplish this

• Multi-org views have been removed, and replaced with synonyms. For example, PO_HEADERS would no longer be a VIEW defined on PO_HEADERS_ALL, but rather a synonym which points to PO_HEADERS_ALL
• PO_HEADERS and PO_HEADERS_ALL are both synonyms which point to the table PO.PO_HEADERS_ALL. However, the view PO_HEADERS_ALL is unrestricted, whereas, PO_HEADERS will only display data for the user’s current operating unit(s) because of the VPD policy that has been assigned to it
• The data restriction is accomplished by assigning a virtual private database (VPD) policy also known as Row Level Security (RLS) to the synonym. This policy allows the system to dynamically generate restricting conditions when queries are run against the synonym
• Data relating to defined VPD policies is viewable in the data dictionary view DBA_POLICIES. These policies associate a function with an object, and when the object is accessed, this function can return additional restrictions on the object to restrict the data returned. The particular policy used to implement Multi-Org in release 12 is:
  1. Policy_name: ORG_SEC
  2. Policy_group: SYS_DEFAULT
  3. Package: MO_GLOBAL
  4. Function: ORG_SECURITY
• A Row Level security is applied to PO_HEADERS, using package function MO_GLOBAL.ORG_SECURITY. Below query will help you to find out the security policy applied on PO_HEADERS

  SELECT * FROM dba_policies WHERE object_name LIKE 'PO_HEADERS';

• With effect of this security policy, whenever you access PO_HEADERS, Oracle RLS will dynamically append WHERE CLAUSE similar to below

   SELECT *
     FROM PO_HEADERS
    WHERE EXISTS
    (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa.organization_id = org_id
    )

  MO_GLOB_ORG_ACCESS_TMP:
  MO_GLOB_ORG_ACCESS_TMP is a global temporary table. The current session is able see data that it placed in the table but other sessions cannot. Data in the table is temporary. It has a data duration of SYS$SESSION. Data is removed at the end of this period. The user will be able to access data for any org_id which has been populated into mo_glob_org_access_tmp. When a session is initialized in the applications, values will be populated into mo_glob_org_access_tmp for each of the operating units the user has access to based on their “MO: Security Profile” setting.

More about ‘MO_GLOBAL.ORG_SECURITY’

The purpose of Row-Level-Security is to hide certain data [based on some conditions]. RLS does so by appending a where clause to the secured object.

1.  MO_GLOBAL.ORG_SECURITY is a function that returns a predicate for the WHERE CLAUSE
2. The where clause will be appended to Table/Synonym/View for which Multi Org Row Level security is enabled

How to set Org Context in R12?

BEGIN
  execute mo_global.set_policy_context('S',&org_id);
END;
--'S' means Single Org Context.

Where

1. option ‘S’ is used to fetch records from specified org_id
2. option ‘M’ is used to fetch records from all accessible Operating Units
3. option ‘A’ is used to fetch records from all Operating Units

If you want access to multiple organization data, you can change the first parameter to ‘M’ (for multiple orgs), all organizations accessible by user:

BEGIN
apps.mo_global.set_policy_context('M','');
END;

How to check if user has access to a particular organization?

SELECT organization_id,
  name,
  mo_global.check_access(ORGANIZATION_ID) accessible
FROM HR_OPERATING_UNITS;

Other procedures/functions of MO_GLOBAL API are listed below:

For more information on the above APIs and how to use them, please go through Oracle’s Integration Repository

How to check if an Organization is present is Security Profile or not?

We have another MOAC utility API ‘MO_UTILS’ which can be used in reports and extensions.

SELECT organization_id,
  name,
  mo_utils.check_org_in_sp(ORGANIZATION_ID, 'OPERATING_UNIT') accessible
FROM HR_OPERATING_UNITS;
-- Returns Y or N depending on whether user has access to given organization

Other procedures/functions of MO_UTILS API are listed below:

Please drop a comment if you need any more additional information, I will do my best to help you out.