Oracle R12 Report (XMLP) Developers - MOAC

The Information has been drawn solely from Development Experience and some of the standard Oracle documents. These Tips are useful to the Developers to have the best practices maintained while developing Oracle Reports/SQL/PLSQL/XMLP in MOAC environment.

The Access Control feature in Release 12 allows the user to enter or query records in one or more operating units without changing application responsibility. Hence in almost all the reports a new parameter Operating Unit has been added. If user provides the value for this parameter then Report will be executed for that parameter only, but in case user left that blank report will execute for all the OU.

Also in R12 all the operating Unit sensitive view (Oracle 11i Multi-org) has been replaced with the secured synonyms. That means in R12 table with _ALL is no longer be a view defined on base table as Oracle have used the convention earlier, but provided a synonym which points to _ALL table. The data is restriction 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.

Example:

1.      A table is created in ONT Schema, named OE_ORDER_HEADERS_ALL.

2.      A synonym named OE_ORDER_HEADERS_ALL is created in APPS schema, referring to ONT.OE_ORDER_HEADERS_ALL

3.      Another synonym named OE_ORDER_HEADERS is created in APPS, referring to OE_ORDER_HEADERS_ALL

4.      A Row Level security is applied to OE_ORDER_HEADERS, using package function MO_GLOBAL.ORG_SECURITY.
This can be double-checked by running SQL 

SELECT *

FROM all_policies

WHERE object_name='OE_ORDER_HEADERS';

ü  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

The effect of this policy is that,whenever you access OE_ORDER_HEADERS, Oracle RLS will dynamically append WHERE CLAUSE similar to below

SELECT *

FROM OE_ORDER_HEADERS

WHERE EXISTS

(SELECT 1 FROM mo_glob_org_access_tmp oa

  WHERE oa.organization_id = org_id)

So the following points to be considered before starting report development:

1.      Oracle Report & PL/SQL Stored Procedure:

Case 1: Access Control is limited to only one Operating Unit.

In that case Access Mode would be – Single OU (S). An example is when a user can access to only one operating unit through the MO: Security Profile or the MO: Security Profile is not set and the user access depends on MO: Operating Unit.

x_org_id:= MO_GLOBAL.GET_CURRENT_ORG_ID

MO_GLOBAL.SET_POLICY_CONTEXT(‘S’, x_org_id);

SELECT order_number FROM oe_order_headers

Or you can use

SELECT order_number

FROM oe_order_headers_all

WHERE org_id = x_org_id

Case 2: Access control is enabled with access to multiple operating units:

The security profile provides access to multiple operating units. In that case Access Mode would be – Multiple OU (M). The profile options MO: Security Profile or MO: Operating Unit populates the multiple organizations global temporary table in run time. The profile option MO: Security Profile takes precedence over MO: Operating Unit.

The following SQL is used to get 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;

And accordingly you have to execute

MO_GLOBAL.SET_POLICY_CONTEXT(‘M’, x_org_id)

In that case x_org_id NOT APPLACABLE if Access Mode = M

SELECT order_number

FROM oe_order_headers

is modified at runtime if the responsibility can access multiple operating units to:

SELECT order_number FROM oe_order_headers

WHERE EXISTS

(SELECT 1 FROM mo_glob_org_access_tmp oa

WHERE oa.organization_id = org_id))

or is modified at runtime if the user’s access responsibility can access one Operating Unit with access control enabled for the module to:

SELECT order_number FROM oe_order_headers

WHERE org_id =  sys_context('multi_org2','current_org_id')

2.      Single Organization Concurrent Programs:

The Operating unit mode for single organization concurrent programs is flagged as 'SINGLE' in the Define Concurrent Programs form. The special parameter – Operating Unit is available for a single request as well as request sets. The operating unit is a required field and the default value is derived from the MO_UTILS.get_default_org_id() API

When a user selects an operating unit, Oracle Applications invokes the Multiple Organizations API MO_GLOBAL.set_policy_context() to set the VPD policy context and ensures that a simple equality predicate is used for the policy function and the organization specific value sets (based on secured synonyms) returns data without any changes to the code.

Note: The multiple organizations initialization routine populates the temporary table depending on the application that owns the concurrent program.

3.      In SQL:

To Set Org Context and global Initializations

FND_GLOBAL.APPS_INITIALIZE (x_user_id,x_responsibility_id, x_responsibility_application_id);
MO_GLOBAL.INIT(‘x_appl_short_name’);

MO_GLOBAL.SET_POLICY_CONTEXT(‘S’, x_org_id); --For Specific OU

You can get these values through this query

SELECT

user_id,

responsibility_id,

responsibility_application_id,

security_group_id

FROM fnd_user_resp_groups

WHERE user_id =

(SELECT user_id FROM fnd_user

WHERE user_name = ‘&user_name’)

AND responsibility_id =

(SELECT responsibility_id

 FROM fnd_responsibility_vl

 WHERE responsibility_name = ‘&resp_name’);

Note: Please see below summarize Note for MO_GLOBAL API & MOAC Profile options.

MO_GLOBAL API

Name	Type	Details
SET_POLICY_CONTEXT	Procedure	p_access_mode p_org_id number This procedure will set the access mode, and when applicable the current operating unit context.
SET_ORG_ACCESS	Procedure	p_org_id_char p_sp_id_char p_appl_short_name 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".
INIT	Procedure	p_appl_short_name Pass 'S' OR 'M' to initialize the OU for single or Multiple OU context 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”, this procedure calls set_org_access to establish the multi-org context for the session. To call this from within SQL, the profile option context should have been initialized for the session.
GET_CURRENT_ORG_ID	Function	Returns the current operating unit setting. This should be null if the access mode is not 'S'
GET_ACCESS_MODE	Function	Returns the current access mode value (Access mode S, M or A) stored in the application Context.

MOAC Profile Options:

Profile Name	Usages
MO: Security Profile	The MO Security Profile controls the list of operating units that a responsibility or user can access. If you set the security profile at the responsibility level, then all users using that responsibility will have access to only the operating units available in the security profile. If you set the security profile at the user level, then the user will have access to only those operating units, irrespective of application responsibility that they log into.
MO: Default Operating Unit	The MO: Default Operating Unit is optional and allows you to specify a default operating unit that defaults when you open different subledger application pages. Because you can access multiple operating units, you may want to set up a default one instead of forcing users to constantly have to choose one. User Preferences allows you to specify a default operating unit at the user level. Use the MO: Default Operating Unit profile option to set the operating unit context or default operating unit when accessing an applications.
MO: Operating Unit	MO: Operating Unit profile option is for backwards compatibility and to support products that do not use Multiple Organizations. The release 11i setting was for this is preserved during upgrade. The Release 11i MO: Operating Unit profile option is also supported in Release 12.