KBACE Technologies

1
Introduction to Multi-Org-Access-Control (MOAC)
R12
2
Concepts of MOAC

• MOAC allows users of a single responsibility to
  perform task in multiple operating units without
  having to switch responsibilities.

3
Shared Service Center?
Shared
Supplied to multiple business units
Service of Transaction Management
Service
Center
From a centralized Operations
4
Goal Shift from Local to Global Systems

• Consolidate IT business operations
• Implement Self-Service Operations
• Drive down transaction costs

5
11i
Access by Changing Responsibility
6
R12
Access without Changing Responsibility
7
Key to Shared Service Center efficiency

• Separate data and function security
• Access many operating units to one responsibility
• Operating unit selected or derived for
  transactions

8
Demo of MOAC w.r.t Profile Options
9
Access Control Architecture

• Multiple Organization Architecture was first
  introduced in Release 10.6, for data security by
  Operating Unit.
• In Release 10.7, a column, ORG_ID was added and
  tables renamed as _ALL
• (VPD)Virtual Private Database feature - attaches
  predicates for the security policies to every SQL
  statement against the database objects where
  policies are applied. MOAC Utilized this feature
  for access control in R12

10
Access Control Architecture 11i Vs R12
11i
R12
11
Some useful Objects / APIs/Tables

• New Tables
• MO_GLOB_ORG_ACCESS_TMP - Table
• FND_MO_PRODUCT_INIT - Table
• Security Profile
• MO Security Profile
• MO Default Operating Unit
• Multi-Org APIs
• MO_GLOBAL.Check_Access
• MO_GLOBAL.get_ou_name
• MO_GLOBAL.check_valid_org
• Policy Context API
• MO_GLOBAL.set_policy_context Procedure
• MO_GLOBAL.get_current_org_id Function
• MO_GLOBAL.get_access_mode Function
• Org Defaulting APIs
• MO_UTILS.Get_Default_OU Procedure
• MO_UTILS.get_default_org_id Function

12
New APIs
Public API Name Feature Area
mo_global.init Initialize multiple organizations context.
mo_global.jtt_init Initialize multiple organizations for JTT based application.
mo_global.is_multi_org_enabled Check if the Multiple Organizations instance is enabled.
mo_global.check_access Check if the operating unit is accessible.
mo_global.get_ou_name Get the operating unit name.
mo_global.check_valid_org Check if the organization is valid.
mo_global.set_policy_context Set the application policy context.
mo_global.get_current_org_id Get the current organization ID in the application context.
mo_global.get_access_mode Get the application context mode.
13
Public API Name Feature Area
mo_global.get_ou_count Get the operating unit count on the access control list.
mo_global.get_valid_org Get the current default/valid organization.
mo_global.validate_orgid_pub_api Get the default organization and check if the organization ID is valid. Used by public APIs only.
mo_global.is_mo_init_done Check if multiple organizations is initialized.
mo_utils.get_ledger_name Returns the ledger name.
mo_utils.get_ledger_info Returns information about the ledger.
mo_utils.get_default_ou Gets the default operating unit from MO Default Operating Unit profile or from current organization.
mo_utils.get_default_org_id Returns the organization ID of the default operating unit.
mo_utils.check_org_in_sp Checks if the specified organization is present in the security profile.
mo_utils.check_ledger_in_sp Checks if all operating units of a ledger is included in the security profile.
mo_utils.check_org_name Returns the operating unit name for an organization ID.
mo_utils.get_orgid_fr_ledger Returns the operating unit ID and the number of operating units in the given ledger.
14
Example 1 The view definition of single
organization view RA_BATCHES is shown below in
the example.
CREATE OR REPLACE VIEW RA_BATCHES AS SELECT "BATCH_ID", LAST_UPDATE_DATE", "LAST_UPDATED_BY", "CREATION_DATE", ... "ORG_ID" , "PURGED_CHILDREN_FLAG" , "ISSUE_DATE" , "MATURITY_DATE" , "SPECIAL_INSTRUCTIONS" , "BATCH_PROCESS_STATUS" , "SELECTION_CRITERIA_ID" FROM RA_BATCHES_ALL WHERE NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)

• This single organization view RA_BATCHES must be
  replaced by a synonym as given below

CREATE SYNONYM RA_BATCHES FOR AR.RA_BATCHES_ALL

• The summary of changes that must be done for
  single organization views joined to single _ALL
  table are given below
• Drop the single organization view
• Create a synonym with the same name as the
  obsolete single organization view
• Attach policy function to the synonym

15
Attach Security Policy to your Tables
BEGIN FND_ACCESS_CONTROL_UTIL.Add_Policy
( p_object_schema gt '1', --
Apps user name p_object_name gt
'FINANCIALS_SYSTEM_PARAMETERS',
p_policy_name gt 'ORG_SEC,
p_function_schema gt '1', -- Apps user name
p_policy_function gt
'MO_GLOBAL.ORG_SECURITY',
p_statement_types gt 'SELECT, INSERT, UPDATE,
DELETE', p_update_check gt TRUE,
p_enable gt TRUE,
p_static_policy gt FALSE) END
16
MO_GLOBAL.Org_Security function
FUNCTION org_security(obj_schema VARCHAR2, obj_name VARCHAR2) RETURN VARCHAR2 IS BEGIN -- -- Returns different predicates based on the access_mode -- The codes for access_mode are -- M - Multiple OU Access -- A - All OU Access -- S - Single OU Access -- Null - Backward Compatibility - CLIENT_INFO case IF g_access_mode IS NOT NULL THEN IF g_access_mode 'M' THEN RETURN 'EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa.organization_id org_id)' ELSIF g_access_mode 'A' THEN -- for future use RETURN NULL ELSIF g_access_mode 'S' THEN RETURN 'org_id sys_context(''multi_org2'',''current_org_id'')' END IF ELSE RETURN 'org_id substrb(userenv(''CLIENT_INFO''),1,10)' END IF END org_security
17
Register Multi-Org Access Enabled in MO table

• Product teams must register their product in the
  Multi-Org table FND_MO_PRODUCT_INIT to indicate
  that Multi-Org Access Control is enabled
• To enable access FND_MO_PRODUCT_INIT_PKG.registe
  r_application('XXXCHR', SEED,Y)
• To delete your application entry
  FND_MO_PRODUCT_INIT_PKG.remove_application('XXXCH
  R')

Column Name Size Type Rqd Value
APPLICATION_SHORT_NAME 80 VARCHAR2 Yes XXXCHR
PRODUCT_DEPENDENCY 80 VARCHAR2 Yes MO
18
Forms Enhancements

• Multi-Org Initialization
• include the following code as given below in the
  PRE-FORM trigger
• BEGIN APP_STANDARD.EVENT(PRE-FORM)
  MO_GLOBAL.init (XXXCHR)
• END

19
Multi-Org Initialization

• IMPORTANT AOL initialization (fnd_global.apps_ini
  tialize()) is executed by the app_standard.event()
  call in the Pre-Form trigger. Multi-Org
  initialization should be executed after this
  call.
• Critical setup
• MO Security Profile
• MO Operating Unit
• MO Default Operating Unit

20
Exercise
Step1 1) select from MO_GLOB_ORG_ACCESS_TMP
2) select hr.name operating_unit,
hr.organization_id org_id from
hr_operating_units hr where
mo_global.check_access(hr.organization_id)'Y
3) select from ar_system_parameters
21
Step2 1) begin FND_GLOBAL.APPS_INITIALIZE(user_
id gt1013421 ,resp_id
gt 20678,resp_appl_id gt222 ,
security_group_id gt0) end 2) select
from MO_GLOB_ORG_ACCESS_TMP 3) select hr.name
operating_unit, hr.organization_id org_id
from hr_operating_units hr where
mo_global.check_access(hr.organization_id)'Y' 4)
select hr.name operating_unit,
hr.organization_id org_id
,mo_global.check_access(hr.organization_id)
from hr_operating_units hr 5) select from
ar_system_parameters
22
Step3 1) FND_GLOBAL.APPS_INITIALIZE(user_id
gt1013421 ,resp_id gt
20678,resp_appl_id gt222 ,
security_group_id gt0)
mo_global.init('AR') --SQLAP for AP end
check table FND_MO_PRODUCT_INIT for init
parameter 2) select from MO_GLOB_ORG_ACCESS_T
MP 3) select hr.name operating_unit,hr.organizat
ion_id org_id from hr_operating_units hr
where mo_global.check_access(hr.organization_id)
'Y 4) select from ar_system_parameters