How to setup and check MO/MOAC setup in APPS instance at R12 Level
Ebusiness Suite started using this feature at R12 level
MOAC (Multi-Organization Access Control)
A) How To Setup MO / MOAC Setup In APPS Instance At R12 Level ?
B) What Are The Defaulting Rules Between Different Profile Options
C) How To Check MO / MOAC Setup In APPS Instance At R12 Level ?
Here is the solution for the above questions.
Solution 1:
Setup MOAC (Multi-Organization Access Control) :
1) Define Security Profile for MOAC (Multi-Organization Access Control)
a) HRMS Super User Responsibility > Security > Define Security Profile.
b) Enter name ‘XX-TM-SECURITY-PROFILE’.
c) Enter ‘Vision Corporation’ for ‘Business Group’.
d) Select ‘Organization Security’ tab,
e) For ‘Security Type’, select ‘Secure organizations by organization hierarchy and/or organization list’
f) Enter rows with the following organization names with Classification ‘Operating Unit’ :
- Vision Operations
- Vision Corporation
- Vision Services
- Vision Utilities
2) Run Security List Maintenance request
a) Processes & Reports > Submit Process & Report.
b) Select ‘Security List Maintenance’ for request name.
c) Enter the followings :
- Generate lists for = One Named Security Profile
- Security Profile = ‘XX-TM-SECURITY-PROFILE’
d) Submit the request and wait until it completes.
3) Assign TM Responsibilities to User
a) System Administrator Responsibility > Security : User > Define.
b) Query/add the User you are going to use with Trade Management ( TM).
c) Add the following direct responsibilities :
- Oracle Trade Management User
- Oracle Trade Management Administrator
4) Assign Security Profile to Responsibilities :
a) Profile > System.
b) Assign values to the following profiles as indicated for each of the above responsibilities :
- MO: Default Operating Unit = Vision Operations (e.g.)
- MO: Security Profile = XX-TM-SECURITY-PROFILE
This will give the responsibilities access to the 4 operating units assigned to the security profile ‘XX-TM-SECURITY-PROFILE’.
Note that a valid configuration is to setup only profile option MO: Security Profile and leave MO: Operating Unit at null (ie no setup at any level) which is mandatory to be in MOAC situation and also leave MO: Default Operating Unit at null (ie no setup at any level) which is a possible option
B) R12 MOAC defaulting / precedence rules:
Here is the process which determines the precedence ( priority)between MO and MOAC profile options
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 only 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 :
* If the profile value “MO: Default Operating Unit” is set, it 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.
* Else there is no defaulted Operating Unit .
* If the Profile Option “MO: Default Operating Unit” is not set, then there is zero (no) default Operating Unit.
C) Run below SQL statements :
1.To check Organization which are related to a profile
select *
from PER_SECURITY_PROFILES PPR,
PER_SECURITY_ORGANIZATIONS PPO
where PPR.security_profile_id = PPO.security_profile_id
and security_profile_name like ‘%&your_security_profile_name %’
2.To check global profile options setup :
select substr(e.profile_option_name,1,35) Profile,
decode(a.level_id,10001,’Site’,10002,’Application’,10003,’Resp’,10004,’User’) L,
decode(a.level_id,10001,’Site’,10002,c.application_short_name,
10003,b.responsibility_name,10004,d.user_name) LValue,
nvl(a.profile_option_value,’Is Null’) Value
from fnd_profile_option_values a, fnd_responsibility_tl b,
fnd_application c, fnd_user d, fnd_profile_options e
where e.profile_option_name in (‘ORG_ID’,'DEFAULT_ORG_ID’, ‘XLA_MO_SECURITY_PROFILE_LEVEL’,'XLA_MO_TOP_REPORTING_LEVEL’)
and e.profile_option_id = a.profile_option_id
and a.level_value = b.responsibility_id (+)
and a.level_value = c.application_id (+)
and a.level_value = d.user_id (+)
order by 1,2;
2. To get detail of profile options setup at user level :
select substr(e.profile_option_name,1,35) Profile,
decode(a.level_id,10001,’Site’,10002,’Application’,10003,’Resp’,10004,’User’) L,
decode(a.level_id,10001,’Site’,10002,c.application_short_name,
10003,b.responsibility_name,10004,d.user_name) LValue,
nvl(a.profile_option_value,’Is Null’) Value
from fnd_profile_option_values a, fnd_responsibility_tl b,
fnd_application c, fnd_user d, fnd_profile_options e
where e.profile_option_name in (‘ORG_ID’,'DEFAULT_ORG_ID’, ‘XLA_MO_SECURITY_PROFILE_LEVEL’,'XLA_MO_TOP_REPORTING_LEVEL’)
and e.profile_option_id = a.profile_option_id
and a.level_value = b.responsibility_id (+)
and a.level_value = c.application_id (+)
and a.level_value = d.user_id (+)
and decode(a.level_id,10001,’Site’,10002,’Application’,10003,’Resp’,10004,’User’) = ‘User’
and decode(a.level_id,10001,’Site’,10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) in (‘&user_name’ ) — enter fnd user name
order by 1,2;
3. To get detail of profile options setup at Site and Application level :
select substr(e.profile_option_name,1,35) Profile,
decode(a.level_id,10001,’Site’,10002,’Application’,10003,’Resp’,10004,’User’) L,
decode(a.level_id,10001,’Site’,10002,c.application_short_name,
10003,b.responsibility_name,10004,d.user_name) LValue,
nvl(a.profile_option_value,’Is Null’) Value
from fnd_profile_option_values a, fnd_responsibility_tl b,
fnd_application c, fnd_user d, fnd_profile_options e
where e.profile_option_name in (‘ORG_ID’,'DEFAULT_ORG_ID’, ‘XLA_MO_SECURITY_PROFILE_LEVEL’,'XLA_MO_TOP_REPORTING_LEVEL’)
and e.profile_option_id = a.profile_option_id
and a.level_value = b.responsibility_id (+)
and a.level_value = c.application_id (+)
and a.level_value = d.user_id (+)
and decode(a.level_id,10001,’Site’,10002,’Application’,10003,’Resp’,10004,’User’) in (‘Site’, ‘Application’)
order by 1,2,3;
4. To get detail of profile options setup at Responsibility level : .
select substr(e.profile_option_name,1,35) Profile,
decode(a.level_id,10001,’Site’,10002,’Application’,10003,’Resp’,10004,’User’) L,
decode(a.level_id,10001,’Site’,10002,c.application_short_name,
10003,b.responsibility_name,10004,d.user_name) LValue,
nvl(a.profile_option_value,’Is Null’) Value
from fnd_profile_option_values a, fnd_responsibility_tl b,
fnd_application c, fnd_user d, fnd_profile_options e
where e.profile_option_name in (‘ORG_ID’,'DEFAULT_ORG_ID’, ‘XLA_MO_SECURITY_PROFILE_LEVEL’,'XLA_MO_TOP_REPORTING_LEVEL’, ‘FND_INIT_SQL’)
and e.profile_option_id = a.profile_option_id
and a.level_value = b.responsibility_id (+)
and a.level_value = c.application_id (+)
and a.level_value = d.user_id (+)
and decode(a.level_id,10001,’Site’,10002,’Application’,10003,’Resp’,10004,’User’) in (‘Resp’)
and upper(decode(a.level_id,10001,’Site’,10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name)) like upper(‘%&resp_name%’)
order by 1,2,3;
