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:

Purpose Procedure/Function Description
Initialize INIT Set up multiple organizations context.
JTT initialize JTT_INIT Initialize multiple organizations for JTT based application.
Is multi-org enabled is_multi_org_enabled Check if the multiple organizations instance is enabled.
Check access check_access Check if the operating unit is accessible.
Get OU name get_ou_name Get the operating unit name.
Check valid org check_valid_org Check if the organization is valid.
Set policy context set_policy_context Set the application policy context.
Get current org Id get_current_org_id Get the current organization ID in the application context.
Get access mode get_access_mode Get the application context mode.
Get OU count get_ou_count Get the operating unit count on the access control list.
Get valid org get_valid_org Get the current default/valid organization.
Validate org id public API validate_orgid_pub_api Get the default organization and check if the organization is valid.
Is MO initialization done is_mo_init_done Check if the multiple organization is initialized.

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:

Purpose Procedure/Function Description
Purpose Procedure/Function Description
Get Ledger Name Get_Ledger_Name Returns the ledger name.
Get ledger information Get_Ledger_Info Returns information about the ledger.
Get Default operating unit get_default_ou Gets the default operating unit from MO: Default Operating Unit profile or from current organization.
Get Default operating unit Id get_default_org_id Returns the organization ID of the default operating unit.
Check org in security profile check_org_in_sp Checks if the specified organization is present in the security profile.
Check ledger in security profile check_ledger_in_sp Checks if all operating units of a ledger are included in the security profile.
Get organization name Get_Org_Name Returns the operating unit name for an organization ID.
Get organization IDs for the ledger get_orgid_fr_ledger Returns the operating unit ID and the number of operating units in the given ledger.

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