White Paper on
R12 MOAC
(Multi Org Access Control)
White Paper on MOAC
Document Control
Change Record
3
Date Author Version Change Reference
Reviewers
Name Position
Distribution
Copy No. Name Location
1
2
3
4
Page 2 of 17
White Paper on MOAC
TABLE OF CONTENTS
DOCUMENT CONTROL.....................................................................................................................1
INTRODUCTION 4
OBJECTIVE.......................................................................................................................................4
PRE REQUISITE................................................................................................................................4
DOCUMENT USAGE.........................................................................................................................4
DETAILS OF DOCUMENT..................................................................................................................4
1. OVERVIEW...............................................................................................................................4
1.1 WHAT IS MOAC?.....................................................................................................................4
1.2 ADVANTAGE OF MOAC IN ORACLE APPLICATION R12:.........................................................5
1.3 PRE-R12 MULTI-ORG ARCHITECTURE.....................................................................................5
1.4 R12 MULTI-ORG ARCHITECTURE.............................................................................................6
2. EXAMPLES...............................................................................................................................6
2.1 IN SQL PRE R12 HOW TO GET THE ORG_ID.......................................................................7
2.2 IN SQL R12 HOW TO GET THE ORG_ID.............................................................................7
2.3 EXAMPLE FOR QUERY.........................................................................................................7
3. SETUPS FOR SECURITY PROFILES..........................................................................................13
3.1 PROFILE OPTIONS..............................................................................................................14
4 Concurrent programs............................................................................................................15
Page 3 of 17
White Paper on MOAC
Introduction
Objective
This white paper assists how to use the Multi org access control in the oracle application R12.
The document explains the Multi Org Access Control functionality and usage.
Pre Requisite
The pre requisite to understand this document is to have the working knowledge of AOL,
Oracle, Reports 6i and Oracle Application R12.
Document Usage
• This document can be used for Comparison of the Pre-R12 and R12 Multi-Org
Architectures.
• What you need to know now when using tools against an R12 MOAC DB
• MOAC Setups
Details of Document
1. Overview
This document describes the process of true multi org access control in Oracle Applications
R12.
1.1 What is MOAC?
• Responsibilities are assigned a Security Profile which is a group of Operating Units
• Assignment is through the profile option ‘MO: Security Profile’ set at the
Responsibility Level.
Page 4 of 17
White Paper on MOAC
Responsibility
Operating Units
1.2 Advantage of MOAC in Oracle Application R12:
The following are the advantages of MOAC:
One responsibility you can perform transactions and report on transactions from
multiple operating units.
Users have to be very careful and disciplined while using MOAC
Even though MOAC is available changing responsibilities to change operating units
has some benefits.
R12 implements MOAC through DB Synonyms that replace the old Multi-Org Views.
For Example:
ONT Database User Apps Database User
OE_ORDER_HEADERS_ALL OE_ORDER_HEADERS
1.3 Pre-R12 Multi-Org Architecture
Base data tables exist in the product schema with a naming convention of %_ALL. The data
in this table is striped by ORG_ID (Operating Unit).
A view in the APPS schema provides the Multi-Org filtering based on the statement below in
the where clause.
SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10)
ONT DB User APPS DB User
Base Table View
OE_ORDER_HEADERS_ALL OE_ORDER_HEADERS
Page 5 of 17
White Paper on MOAC
1.4 R12 Multi-Org Architecture
Base data tables exist in the product schema with a naming convention of %_ALL. The data
in this table is striped by ORG_ID (Operating Unit).
A synonym in the APPS schema provides the Multi-Org filtering based the Virtual Private
Database feature of the Oracle 10G DB Server.
ONT DB User APPS DB User
Base Table Synonym
OE_ORDER_HEADERS_ALL OE_ORDER_HEADERS
2. Examples
Security Profile and Operating Units
62 – 299 (Canada)
63 – 2 (US)
64 – 299, 2 (North America)
Sample Query
SELECT ORG_ID, count(*)
FROM OE_ORDER_HEADERS
GROUP BY ORG_ID;
Security Profile = 62 (Canada)
299, 1000
Security Profile = 63 (US)
2, 7000
Security Profile = 64 (North America)
299, 1000
2.1 In SQL Pre R12 how to get the Org_id
Pre-R12 you could set your SQL session context for multi-org with the following:
BEGIN
dbms_application_info.set_client_info(2);
END;
Page 6 of 17
White Paper on MOAC
2.2 In SQL R12 how to get the Org_id
In R12 you can set your SQL session context for a single OU with the following:
BEGIN
execute mo_global.set_policy_context('S',2);
END;
• The ‘S’ means Single Org Context
• 2 is the ORG_ID I want set
2.3 Example for Query
BEGIN
fnd_global.apps_initialize( user_id => 1331
, resp_id =>20639
, resp_appl_id =>200);
mo_global.init('SQLAP');
END;
BEGIN
MO_GLOBAL.SET_POLICY_CONTEXT('S',86); --398
END;
SELECT rct.trx_number "invoice no"
, hca.account_number "customer no"
, hp.party_name "customer name"
--, rct.ct_reference "project/departyment"
, rct.interface_header_attribute1 "project/departyment"
, rctt.NAME "invoice type"
, rct.trx_date "invoice date"
, aps.due_date "due date"
,TO_CHAR(TO_DATE(rct.interface_header_attribute8,'YYYY/MM/DD HH24:MI:SS'),'DD-
MON-YY') "Tax Point Date"
, ROUND(NVL(aps.exchange_rate,1)* NVL(aps. amount_due_original,0),2) "original
amount"
, rct.customer_trx_id
, aps.payment_schedule_id
, rctt.TYPE
, NVL(CASE
Page 7 of 17
White Paper on MOAC
WHEN (:p_as_of_date) <= TO_DATE(aps.due_date,'DD-MON-YY')
THEN NVL(aps.amount_due_remaining,0)* NVL(aps.exchange_rate,1)
END,0) "Current"
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - TO_DATE(aps.due_date,'DD-
MON-YY'))
BETWEEN 1 AND 30 THEN NVL(aps.amount_due_remaining,0)*
NVL(aps.exchange_rate,1)
END,0) "1-30"
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - TO_DATE(aps.due_date,'DD-
MON-YY'))
BETWEEN 31 AND 60 THEN NVL(aps.amount_due_remaining,0)*
NVL(aps.exchange_rate,1)
END,0) "31-60"
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - TO_DATE(aps.due_date,'DD-
MON-YY'))
BETWEEN 61 AND 90 THEN NVL(aps.amount_due_remaining,0)*
NVL(aps.exchange_rate,1)
END,0) "61-90"
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - TO_DATE(aps.due_date,'DD-
MON-YY'))
BETWEEN 91 AND 120 THEN NVL(aps.amount_due_remaining,0)*
NVL(aps.exchange_rate,1)
END,0) "91-120"
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - TO_DATE(aps.due_date,'DD-
MON-YY'))
BETWEEN 121 AND 150 THEN NVL(aps.amount_due_remaining,0)*
NVL(aps.exchange_rate,1)
END,0) "121-150"
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - TO_DATE(aps.due_date,'DD-
MON-YY'))
BETWEEN 151 AND 180 THEN NVL(aps.amount_due_remaining,0)*
NVL(aps.exchange_rate,1)
END,0) "151-180"
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - TO_DATE(aps.due_date,'DD-
MON-YY'))
> 180 THEN NVL(aps.amount_due_remaining,0)* NVL(aps.exchange_rate,1)
END,0) ">180"
, NVL(CASE
Page 8 of 17
White Paper on MOAC
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - TO_DATE(aps.due_date,'DD-
MON-YY'))
> 180 THEN aps.tax_remaining
END,0) ">180 Tax"
FROM ra_customer_trx rct
, ra_cust_trx_line_gl_dist rctlg
, gl_code_combinations gcc
, ar_payment_schedules aps
, ra_cust_trx_types rctt
, hz_cust_accounts hca
, hz_parties hp
, hr_operating_units hou
, pa_projects ppa
WHERE rct.customer_trx_id = aps.customer_trx_id
AND rct.customer_trx_id = rctlg.customer_trx_id
AND rctlg.code_combination_id = gcc.code_combination_id
AND aps.cust_trx_type_id = rctt.cust_trx_type_id
AND rct.org_id = aps.org_id
AND rct.org_id = rctt.org_id
AND rct.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rct.org_id = hou.organization_id
--AND rct.ct_reference = ppa.project_id
--AND rct.ct_reference = TO_CHAR(ppa.project_id)
AND UPPER(rct.interface_header_attribute1) = UPPER(ppa.segment1)
AND ppa.carrying_out_organization_id =
NVL(:p_carrying_out_organization_id,ppa.carrying_out_organization_id)
AND ppa.project_id BETWEEN NVL(:p_project_from,ppa.project_id) AND
NVL(:p_project_to,ppa.project_id)
AND gcc.segment3 BETWEEN NVL(:p_department_from,gcc.segment3) AND
NVL(:p_department_to,gcc.Segment3)
AND hp.party_id BETWEEN NVL(:p_customer_from,hp.party_id) AND
NVL(:p_customer_to,hp.party_id)
AND hou.organization_id = NVL(:p_opco,hou.organization_id)
AND rctt.type = NVL(:p_invoice_class,rctt.type)
AND NVL(:p_project,'Project') = 'Project'
AND
xxbb_rep_balanc_pkg.get_sum_amount_f(aps.payment_schedule_id,rct.customer_trx_id,rctt.t
ype,aps.exchange_rate,aps.amount_due_original)!=0
GROUP BY rct.trx_number
, hca.account_number
, hp.party_name
, rct.interface_header_attribute1
, rctt.name
, rct.trx_date
, aps.due_date
Page 9 of 17
White Paper on MOAC
, rct.interface_header_attribute8
, aps.amount_due_original
, aps.amount_due_remaining
, aps.tax_remaining
, rct.customer_trx_id
, aps.payment_schedule_id
, aps.exchange_rate
, rctt.type
UNION ALL
SELECT rct.trx_number "invoice no"
, hca.account_number "customer no"
, hp.party_name "customer name"
--, rct.ct_reference "project/departyment"
, rct.interface_header_attribute1 "project/departyment"
, rctt.NAME "invoice type"
, rct.trx_date "invoice date"
, aps.due_date "due date"
,TO_CHAR(TO_DATE(rct.interface_header_attribute8,'YYYY/MM/DD HH24:MI:SS'),'DD-
MON-YY') "Tax Point Date"
, ROUND(NVL(aps.exchange_rate,1)* NVL(aps. amount_due_original,0),2) "original
amount"
, rct.customer_trx_id
, aps.payment_schedule_id
, rctt.type
, NVL(CASE
WHEN (:p_as_of_date) <= TO_DATE(aps.due_date,'DD-MON-YY')
THEN NVL(aps.amount_due_remaining,0)* NVL(aps.exchange_rate,1)
END,0) "Current"
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - TO_DATE(aps.due_date,'DD-
MON-YY'))
BETWEEN 1 AND 30 THEN NVL(aps.amount_due_remaining,0)*
NVL(aps.exchange_rate,1)
END,0) "1-30"
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - TO_DATE(aps.due_date,'DD-
MON-YY'))
BETWEEN 31 AND 60 THEN NVL (aps.amount_due_remaining,0)*
NVL(aps.exchange_rate,1)
END,0) "31-60"
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - TO_DATE(aps.due_date,'DD-
MON-YY'))
BETWEEN 61 AND 90 THEN NVL(aps.amount_due_remaining,0)*
NVL(aps.exchange_rate,1)
END,0) "61-90"
Page 10 of 17
White Paper on MOAC
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - TO_DATE(aps.due_date,'DD-
MON-YY'))
BETWEEN 91 AND 120 THEN NVL(aps.amount_due_remaining,0)*
NVL(aps.exchange_rate,1)
END,0) "91-120"
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - TO_DATE(aps.due_date,'DD-
MON-YY'))
BETWEEN 121 AND 150 THEN NVL(aps.amount_due_remaining,0)*
NVL(aps.exchange_rate,1)
END,0) "121-150"
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - to_date(aps.due_date,'DD-MON-
YY'))
BETWEEN 151 AND 180 THEN NVL(aps.amount_due_remaining,0)*
NVL(aps.exchange_rate,1)
END,0) "151-180"
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) - to_date(aps.due_date,'DD-MON-
YY'))
> 180 THEN NVL(aps.amount_due_remaining,0)* NVL(aps.exchange_rate,1)
END,0) ">180"
, NVL(CASE
WHEN CEIL(TRUNC(TO_DATE(:p_as_of_date)) -to_date(aps.due_date,'DD-MON-
YY'))
> 180 THEN aps.tax_remaining
END,0) ">180 Tax"
FROM ra_customer_trx rct
, ra_cust_trx_line_gl_dist rctlg
, gl_code_combinations gcc
, ar_payment_schedules aps
, ra_cust_trx_types rctt
, hz_cust_accounts hca
, hz_parties hp
, hr_operating_units hou
WHERE rct.customer_trx_id = aps.customer_trx_id
AND rct.customer_trx_id = rctlg.customer_trx_id
AND rctlg.code_combination_id = gcc.code_combination_id
AND aps.cust_trx_type_id = rctt.cust_trx_type_id
AND rct.org_id = aps.org_id
AND rct.org_id = rctt.org_id
AND rct.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rct.org_id = hou.organization_id
AND rctt.type = NVL(:p_invoice_class,rctt.type)
Page 11 of 17
White Paper on MOAC
AND gcc.segment3 BETWEEN NVL(:p_department_from,gcc.segment3) AND
NVL(:p_department_to,gcc.Segment3)
AND hp.party_id BETWEEN NVL(:p_customer_from,hp.party_id) AND
NVL(:p_customer_to,hp.party_id)
AND hou.organization_id = NVL(:p_opco,hou.organization_id)
AND UPPER(rctt.NAME) NOT LIKE 'PROJECT%'
AND (:p_project != 'Project' OR :p_project IS NULL)
AND
xxbb_rep_balanc_pkg.get_sum_amount_f(aps.payment_schedule_id,rct.customer_trx_id,rctt.t
ype,aps.exchange_rate,aps.amount_due_original)!=0
--AND rct.trx_number='10102'
GROUP BY rct.trx_number
, hca.account_number
, hp.party_name
, rct.interface_header_attribute1
, rctt.NAME
, rct.trx_date
, aps.due_date
, rct.interface_header_attribute8
, aps.amount_due_original
, aps.amount_due_remaining
, aps.tax_remaining
, rct.customer_trx_id
, aps.payment_schedule_id
, aps.exchange_rate
, rctt.type;
/
How to find the Security Profiles
The following SQL will dump out 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;
Security Profile From Profile Option
The following SQL will dump out the Security Profiles assigned in via the Profile Options
Page 12 of 17
White Paper on MOAC
SELECT
fnd_profile.value_specific('XLA_MO_SECURITY_PROFILE_LEVEL',
user_id,
resp_id,
appl_id)
from dual;
Where:
user_id: FND_USER.USER_ID
resp_id: FND_RESPONSIBILITY_TL.RESPONSIBILITY_ID
appl_id: FND_APPLICATIONS.APPLICATION_ID
3. Setups for Security profiles
The Security Profiles form allows you to group together Operating Units.
Page 13 of 17
White Paper on MOAC
3.1 Profile Options
.
There are three Profile Options you need to be aware of related to Multi-Org that
should be set at the Responsibility Level.
The R12 profile option ‘MO: Security Profile’ is always evaluated first.
The pre-R12 profile option ‘MO: Operating Unit’ still works in R12. It is just a
secondary priority being evaluated after ‘MO: Security Profile’.
The R12 profile option ‘MO: Default Operating Unit’ sets the default Operating
Unit for transactions when running under a Security Profile
Pre-R12 ‘MO: Operating Unit’
Many R12 applications modules do not work with ‘MO: Security Profile’ set for a given
responsibility.
• They must only use ‘MO: Operating Unit’.
• Some even require all three Profile Options set.
Page 14 of 17
White Paper on MOAC
4 Concurrent programs.
• Oracle has implemented a new parameter on the System Administration: Concurrent
Parameters form to control how to handle Operating Units.
Go to System Administration Responsibility
Page 15 of 17
White Paper on MOAC
The Operating Unit Mode parameter does not show up in the System Administrator:
Define Concurrent Programs form.
Page 16 of 17
White Paper on MOAC
• The Operating Unit Mode parameter is not always set properly by Oracle
Development during an upgrade.
• This setting has three values:
NULL – default setting
Single – run only for a specific Operating Unit specified by ‘MO: Operating Unit’
profile option
Multi – run for multiple Operating Units based on the ‘MO: Security Profile’ profile
option
Change this setting from the default NULL setting to Single to see if this resolves
report execution errors.
• This Operating Unit Mode parameter is used to identify:
– How the program executes the multiple organizations initialization
– When to display Operating Unit prompt in the Submit Requests window and
Schedule Requests window.
• This impacts how the Submit Requests form evaluates Concurrent Request Parameter
List of Values. If you don’t see what you want try changing this setting.
Page 17 of 17
