Multi-Org Architecture in Release 12 And Related SQL Queries

SQL Queries and Multi-Org Architecture in Release 12

In Release 12, the architecture of multi-org and the way in which data is partitioned by operating unit has changed significantly. As a result the ways in which data are stored and accessed has changed.
In Previous Releases data was restricted to a single operating unit using views which striped base table data based on the current operating unit setting.
Base tables (generally named with ‘_ALL’, e.g. MY_TABLE_ALL) contained data for all operating units. Each such table contained a column named ORG_ID to indicate what operating unit a particular row belonged to.Data was then restricted by using restricted views (e.g. MY_TABLE) which would only return rows which corresponded to the current operating unit’s organization ID. The current operating unit was stored in the first 10 characters of the database Application Context variable CLIENT_INFO. 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(‘’);
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, MY_TABLE would no longer be a view defined on MY_TABLE_ALL, but rather a synonym which points to MY_TABLE_ALL
The data restriction is accomplished by assigning a virtual private database (VPD) policy to the synonym. This policy allows the system to dynamically generate restricting conditions when queries are run against the synonym.
For example: In release 12 in the APPS schema, PA_PROJECTS and PA_PROJECTS_ALL are both synonyms which point to the table PA.PA_PROJECTS_ALL. However, the view PA_PROJECTS_ALL is unrestricted, whereas, PA_PROJECTS will only display data for the user’s current operating unit(s) because of the VPD policy that has been assigned to it.
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:
Policy_name: ORG_SEC
Policy_group: SYS_DEFAULT
Package: MO_GLOBAL
The function (MO_GLOBAL.ORG_SECURITY) is called with the following parameters:
obj_schema – the object schema, in this case APPS
obj_name – the object name (e.g., MY_TABLE)
The function then returns additional where clause conditions to restrict the data accessible from the object. The structure of this function will dynamically generate conditions which will either:
Restrict the data to a single operating unit if the access mode is Single
Restrict the data to multiple operating units if the access mode is Multiple
Restrict the data to eliminate only seed data rows is the access mode is All
Restrict the data to not return any rows if the access mode is None
The conditions returned in each case are as follows:
Single OU Access
org_id = sys_context(‘multi_org2′,’current_org_id’)
Only data for the current operating unit is accessible. The value of sys_context(‘multi_org2′,’current_org_id’) would have to be set to the current operating unit as described below.
Multiple OU Access
FROM mo_glob_org_access_tmp oa
WHERE oa.organization_id = org_id)
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.
All OU Access
org_id -3113
Seed template records, which are used to create new seed data when a new operating unit is created. are created with an org_id of –3113. So in this mode, only these template records, which do not correspond to any actual operating unit, will be filtered out.
No OU Access
1 = 2
The condition is never satisfied. No data will be returned from the object.
Multi-Org Session Context
The database utility DBMS_SESSION.SET_CONTEXT(, , ) is used to initialize and set the Multi-Org context information for a user’s session. The utility SYS_CONTEXT(, ) is used to retrieve this data. The key context items are:
Namespace Namespace Value
multi_org access_mode S=Single, M=Multiple, A=All, X=None
multi_org2 current_org_id Operating unit org id, only applicable if access mode is Single
For example: The following example shows how you could set the access mode or determine the current setting:
myvar := sys_context(‘multi_org’,’access_mode’);
dbms_output.put_line(‘Access Mode: ‘||myvar);
However, it is generally preferable to use the following wrapper functions from MO_GLOBAL which will call the appropriate utilities to maintain the various elements of the multi-org context:
Procedure: SET_POLICY_CONTEXT(p_access_mode varchar2, p_org_id number)
This procedure will set the access mode, and when applicable the current operating unit context.
Procedure: SET_ORG_ACCESS(p_org_id_char varchar2, p_sp_id_char varchar2, p_appl_short_name varchar2)
This procedure determines if the application specified has multi-org access control enabled, by querying FND_MO_PRODUCT_INIT for the application short name. If this is enabled, and a security profile is specified (p_sp_id_char), then all orgs the user has access to will be populated in MO_GLOB_ORG_ACCESS_TMP. If there are more than one such org, the access method will be set to “Multiple”. Otherwise if no security profile id is specified, it will use the value of p_org_id to set the current operating unit value and set the access mode to “Single”.
Procedure: INIT(p_appl_short_name varchar2)
The procedure used by the applications when starting a new session. Based on the profile options “MO: Operating Unit” (ORG_ID) and “MO: Security Profile” (XLA_MO_SECURITY_PROFILE_LEVEL), this procedure calls set_org_access to establish the multi-org context for the session. To call this from withing SQL, the profile option context should have been initialized for the session.
Returns the current operating unit setting. This should be null if the access mode is not ‘S’
Returns the current access mode value.
For Example:
1) When logging into a SQL session to set the org context as it would be for a particular user in a particular responsibility:
a) If you know the security_profile_id for that responsibility and user, you could call:
execute mo_global.set_org_access(null, , ‘PA’);
Security profiles are stored in PER_SECURITY_PROFILES, and the final parameter is the application short name of the application associated with the responsibility you would be using.
b) If you do not know the security profile or operating unit profile option settings for your user, responsibility and application, you could use code similar to the following to get this information:
l_user_id fnd_user.user_id%type;
l_resp_id fnd_responsibility.responsibility_id%type;
l_appl_id fnd_application.application_id%type;
l_appl_short_name fnd_application_vl.application_short_name%type;
l_ou_value fnd_profile_option_values.profile_option_value%type;
l_sp_value fnd_profile_option_values.profile_option_value%type;
select user_id into l_user_id
from fnd_user
where user_name = upper(‘&user_name’);
select responsibility_id into l_resp_id
from fnd_responsibility_vl
where responsibility_name = (‘&resp_name’);
select application_id, application_short_name into l_appl_id, l_appl_short_name
from fnd_application_vl
where application_short_name = upper(‘&appl_short_name’);
l_ou_value := fnd_profile.value_specific(
‘ORG_ID’,l_user_id, l_resp_id, l_appl_id);
l_sp_value := fnd_profile.value_specific(
‘XLA_MO_SECURITY_PROFILE_LEVEL’, l_user_id, l_resp_id, l_appl_id);
dbms_output.put_line(‘MO: Operating Unit: ‘||l_ou_value);
dbms_output.put_line(‘MO: Security Profile: ‘||l_sp_value);
if l_sp_value is null and l_ou_value is null then
dbms_output.put_line(‘No operating unit or security profile information
mo_global.set_org_access(l_ou_value, l_sp_value, l_appl_short_name);
end if;
exception when others then
dbms_output.put_line(‘Error: ‘||sqlerrm);
2) To set the operating unit context to a single operating unit, you could simply use:
execute mo_global.set_policy_context(‘S’,);
Backwards Compatibility
When running queries on multi-org objects in SQL, you can still use the old CLIENT_INFO settings to gather data and run queries against multi-org objects if the profile option:
MO: Set Client_Info for Debugging (FND_MO_INIT_CI_DEBUG) is set to “Yes”.
When this profile option is set to “Yes” and the global access mode setting is null (as it would be in a SQL*Plus or other client session unless specifically set), the VPD function MO_GLOBAL.ORG_SECURITY will return the following as the additional where clause condition for the object:
org_id = substrb(userenv(‘CLIENT_INFO’),1,10)



Advance Table In OAF,1,AOL Concepts,18,AP Holds,1,Apps Initialization,1,Blanket Purchase Orders,1,Blanket Releases,1,Careers,9,Common Errors and Solutions,2,Concurrent Programs,10,Contract Purchase Orders,1,Corporate Culture,3,Corporate Ladder,3,Customer Interface,3,Customization,1,De-Normalization,1,EAM,1,EBS Tutorials,10,Education,16,Emterprise Asset Management,1,English,1,Enterprise Asset Management,1,Entrepreneur,1,Error Fixes,1,Executables,2,Extension,1,fnd_user,1,FNDLOAD,1,Global Agreements,1,Guest Posts,7,Handy Scripts,63,Health Tips,1,Important Apps Queries,1,Infographic,3,Interview Preparation,57,Interview Q/A,57,Inventory Org ID,1,Iproc,4,Iprocurement,4,IT,3,jDeveloper,1,Job Search,1,Key Tables In Oracle Apps,5,Learning English,1,MDS,1,Metadata Tables,2,Multi Org Architecture,2,Normalization,1,O2C,1,OAF Interview,49,OAF Personalization,1,OAF Tutorials,52,Operating Unit,1,Oracle Alerts,1,Oracle AP,11,Oracle APIs,4,Oracle Application Object Library,19,Oracle Applications,10,Oracle Apps,2,Oracle Apps Interviews,29,Oracle Apps Tips,8,Oracle Apps Tutorials,97,Oracle AR,18,Oracle Assets,2,Oracle Dabase Structure,2,Oracle Database Architecture,2,Oracle Database Key Tables,3,Oracle EAM,2,Oracle EBS,5,Oracle Financials,2,Oracle Forms And Reports,17,Oracle Forms Interview Questions,6,Oracle Forms Tutorials,6,Oracle GL,4,Oracle Holds,1,Oracle HRMS,10,Oracle Human Resources,1,Oracle Inventory,4,Oracle Payables,1,Oracle Payroll,1,Oracle POTutorials,1,Oracle Purchasing,7,Oracle Reports Interview,9,Oracle Reports Tutorials,3,Oracle System Administrator,4,Oracle Workflow,3,Order Management,2,Organization ID,3,Organizations,3,Performance Tuning,2,Personalization,1,PL/SQL Block,1,PL/SQL Interview Questions,11,PL/SQL Scripts,44,PL/SQL Tutorials,53,Planned Purchase Orders,1,PO,1,PO Action History,1,PO_ACTION_HISTORY,1,POracle Purchasing,1,PR,1,Procedures And Functions,2,Profile Options,2,Purchase Orders,1,Purchase Requisition,2,Receipts,1,Request Groups,1,Responsibility,1,Shell Script,1,SOA Tutorials,4,Social Media,1,Software Industry,2,Software Tools,1,SQL And PL/SQL Interview,38,SQL Interview,51,SQL Scripts,38,SQL Tutorials,43,Standard Purchase Orders,1,Troubleshooting,1,Unexpected error,1,Unix/Linux Commands,3,User,1,Value Sets,1,Weblogic,1,XML Publisher,3,
Oracle Apps Guy: Multi-Org Architecture in Release 12 And Related SQL Queries
Multi-Org Architecture in Release 12 And Related SQL Queries
SQL Queries and Multi-Org Architecture in Release 12,Multi-Org Architecture in Release 12 And Related SQL Queries
Oracle Apps Guy
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS CONTENT IS PREMIUM Please share to unlock Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy