R12 Multi Org Access Control (MOAC) Reporting Notes

---------------------------------------------------------------------------------------------------------------------- 

// Context Initialization Procedure for MOAC in Database Session

----------------------------------------------------------------------------------------------------------------------

-- Author: Faraz Anwar

-- Dated : 2010/11/3

//For Multiple Org Context Initialization in R12

BEGIN

MO_GLOBAL.init('AR');

mo_global.set_policy_context('M', 115);

--username, responsibility_id, resp_application_id, security_profile_id, server_id

FND_GLOBAL.APPS_INITIALIZE(1116,50651,222,62,7118);

END;

//For Single Org Context Initialization in R12

BEGIN

MO_GLOBAL.init('AR');

mo_global.set_policy_context('S', 115);

FND_GLOBAL.APPS_INITIALIZE(1116,50651,222,62,7118);

END;

 

// Important Procedures

-- This procedure only works with Security Profiles. MO Operating Unit is not Affected with this procedure

MO_UTILS.check_org_in_sp(p_organization_id, p_class);   

e.g.,

SELECT organization_id, name, MO_UTILS.check_org_in_sp(ORGANIZATION_ID, 'OPERATING_UNIT') accessable FROM HR_OPERATING_UNITS;

-- Returns Y or N depending on whether user has access to given organization

mo_global.check_access(p_organization_id)

e.g.,

SELECT organization_id, name, mo_global.check_access(ORGANIZATION_ID) accessable FROM HR_OPERATING_UNITS;

//Related Tables

-- Lists all the Organizations as per Current Security Context either Orgs Accessable through "MO Operating Unit" or

-- MO Security Profile" profile options

mo_glob_org_access_tmp   

-- Lists products enabled for MOAC

FND_MO_PRODUCT_INIT    

-- Other Misc Tables

PER_SECURITY_PROFILES 

PER_ORGANIZATION_LIST 

// Example Standard Report

Accounts Payable Trial Balance

---------------------------------------------------------------------------------------------------------------------- 

// Multi-Org Reporting in R12 for Custom Reports

----------------------------------------------------------------------------------------------------------------------

// Modify Report Definintion to use either 'Single' or 'Multiple' Operating Unit Mode.

1. Login into application with System Administration responsibility (NOT System Administrator)

2. Navigate: Concurrent -> Programs

3. Query for short name of the concurrent program

4. Click on Update pencil icon of your program

5. Under 'Update Concurrent Program' region, select Request tab

6. Under 'Request Setting' region, select 'Single' from the drop down of 'Operating Unit

Mode' field

7. Save changes by clicking on 'Apply' button.

OR

UPDATE FND_CONCURRENT_PROGRAMS

SET MULTI_ORG_CATEGORY='S'

WHERE CONCURRENT_PROGRAM_NAME = '<your program name>'

Reference Document:

1. Metalink Note: Operating Unit Not Selectable When starting Concurrent Requests For MOAC Responsibility [ID 837615.1]

2. Forum Ref:     http://forums.oracle.com/forums/thread.jspa?threadID=1056143&tstart=60

// Value Set in Report

The Default Value Set provided by Oracle Applications for MOAC based Security is

FND_MO_OU

// Convert your reports to use synonyms created for _ALL Tables

e.g.,

RA_CUSTOMER_TRX_ALL Table has synonym RA_CUSTOMER_TRX for APPS Schema.

This RA_CUSTOMER_TRX synonym has security predicate attached to it using VPD (Virtual Private Database).

OR

// OR If you don't wish to use VPD synonyms directly

If you don't wish to use VPD synonyms directly then as a work around you can join

your _ALL tables org_id column with

   mo_glob_org_access_tmp.organization_id

// Create User Parameter

P_CONC_REQUEST_ID

Datatype: Number

Length 15

// Add following code to After Parameter Form (NOT Before Report Trigger) to Initialize Context

SRW.USER_EXIT('FND SRWINIT');

--You may also put some debug messages here for instance

SRW.MESSAGE(999,'After SRWINIT');

// Add following code in After Report Trigger

SRW.USER_EXIT('FND SRWEXIT');

// VPD References

http://www.devshed.com/c/a/Oracle/RowLevel-Security-with-Virtual-Private-Database/1/

Oracle Applications Multiple Organizations Access Control for Custom Code Doc ID: Note:420787.1