Difference between Org_id ,organization_id and operating_unit

For instance let us say you are having Inventory Store.
You have Inventory Stores in two different countries like India and USA
You have installed Oracle Apps single instance and entered all the suppliers information,customers data,tax rules etc.

And your business requirement is like this, all the rules and the data corresponding to indian store shouldnt be applicable/available to american store and vice versa.

So to meet the above requirements you will define two operating units one for india and one for USA.

Now each operating unit will have an ID known as ORG ID.

And we use ORG ID to secure our business data.

Now to do transactions for indian operations you need to define one responsibility and attach indian ORG ID to this responsibility.By this an indian will not see any data/rules applicable to USA.

Similarly you will define another responsibility for USA and attach USA's ORG ID to this responsibility so that an american will not see any data/rules corresponding to India.

This is the concept of ORG ID.

Now let us see what is Organization ID.

Within India you have got various branches in different parts of the country in different states like AP,UP,MP etc.
And the business requirement is like this in AP you will maintain stock of Rice,UP for Wheat and MP for Maize.
To meet this business requirement you will define 3 Inventory Organizations under Indian Operating Unit to maintain the respective stocks of the states..

Each Inventory Organization will have an Organization ID.

This is the outline of the differences between ORG ID and Organization ID.

Navigation: Inventory (Responsibility) >> Setup >> Organizations >> Organizations

Both operating unit and inventory organization are organizations defined in the Oracle EBS (E-Business Suite). They differ in the organization classification which is highlighted in the red box in below images.

The information about all the organizations (including inventory organizations) are available in HR_ORGANIZATION_UNITS table.

The information about all the operating units are available in the HR_OPERATING_UNITStable.

An inventory organization is uniquely identified by ORGANIZATION_ID.

An operating unit is uniquely identified by ORG_ID in different tables for which the value is the ORGANIZATION_ID from HR_OPERATING_UNITS table.

Queries:

SELECT *

  FROM hr_organization_units;

SELECT *

  FROM hr_operating_units;

MOAC (Multiple Organization Access Control) in Oracle E-Business Suite Release 12

With the release of EBS R12, Oracle has released some new features on how data is accessed through the applications and standard programs by the use of Multiple Organization Access Control (MOAC). MOAC allows you to create a security group which can contain many operating units and assign that to the User’s responsibility. All the forms that process OU striped data now allow you to pick an OU to work in from a list that contains all the OU you have access to. You will also find all the OU based reports have a parameter added for OU.

The new feature in R12 enables companies wanting to implement a shared services operating model to efficiently process business transactions by allowing them to access, process and report on data for an unlimited number of operating units within a single applications’ responsibility.

The setup is straight forward. You can define a security profile in the HR Security Profile form, adding Operating Units to it, and then you must run Security List Maintenance program before you can assign the security profile to the profile option MO: Security Profile for a responsibility.

MOAC is initialized when you open a Form, OA page or a Report. The first MOAC call checks if the profile “MO: Security Profile” has a value. If Yes, then the list of operations units to which access is allowed is fetched and the list of values (LOV) is populated. Then default value of the LOV is set to the operating unit specified in “MO: Default Operating Unit”. This is how MOAC works in Oracle Release 12 when the value of “MO: Security Profile” is set.

When the profile “MO: Security Profile” does not have a value MOAC switches to the 11i single organization mode. As in 11i, the profile “MO: Operating Unit” is checked and the operating unit is initialized to the one defined in it.

The important point to note here is that the profile “MO: Operating Unit” is ignored when the profile “MO: Security Profile” is set. This enables us to use both R12 MOAC behavior and 11i behavior simultaneously in R 12. You can also choose to completely use one of them.

With MOAC, users can:

§  Perform multiple tasks across Operating Units without changing responsibilities such as invoice entry, order processing, bank payments etc. thus improving the efficiency of transactions for companies that have centralized business functions or operate Shared Service Centers

§  Obtain better information for decision making such as, accessing supplier and customer site levels details across multiple OUs

§  Speed up data entry

§  Reduce setup and maintenance of many responsibilities

How Does the R12 MOAC Defaulting Rules Work?

1.       If the profile option “MO: Security Profile” is not set, then “MO:
Operating Unit” value is used as the default Operating Unit even if “MO:
Default Operating Unit” profile is set to a different value.

2.       If the profile option “MO: Security Profile” is set and gives access to
one Operating Unit, the default Operating Unit will return this value even if
“MO: Default Operating Unit” is set to a different value.

3.       If the profile option “MO: Security Profile” is set and gives access to
multiple Operating Units, then the profile value “MO: Default Operating Unit”
if set is validated against the list of Operating Units in “MO: Security
Profile”. If the Operating Unit is included in the security profile then it
is returned as the default value. Otherwise there is no Operating Unit
default. Moreover, if the Profile Option “MO: Default Operating Unit” is not
set, then there is no default Operating Unit.

What is the impact to you?

With R12 the views owned by “apps” schema are replaced by synonyms to the base tables. These synonyms have security policies attached to them to provide the proper org_ID(s) in the where clause to retrieve the data (the database rewrites the SQL statement to include the conditions set by the security policy). You can read up on virtual private database (VPD) and its features in the Oracle Metalink note mentioned at top of the article. If your report or program uses these synonyms then you will want to choose the proper value for the Operating Unit Mode field. However, if your report or program does use the base tables then this field is not as important.

To understand which applications have multiple organizations access control feature enabled you can query a new table “FND_MO_PRODUCT_INIT). If the product that is enabled has a “Y” for status field then that product can use the MOAC feature.

Pre R12 Multi-Org Features

·         Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).

·         A view in the APPS schema provides the Multi-Org filtering based on the following statement in the where clause.   SUBSTRB(USERENV (’CLIENT_INFO’), 1, 10)

R12 Multi-Org Features

·         Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).

·         A synonym in the APPS schema provides the Multi-Org filtering based the Virtual Private Database feature of the Oracle 10G DB Server.

Pre-R12 you could set your SQL session context for multi-org with the following:
BEGIN
dbms_application_info.set_client_info(101);
END;

·         In this example 101 is the ORG_ID for the Operating Unit or you could have used FND_GLOBAL.APPS_INITIALIZE to set your context.

In R12 you can set your SQL session context for a single OU with the following:
BEGIN
execute mo_global.set_policy_context(’S',101);
END;

·         The ‘S’ means Single Org Context

·         101 is the ORG_ID you want set

Also In R12 you can set your SQL session context for multiple OUs with the following:
BEGIN
execute mo_global.set_org_access(NULL,111,‘ONT’);
END;

·         111 is the Security Profile you want to use

·         ‘ONT’ is the application short name associated with the responsibility you will be using to find the security profiles:

The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them:
select psp.SECURITY_PROFILE_NAME, psp.SECURITY_PROFILE_ID, hou.NAME, hou.ORGANIZATION_ID               

from PER_SECURITY_PROFILES psp, PER_SECURITY_ORGANIZATIONS pso, HR_OPERATING_UNITS hou           

where pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID  and pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

How MOAC works technically:

MOAC is initialized when you open a Form, Oracle EBS page or a Report or submit the concurrent program. The first MOAC call checks if the profile “MO: Security Profile” has a value. If Yes, then the list of operating units to which access is allowed is fetched and the list of values (LOV) is populated .This list of values is nothing but list of OUs associated with the Security Profile attached to MO: Security Profile. Security profiles are defined with the help of the HR responsibility. Then, default value of the LOV is set to the operating unit specified in “MO: Default Operating Unit”.

When the profile “MO: Security Profile” does not have a value, MOAC switches to the 11i single organization mode. As in 11i, the profile “MO: Operating Unit” is checked and the operating unit is initialized to the one defined in it.

The important point to note here is that the profile “MO: Operating Unit” is ignored when the profile “MO: Security Profile” is set.

MOAC setups:

Following are the basic steps to be performed in order to enable MOAC feature:

1.     Define Security Profiles (using form function ‘Define Global Security Profile’)

§  Enter a unique name for the security profile.

§  To restrict access by discrete list of organizations, select ‘Secure organizations by organization hierarchy and/or organization list for the Security Type’.

§  Check the Exclude Business Group check box to remove the business group in the list of organizations.

§  Use the Classification field to limit the list of values (LOV) in the Organization Name field. For example, if you select the classification to Operating Unit, only operating units will display in the LOV.

§  In the organization name field, select the Operating Unit for which you want access.

Repeat until you have included all organizations to which you need access.

2.     Run the concurrent program “Security List Maintenance Program” from the standard request submission form. The “Security List Maintenance Program” can be run for a single named security profile to prevent impact to other security profiles.

3.     Assign appropriate security to the profile option “MO: Security Profile” for your users and responsibilities

§  Navigate to the “System Administrator” responsibility > System Profile Options

§  Assign the security profiles to MO: Security Profile for your responsibilities and/or users.

4.     Assign a value for profile option “MO: Default Operating Unit” (Optional)

§  Navigate to System Administrator Responsibility > System Profile Options

§  Assign a default operating unit to “MO: Default Operating Unit” profile option for your responsibilities and/or user.

5.     Assign MO: Operating Unit (Mandatory for only Single Org or if MO: Security Profile is not defined)

§  Navigate to System Administrator Responsibility > System Profile Options

§  Assign the Operating unit to MO: Operating Unit profile option for your responsibility or user.

Note – From the above screen shots we can conclude that user with purchasing responsibility will be able to access data from two Operating Units Vision Operations and Vision Services.

Developer’s Insight:

To increase the flexibility and performance in a multiple organizations environment and provide the same level of data security, the DBMS Virtual Private Database (VPD) feature replaces the CLIENT_INFO function.

The Virtual Private Database (VPD) feature allows developers to enforce security by attaching a security policy to database objects such as tables, views and synonyms. It attaches a predicate function to every SQL statement to the objects by applying security policies. When a user directly or indirectly accesses the secure objects, the database rewrites the user’s SQL statement to include conditions set by security policy that are visible to the user.

MOAC –Changes to Custom Code while upgrading to R12 from 11i-–During R12 upgrade the major task is to enable the MOAC feature to custom code. Following is the recommended approach to achieve MOAC implemented in real aspect to custom code:

1.     Multiple Organizations Views/Tables Changes

Single Organization View –

§  Drop the single organization view

§  Create a synonym with the same name as the obsolete single organization view

§  Attach a policy function to the synonym

Reference Views –

§  Add the ORG_ID column if it does not exist

§  Replace single organization views with _ALL tables for all except one, which must be a secured synonym

§  Include the ORG_ID filter in the where clause of the view to avoid the cartesian product, if the ORG_ID is the driving key or part of the composite key

§  Include the ORG_ID parameter in the columns based on functions, if necessary

2.     Enhancements to Forms

The multiple organizations setup and transaction forms must display the Operating Unit field. This allows users to select the operating unit and enter the setup or transaction for the operating unit. Oracle recommends deriving the operating units from the transaction attributes.

3.      Enhancements to Reports and Concurrent Programs

§  You must remove references of CLIENT_INFO and NVL function to the ‘ORG_ID’ column in the reports.

§  Single Organization Reports—The operating unit mode for single organization reports are flagged as     ’SINGLE’ in the Define Concurrent Programs page.

§  Cross Organization Reports–The Operating Unit mode for cross organization reports are flagged as ‘MULTIPLE’ in the Define Concurrent Programs page.

4.     Enhancements to Public APIs

§  Do not use the multiple organizations temporary table directly in the SQL query.

§  Rewrite the SQL joins with two or more views to use just one secured synonym depending on the driving table for the query and replace the remaining views by _ALL tables.

§  Add the ORG_ID to the WHERE clause of the SQL to avoid cartesian joins for tables that include ORG_ID the composite or driving key.

§  Use MO_GLOBAL.Set_Policy_Context.
This API has 2 parameters –1. Operating unit 2. Context
Context has 2 values 1. M  2. S
When policy context is set to ‘M’, data from all accessible Operating Units will be returned.
When policy context is set to ‘S’, then only data from the specified Org_Id will be returned.

§  Products must call the MO_GLOBAL.init() API to execute the multiple organizations initialization.

5.     Enhancements to Workflows

With multiple organizations access control, you must set the current organization ID and not the CLIENT_INFO org context. You must derive the current organization ID from item keys. Do not rely on MO: Security Profile, MO: Default Operating Unit, and MO: Operating Unit profile options when setting the organization context because the operating unit must be validated before initiating the workflow.