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