Multi-Org Architecture

Multi-Org Architecture before to Release 12

Oracle has come up with a column ORG_ID to accommodate data of different operating units in one table,. For example PO_REQUISITION_HEADERS_ALL.org_id is column which stores operating unit ID.
Whereas the table PO_REQUISITION_HEADERS_ALL is stored in PO Schema and a VIEW PO_HEADERS is present in APPS Schema which return rows corresponded to the current operating unit’s organization ID only.
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

Users allowed defining security profiles through this architecture, so 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_REQUISITION_HEADERS_ALL, but rather a synonym which points to PO_REQUISITION_HEADERS_ALL
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.
A Row Level security is applied to PO_REQUISITION_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_REQUISITION_HEADERS';
With effect of this security policy, whenever you access PO_REQUISITION_HEADERS, Oracle RLS will dynamically append WHERE CLAUSE similar to below

SELECT *

FROM PO_REQUISITION_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.

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.

MO_GLOBAL.ORG_SECURITY is a function that returns a predicate for the WHERE CLAUSE
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 option ‘S’ is used to fetch records from specified org_id and

option ‘M’ is used to fetch records from all accessible Operating Units.

Also read : Multi-Org Setup