Oracle EBS之MO_GLOBAL-Dive into R12 Multi Org Design

I hope you have already read article Basics of Multi Org in R12
. A few
questions come to mind when we think about Multi Org in R12. The best way to
analyse those questions is by opening package MO_GLOBAL. Don't worry if you are
not yet on R12, package MO_GLOBAL is installed 11.5.10 too.Lets get digging.

How is CLIENT_INFO being replaced in
R12?

Lets take an example.
In pre Release 12, you would have had
following methodology for PO_HEADERS_ALL

a. A table is created in PO
Schema, named PO_HEADERS_ALL
b. A synonym named PO_HEADERS_ALL is created in
APPS schema, referring to PO.PO_HEADERS_ALL
c. Create a view PO_HEADERS in
APPS schema, as "select * from po_headers_all where
org_id=client_info"

But now in
R12, following will happen

a. A table is created in PO Schema, named
PO_HEADERS_ALL
b. A synonym named PO_HEADERS_ALL is created in APPS schema,
referring to PO.PO_HEADERS_ALL
c. Another synonym named PO_HEADERS is created
in APPS, referring to PO_HEADERS_ALL
d. A Row Level security is applied to
PO_HEADERS, using package function MO_GLOBAL.ORG_SECURITY.
This can be
double-checked by running SQL select * from all_policies where
object_name='PO_HEADERS'

e. The effect of this policy is that,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)

Also see **** below, latter

Does this mean, if I create a new custom
table, I will have to apply RLS [ Row Level Security ] against Custom table
too?

Yes indeed, if it contains data partitioned by ORG_ID. All you
need to do in such case is to assign package function MO_GLOBAL.ORG_SECURITY to
that table/synonym/view.

Will the
Multi Org Row Level security be applied against the table or the synonym or the
view?

In theory, RLS can be applied against any of the above objects.
However in practice, you will apply RLS against Objects in APPS Schema. This
means, you will most probably apply RLS on Synonyms. Basically, the Multi Org
Views are now replaced by RLS Secured Synonyms.  Hence no code change is 
required  where  the pre-R12  Multi-Org  secured  view was being  accessed. The
responsibility of securing data as per ORG_ID now lies with RLS [also known as
VPD - Virtual Private Database].

I
have made changes to my Multi Org Security Profile, by attaching a new Org
Hierarchy. Do i need to run any process?

Just like we do in HRMS, it
is advised that any changes to Security Profiles must be followed by running
"Security List Maintenance"

What
is MO_GLOBAL.INIT

Purpose of
mo_global.init
:-
It will check if new Multi Org Security Profile is
set, to decide if new Security Profile method will be used.
If the new MO
security profile is set, then mo_global.init inserts
one record, for each
Organization in Org Hierarchy
, in table  mo_glob_org_access_tmp



When & from where is
mo_global.init called ?

This package procedure will be called as soon
as you login or as soon as you switch responsibility. Just like
FND_GLOBAL.INITIALIZE is called. It is safe to assume that Oracle will invoke
MO_GLOBAL.INIT after FND_GLOBAL.INITIALIZE

Is mo_glob_org_access_tmp table a global temporary
table?

Yes, it is. Hence after Multi Org is initialised for your
session, your session will have X number of records in table
mo_glob_org_access_tmp. X is the number of organizations assigned to MO Security
profile [view org hierarchy or org list in security
profile]

What is the purpose of
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

What is the purpose of
MO_GLOBAL.SET_POLICY_CONTEXT ?

This procedure has two
parameters
    p_access_mode
          Pass a value "S" in case you want
your current session to work against Single ORG_ID
          Pass a value of
"M" in case you want your current session to work against multiple
ORG_ID's
    p_org_id
          Only applicable if p_access_mode is passed
value of "S"

In SQL*Plus, I wish to
set my session to work against a specific Org [one single org]. How do I do that
in R12

SQL>> exec MO_GLOBAL.SET_POLICY_CONTEXT('S',101);
In
the above case, ORG_ID 101 will be assigned as current org for your
session.
Internally, following code in blue
will be executed by Oracle when you set your
context to single Org, dbms_session.set_context('multi_org2', 'current_org_id',
101);

**** If the current database session is initialised for
Single Org[as in above step], then Where clause appended to object by
Row-Level-Security will be
WHERE org_id =
sys_context('multi_org2','current_org_id')

Why will I as a Apps Techie ever use
MO_GLOBAL.SET_POLICY_CONTEXT ?

Lets say you wish to call an API to
create invoices in ORG_ID 101. In case the API does not have a parameter for
Org_id, you can do the below
a. exec
MO_GLOBAL.SET_POLICY_CONTEXT('S',101)
b. Call the Invoice API, which will
internally read the ORG_ID from MO Current Context.

From SQL*Plus, I wish to simulate login to a specific
responsibility. How do I do this?

a. Call
FND_GLOBAL.INITIALIZE
This will set your responsibility id, user_id etc
b.
call MO_GLOBAL.INIT
This will read the MO profile option values for your
responsibility/user, and will initialize the Multi Org Access.

 

FROM：http://oracle.anilpassi.com/mo-global-dive-into-r12-multi-org-design.html