Wednesday, December 21, 2016

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:
PURPOSEPROCEDURE/FUNCTIONDESCRIPTION
InitializeINITSet up multiple organizations context.
JTT initializeJTT_INITInitialize multiple organizations for JTT based application.
Is multi-org enabledis_multi_org_enabledCheck if the multiple organizations instance is enabled.
Check accesscheck_accessCheck if the operating unit is accessible.
Get OU nameget_ou_nameGet the operating unit name.
Check valid orgcheck_valid_orgCheck if the organization is valid.
Set policy contextset_policy_contextSet the application policy context.
Get current org Idget_current_org_idGet the current organization ID in the application context.
Get access modeget_access_modeGet the application context mode.
Get OU countget_ou_countGet the operating unit count on the access control list.
Get valid orgget_valid_orgGet the current default/valid organization.
Validate org id public APIvalidate_orgid_pub_apiGet the default organization and check if the organization is valid.
Is MO initialization doneis_mo_init_doneCheck 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:
PURPOSEPROCEDURE/FUNCTIONDESCRIPTION
PurposeProcedure/FunctionDescription
Get Ledger NameGet_Ledger_NameReturns the ledger name.
Get ledger informationGet_Ledger_InfoReturns information about the ledger.
Get Default operating unitget_default_ouGets the default operating unit from MO: Default Operating Unit profile or from current organization.
Get Default operating unit Idget_default_org_idReturns the organization ID of the default operating unit.
Check org in security profilecheck_org_in_spChecks if the specified organization is present in the security profile.
Check ledger in security profilecheck_ledger_in_spChecks if all operating units of a ledger are included in the security profile.
Get organization nameGet_Org_NameReturns the operating unit name for an organization ID.
Get organization IDs for the ledgerget_orgid_fr_ledgerReturns 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

No comments:

Post a Comment

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...