How To Setup And Check MO / MOAC Setups In a R12 APPS Instance - Precedence of MO / MOAC Profile Options [ID 745420.1]

Applies to:

Oracle Trade Management

Goal

The purpose of this document is how to setup and check MO/MOAC setups in a R12 APPS Instance.

 

This note had been created after some discussion with Trade Management Development
Even if these advices are valuable in scope of global eBusinessSuite , Service Requests which 
would be created after reading this note should be assigned to each module or to Install ATG group
for a specific MultiOrg installation issue.

 

eBusiness Suite started using this feature in R12, MOAC stands for Multi-Organization Access Control.

This document will cover the following topics:

1. How to setup MO/MOAC in a R12 APPS Instance?
2. What are the Defaulting Rules between different Profile Options? 
3. How to check MO/MOAC setups in a R12 APPS Instance?

Note: The example setup steps are based on the Trade Management Application.

Solution

1. Setting up MOAC (Multi-Organization Access Control)

1. Define Security Profile for MOAC (Multi-Organization Access Control)
   • HRMS Super User Responsibility > Security > Define Security Profile. 
   • Enter name 'XX-TM-SECURITY-PROFILE'. 
   • Enter 'Vision Corporation' for 'Business Group'. 
   • Select 'Organization Security' tab 
   • For 'Security Type', select 'Secure organizations by organization hierarchy and/or organization list' 
   • 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
   • Processes & Reports > Submit Process & Report.
   • Select 'Security List Maintenance' for request name. 
   • Enter the following:
     • Generate lists for = One Named Security Profile
     • Security Profile = 'XX-TM-SECURITY-PROFILE'
   • Submit the request and wait until it completes. 
3. Assign TM Responsibilities to User
   • System Administrator Responsibility > Security : User > Define
   • Query / add the User you are going to use with Trade Management (TM)
   • Add the following direct responsibilities
     • Oracle Trade Management User
     • Oracle Trade Management Administrator
   • Assign Security Profile to Responsibilities
     • Profile > System
     • 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
     • Note: 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 in MOAC, and also leave MO: Default Operating Unit at null (ie no setup at any level) which is a possible option.

 

2. R12 MOAC defaulting / precedence rules

 Bellow is the logic that determines the precedence (priority) between MO and MOAC profile options:

1. 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.

 

3. Verifying MOAC Setups 

To check Organizations that 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 %'

 

 

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;

 

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;

 

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;

 

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;