文档 ID 420787.1

White Paper

Oracle Applications Multiple Organizations Access Control for Custom Code

See Change Record

This document discusses how to update the customization code that is affected by the access control feature in Oracle Applications Multiple Organizations.

This document is provided to customers as a reference to analyze and update customization code that is affected by the multiple organizations access control feature. Oracle does not guarantee that customization code may work or is responsible for any issues in the code after you follow the guidelines described in this document as various techniques are used in customization code.

TOC/Navigation Title

This white paper contains the following information.

1. Introduction
2. Technical Details
3. Public APIs
4. Change Record

Introduction

Multiple Organizations architecture (Multi-Org) includes a new feature Multiple Organizations Access Control (MOAC) in Release 12. The Access Control feature is backward compatible, which means that there are no code or procedural changes if MOAC is not implemented (i.e. The user is assigned one operating unit for a responsibility). .

This document assumes that the reader is familiar with Oracle Applications Multiple Organizations architecture. Please contact Oracle Consulting if you need help in upgrading to custom code.

Overview of Multiple Organizations Architecture (prior to Release 12)

The primary objective of multiple organizations architecture, introduced in Oracle Applications Release 10.6, is to secure data from unauthorized access by individuals belonging to different operating units in an enterprise.

A new or fresh installation of an Oracle Applications instance does not automatically enable multiple organizations. The system administrator creates operating units using the Define Organizations window in Oracle Human Resources Management System (HRMS), and runs the Convert to Multiple Organization program from AD Administrator to enable the multiple organizations feature. Typically, the system administrator defines "MO: Operating Unit" profile at Responsibility and/or User level. The "organization_id" of the "MO: Operating Unit" profile option value filters the transactional data. The CLIENT_INFO application context space stores the multiple organizations context value.

Multi-Org views use the following WHERE clause to filter application records:

Overview of Multiple Organizations Access Control Architecture (Release 12)

The Access Control feature in Release 12 allows the user to enter or query records in one or more operating units without changing application responsibility. It is the system administrator’s discretion to either implement the feature or use the same multiple organizations profile option setting available before Release 12 by using the single operating unit mode (i.e. one operating unit for a responsibility).

In Release 12, the multiple organizations context value is no longer initialized by the FND_GLOBAL.APPS_INITIALIZE routine thereby reducing unnecessary context setting and resource consumption for applications that do not use operating unit context for data security.

To use the single operating unit mode, you must set the value for the "Initialization SQL Statement – Custom profile" to "mo_global.init(‘S‘,null);". This initializes the operating unit context based on the "MO: Operating Unit" profile option and the "MO: Security Profile" profile option must not be set.

Fresh install of Release 12 Application is enabled with multiple organizations, however, the system administrator must create operating units to use multi organizations sensitive application products. The user can create new operating units in the Accounting Setup Manager page in addition to HRMS’s Define Organizations page.

The following section provides guidelines to enable the multiple organizations access control feature to your custom code.

Guidelines for Forms Based Applications

Operating Unit Field in Forms

Users can query or update multiple organizations-striped data by selecting the operating unit. The Operating Unit field list of values (LOV) displays the operating units of the organization that the user‘s application responsibility can access.

General Recommendations

• Display the Operating Unit field on the top left corner of the form as the first navigatable field.
• The LOV window size of the Operating Unit should measure 3 inches x 3 inches.
• If the user can access one operating unit only, then the operating unit field displays the default value and its dependent attributes
• User can enter non-multiple organizations stripped data before specifying the operating unit for a record. The operating unit specific data can be entered only after user sets the operating unit context.

The position of the Operating Unit field on the window depends on the window type. Child windows must display the Operating Unit name in the title bar on saving the parent record.

The following screenshot shows the operating unit in a forms window (Payables - Distribution Sets window):

Default Operating Unit

The user can define a default operating unit. A new profile option, MO: Default Operating Unit, is available to define the defaulting operating unit, which can be set at the Responsibility and User levels. The default operating unit is visible in the Operating Unit field when the form is opened.The user, however, must have access to the default operating unit in his security profile definition. The user can overwrite the default value with another operating unit which the user can access. If the user updates the operating unit, all operating unit sensitive data should be cleared. Alternatively, the entire record can be cleared which is more cost effective in development.

If a user can access only one operating unit, then the operating unit defaults in the operating unit field. This eliminates the task of explicitly defining the default operating unit when the user accesses only a single operating unit.

Guidelines for Oracle Applications (OA) Framework Pages Based Applications

The Operating unit field is visible on OA Framework or JTT based user interfaces, similar to the Oracle Forms user interface.

Operating Unit Field in OA Pages

• The Operating Unit field is available as the first field in the page. If the operating unit is the control field for a particular page, for example create or search page, selecting the operating unit form the Operating Unit field list of values displays the same or different fields and restricts the valid list of values in other related fields.
• Fields that depend on the operating unit, are visible after selecting the operating unit or if the profile option defaults the operating unit.
• The user cannot update the operating unit value once the user saves the record. In OA Framework pages, if the user saves the transaction using the feature “Save for Later”, then the user can still update the operating unit.
• Changing the operating unit before saving a record clears the operating unit specific fields. Alternately, the user can also clear the record instead of clearing the organization specific fields.

The following screenshot shows the operating unit in a OA Framework pages (Oracle Purchasing - Create Blanket Purchase Agreement page):

Default Operating Unit

Similar to forms, the OA Framework pages allows users to default an operating unit using the profile option: "MO: Default Operating Unit", which is set at the Responsibility and User levels. The user must specify a valid operating unit, which is available in the user’s security profile. This feature is useful when the user needs to transact in multiple Operating Units, but usually transacts in one Operating Unit.

If a user can access only one operating unit in the user’s security profile, then the single operating unit defaults in the operating unit field. This eliminates the task of explicitly defining the default operating unit when the user accesses only a single Operating Unit .

If the user can access multiple operating units, then the user can override the default Operating Unit profile option. The Operating Unit dependent default values are cleared when the user overrides the operating unit to ensure data consistency. However, when overwriting, the operating unit reverts to the default when subsequently entering the data .

Concurrent Programs/Reporting

Single Org Reports

A new field "Operating Unit Mode" is added in the Define Concurrent Programs in the OA Framework pages. The user can query the program or report based on an operating unit by updating the "Operating Unit Mode" field with one of the following values:

• Single
• Multiple
• Empty

The default value is Empty.

The multiple organizations context is automatically initialized by the concurrent program if the "Operating Unit Mode" is set to either single or multiple. The user can also select a value from the operating unit field‘s list of values when the mode is single. The value of the "Operating Unit Mode" must be Single for a majority of the existing operating unit context sensitive reports.

There is no need to change the code for single org reports.

The following screenshot illustrates the Operating Unit field enabled in the Submit Request window.

Technical Details

This section provides information for developers for implementing the multiple organizations access control feature.

• Access Control Architecture describes the Virtual Private Database technology and the components for the multiple organizations access control foundation.
• Multiple Organizations Views/Tables Changes provides guidelines to change the multiple organization views.
• Enhancements to Forms provides guidelines to display the Operating Unit field and Operating Unit field validations.
• Enhancements to Reports provides guidelines to modify reports.
• Enhancements to Concurrent Programs provides guidelines to modify concurrent programs.
• Enhancements to Public APIs provides guidelines to modify Public APIs.
• Enhancements to Workflows provides guidelines for modifying Workflows.
• Enhancements to OA Framework Pages provides guidelines to modify OA Framework pages.
• Enhancements to JTT Pages provides guidelines to modify JTT pages.

Access Control Architecture

Background

Multiple organizations architecture (Multi-Org) was introduced in Release 10.6 to secure the data by operating unit. In Release 10.7, Oracle added a column ORG_ID to each base table to partition the data by operating units. The partitioned tables are renamed with the suffix, ‘_ALL‘, and their corresponding secured views are created in Applications (APPS) schema. The following diagram shows a single organization view in the APPS schema.

Figure 1: Database Schema

Multiple organizations views restrict access by filtering records for an operating unit assigned to the application responsibility set for the "MO: Operating Unit" profile option. This profile option value is cached in application context, and is initialized when calling the FND initialization routine. The FND CLIENT_INFO predicate includes all multiple organizations views and SQL statements that require multiple organizations security. The FND_CLIENT_INFO function retrieves the ORG_ID value stored in the application context. This value is valid for a session, unless explicitly changed by the calling procedure.

Use the _ALL table in the SQL statement to retrieve information irrespective of the operating unit. To increase the flexibility and performance in a multiple organizations environment and provide the same level of data security, the DBMS Virtual Private Database (VPD) feature replaces the CLIENT_INFO function.

Virtual Private Database (VPD)

The Virtual Private Database (VPD) feature allows developers to enforce security by attaching a security policy to database objects such as tables, views and synonyms. It attaches a predicate function to every SQL statement to the objects by applying security policies. When a user directly or indirectly accesses the secure objects, the database rewrites the user‘s SQL statement to include conditions set by security policy that are visible to the user.

Figure 2: Database Schema - Access to one operating unit

Figure 3: Database Schema - Access to multiple operating units

You can rewrite reference views that join data from multiple single organization views with the security policy attached to one secured synonym and the remaining reference to _ALL tables instead of single organization views. This improves performance because the policy is used once for the reference views that join data from multiple single organization views.

Multiple Organizations Security Policy Predicate

Synonyms replace single organization views that contain the CLIENT_INFO predicate attached to them. When installing, you must attach a security policy function to the multiple organizations synonyms. This indicates that the security is in place irrespective of the tools used to access the data.

The security policy function returns different predicate based on the number of accessible operating units. An application context attribute “ACCESS_MODE” is set based on the accessible operating units. Context sensitive security policy is used for multiple organizations access control to minimize the coding impact. The multiple organizations code in previous releases works in the context of only one operating unit. It was not anticipated that multiple organizations access would be supported. A solution to code impact is to change the policy predicate whenever needed. For example, when you open a form using a responsibility that can access multiple operating units and when you select an operating unit, the operating unit context is established and you do not need to modify the code that is used for validation from that point onwards, if the synonyms return data for the selected operating unit.

If the access mode is M (Multiple), then the policy predicate issues an EXISTS sub-query to a global temporary table. The global temporary table is a new feature in Oracle 8i. The table stores and manipulates data specific to a SESSION or TRANSACTION. If the access_mode is S (Single), then a simple equality predicate is used for performance reasons, since it is cost effective in comparison to the temporary table. An access mode A (All) is incorporated to bypass the security for functionality that needs full table access. If the access mode is not set or is NULL, then a simple predicate that uses the CLIENT_INFO value for ORG_ID is used for the policy predicate to support backward compatibility.

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;

The simple predicate using CLIENT_INFO is used for the following case:

• Access control is not enabled for older releases of the applications, it is not backward compatible: You cannot enable the multiple organizations access control feature for all products simultaneously because multiple organizations views are shared between products at different levels. For example, if you choose to upgrade Payables but choose to keep an earlier version of Purchasing then Payables is access control enabled, but Purchasing is not. Therefore, Purchasing must replace the views it shares with Payables, such as PO_VENDOR_SITES, and PO_HEADERS, with secured synonyms. The secured synonyms must work as before for Purchasing, since you have not upgraded Purchasing and Purchasing still relies on CLIENT_INFO.

The simple predicate using current_org_id is used for the following cases:

• Access control is limited to only one operating unit: In this case, the access mode is ‘S‘. An example is when a user can access to only one operating unit through the MO: Security Profile or the MO: Security Profile is not set and the user access depends on MO: Operating Unit.
• Access control is enabled with access to multiple operating units: The security profile provides access to multiple operating units, but in the scope of a transaction since the operating unit is controlled, a simple predicate eliminates additional changes to the server and client side code.

The complex predicate is used for these cases:

• Access is enabled and the security profile gives access to multiple Operating Units. The access mode is set to ‘M‘ for this case.

For example, any statement on RA_CUSTOMER_TRX (synonym to which the security policy is attached) is dynamically modified to use the policy predicate.

A simple query by the user:

SELECT trx_number from ra_customer_trx

is modified at runtime if the responsibility can access multiple operating units to:

SELECT trx_number from ra_customer_trx
WHERE (EXISTS (SELECT 1
                 FROM mo_glob_org_access_tmp oa
                 WHERE oa.organization_id = org_id))

or is modified at runtime if the user’s access responsibility can access one Operating Unit with access control enabled for the module to:

SELECT trx_number from ra_customer_trx
 ORG_ID = sys_context(‘multi_org2‘,‘current_org_id‘)

Multiple Organizations Initialization

The profile options MO: Security Profile or MO: Operating Unit populate the multiple organizations global temporary table. The profile option MO: Security Profile takes precedence over MO: Operating Unit. You can combine the following under one application menu:

• Products at different levels
• Products that are access control enabled
• Products that are not access control enabled (i.e. in transition)

In such cases, initializing the multiple organizations depends on the application of the calling module and not the application tied to the responsibility, since the profile Option MO: Security Profile must be ignored for products that are not access control enabled or are in the transition phase.

A new table (FND_MO_PRODUCT_INIT) is introduced which contains a value Y for products that are enabled with the multiple organizations access control feature. The multiple organizations initialization API uses the module owner to initialize the temporary table depending on the value for the product in the FND_MO_PRODUCT_INIT table.

Legend: Y indicates multiple organizations access control is enabled, N indicates otherwise.

Use the shared services API to register products that are enabled with access control. For example to enable or remove access control for Payables (SQLAP), enter the following code:

To enable access:
FND_MO_PRODUCT_INIT_PKG.register_application(‘SQLAP‘,
‘SEED‘,‘Y‘);
To delete your application entry:
FND_MO_PRODUCT_INIT_PKG.remove_application(‘SQLAP‘);

The Payables system administrator must then seed a row in the Multiple Organizations table to indicate that Payables is enabled with access control.

Products must call the MO_GLOBAL.init() API to execute the multiple organizations initialization.

Multiple organizations initialization performs the following:

1. Initializes the security policy predicate
2. Populates a global temporary table that is used in the user interfaces and the security policy function.

Data Model Design

New Tables

MO_GLOB_ORG_ACCESS_TMP

This table is a session-specific global temporary table that stores the operating units available in the current responsibility‘s (or site‘s) MO: Security Profile profile option. If you do not define the profile option, MO: Security Profile, then the operating unit available in the current responsibility‘s (or site‘s) MO: Operating Unit profile option is stored in the table. The tables/views PER_ORGANIZATION_LIST and HR_OPERATING_UNITS populate the records of this table. Use this table to initialize multiple organizations security policy.

A unique index MO_GLOB_ORG_ACCESS_TMP_U1 exists on ORGANIZATION_ID column.

FND_MO_PRODUCT_INIT

This table stores information about a product that implements multiple organizations access control. An entry in this table indicates that the product implements access control and the multiple organizations initialization code uses MO: Security Profile and not MO: Operating Unit.

A unique index FND_MO_PRODUCT_INIT_U1 exists on APPLICATION_SHORT_NAME column.

Multiple Organizations Public APIs

The multiple organizations public APIs are described at the end of this document.

BC4J Objects

The following BC4J components are available for uptaking multiple organizations access control in OA Framework pages.

Operating Unit LOV View Object

The operating unit list of values uses this View Object (VO).

OperatingUnitsRN
  OperatingUnitsTable
    OUName
    OrganizationId

OperatingUnitsRN

OperatingUnitsTable

OUName

OrganizationId

AttributeSets

There are two new attribute sets available for uptaking multiple organizations access control in OA Framework pages:

• OperatingUnitName_Transient
  Use this attribute set for transient items of the operating units, such as list of values or search criteria.
• OperatingUnitName_Persistent
  Use this attribute set for persistent items of the operating units such as displaying the fields in the search results region or displaying the columns in the list of values.

OperatingUnitName_Transient

Some transient items of the operating unit are mandatory, for example, items in the list of values and some are optional, which include items in the search value. Oracle recommends that you specify the transient items as ‘Required‘ depending on the item‘s usage. Providing two attribute sets, one for the required items and another for the optional items, nullifies the purpose of attribute sets.

OperatingUnitName_Persistent

The attributesets are available in this file and location:

• File Name: HrOperatingUnits.xml
• File Location: /oracle/apps/fnd/attributesets/

Multiple Organizations Views/Tables Changes

You must carefully review the multiple organizations views/tables and implement the following changes.

NOT NULL Logical Constraint on ORG_ID column

Multiple organizations architecture is mandatory in Release 12. Therefore, the ORG_ID column in the multiple organizations tables (_ALL, _ALL_TL and _ALL_B) must have a value. An exception to this rule is when you refer to the ‘org_id‘ column for transaction purposes.

The application code must enforce ‘NOT NULL‘ constraint logically in the business logic which minimizes the upgrade time. Enforcing NOT NULL constraint on large tables may take several hours. However, this is optional.

Modify Your Database Views

The multiple organizations access control feature uses a security policy attached to the multiple organizations synonyms to implement the security instead of the CLIENT_INFO predicate in the views. 

The multiple organizations views are divided into two categories: single organization views and reference views.

Single Organization Views are views based on the _ALL, _ALL_B or ALL_TL multiple organizations tables and have the single organization predicate attached to them to return data for the current Operating Unit as specified by the CLIENT_INFO environment variable. The tables _ALL_B and _ALL_TL are introduced for Multi-Lingual Support (MLS).

Reference Views are the views that are joined to single organization views. They do not have the single organization predicate attached to them. They may or may not have the ORG_ID column in their view definition.

Single Organization Views

Replace all single organization views by synonyms to _ALL tables and. attach the security policy function to the synonyms to enforce operating unit security.

Case 1: Single Organization view

Example 1:

The following is an example of the view definition of a single organization view: RA_BATCHES.

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)

A synonym replaces the single organization view RA_BATCHES.

CREATE SYNONYM RA_BATCHES FOR AR.RA_BATCHES_ALL

The following summarizes the changes for joining the single organization views to one _ALL table:

• Drop the single organization view
• Create a synonym with the same name as the obsolete single organization view
• Attach a policy function to the synonym

Example 2:

The following is an example of the view definition of a simple single organization view AR_VAT_TAX_B.

CREATE OR REPLACE VIEW AR_VAT_TAX_B AS
SELECT "VAT_TAX_ID",
       "SET_OF_BOOKS_ID",
       "TAX_CODE",
       ...
       "ORG_ID",
   ...
  FROM AR_VAT_TAX_ALL_B
 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)

A synonym attached with the security policy replaces this single organization view AR_VAT_TAX_B.

CREATE SYNONYM AR_VAT_TAX_B FOR AR.AR_VAT_TAX_ALL_B

Example 3:

The following is an example of AP_CARD_SUPPLIERS. This view uses ROWID alias for the ROW_ID column of the underlying AP_CARD_SUPPLIERS_ALL table.

CREATE OR REPLACE VIEW AP_CARD_SUPPLIERS AS
SELECT ROWID,
       CARD_ID,
       VENDOR_ID,	
       ORG_ID,
...
  FROM AP_CARD_SUPPLIERS_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)

A synonym attached with the security policy replaces this single organization view AP_CARD_SUPPLIERS.

CREATE SYNONYM AP_CARD_SUPPLIERS FOR AP.AP_CARD_SUPPLIERS_ALL

On replacing the view with a synonym, the code depending on the ROWID column becomes INVALID since the synonym AP_CARD_SUPPLIERS does not have this column. You must fix the code for the incorrect columns.

Example 4:

The following is an example of the view definition of single organization view AR_PAYMENT_SCHEDULES_V. This is a special case, where the CLIENT_INFO predicate is coded in the view definition for performance reasons. You cannot merge this view definition because of its union clause. Therefore, you use the base tables of the views in the FROM clause, instead.

CREATE OR REPLACE VIEW AR_PAYMENT_SCHEDULES_V AS
SELECT PS.ROWID,
       PS.PAYMENT_SCHEDULE_ID,
       PS.TRX_NUMBER,
       ...
  FROM ar_lookups al_status,
       ar_collectors ar_coll,
       ar_cons_inv_all cons,
       ra_cust_trx_types_all ctt,
       ra_batch_sources_all bs,
       ra_customer_trx_all ct,
       hz_cust_site_uses_all su,
       hz_cust_accounts cust_acct,
       hz_parties party,
       ar_payment_schedules_all ps
 WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
   AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
   AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID
   AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
   AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
   AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
   AND PS.STATUS = AL_STATUS.LOOKUP_CODE
   AND AL_STATUS.LOOKUP_TYPE = ‘INVOICE_TRX_STATUS‘
   AND PS.COLLECTOR_LAST = AR_COLL.COLLECTOR_ID (+)
   AND PS.CONS_INV_ID = CONS.CONS_INV_ID (+)
   AND
NVL(CONS.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)
 	AND
NVL(CTT.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)
   AND
NVL(BS.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)
   AND
NVL(CT.ORG_ID,NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV(‘CLIENT_INFO‘),1,1),
‘ ‘, NULL, SUBSTRB(USERENV(‘CLIENT_INFO‘),1,10))),-99))=
NVL(TO_NUMBER(DECODE(S UBSTRB(USERENV(‘CLIENT_INFO‘),1,1),
‘ ‘, NULL, SUBSTRB(USERENV(‘CLIENT_INFO‘),1, 10))),-99)
   AND
NVL(SU.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)
   AND
NVL(PS.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)
   AND PS.STATUS = NVL(ARP_VIEW_CONSTANTS.GET_STATUS,PS.STATUS)
UNION ALL
SELECT PS.ROWID,
       PS.PAYMENT_SCHEDULE_ID,
       PS.TRX_NUMBER,
       ...
FROM   ar_lookups al_risk_receipt,
       ar_cons_inv_all cons,
       ar_receipt_methods rm,
       ar_batch_sources_all bs,
       ar_batches_all arb2,
       ar_cash_receipt_history_all crh,
       ar_cash_receipt_history_all crh_current,
       ar_cash_receipts_all cr,
       hz_cust_site_uses_all su,
       hz_cust_accounts cust_acct,
       hz_parties party,
       ar_payment_schedules_all ps,
       ar_cash_receipt_history_all crh_remit,
       ar_batches_all arb_remit,
       fnd_currencies fc
WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID (+)
  AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID (+)
  AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID (+)
  AND PS.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
  AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
  AND CRH.FIRST_POSTED_RECORD_FLAG = ‘Y‘
  AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
  AND CRH.BATCH_ID = ARB2.BATCH_ID (+)
  AND ARB2.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID(+)
  AND CR.CASH_RECEIPT_ID = CRH_CURRENT.CASH_RECEIPT_ID
  AND CRH_CURRENT.CURRENT_RECORD_FLAG = ‘Y‘
  AND PS.CONS_INV_ID = CONS.CONS_INV_ID (+)
  AND CR.CASH_RECEIPT_ID = CRH_REMIT.CASH_RECEIPT_ID(+)
  AND CRH_REMIT.STATUS(+) = ‘REMITTED‘
  AND CRH_REMIT.BATCH_ID = ARB_REMIT.BATCH_ID(+)
  AND FC.CURRENCY_CODE = CR.CURRENCY_CODE
  AND AL_RISK_RECEIPT.LOOKUP_TYPE = ‘YES/NO‘
  AND AL_RISK_RECEIPT.LOOKUP_CODE IN
(DECODE(CRH_CURRENT.STATUS,‘CLEARED‘,‘Y‘,‘N‘),
DECODE(NVL(ARP_VIEW_CONSTANTS.GET_INCL_RECEIPTS_AT_RISK,‘N‘),‘Y‘, ‘Y‘,NULL ) )
  AND ARP_VIEW_CONSTANTS.GET_SALES_ORDER IS NULL
  AND
NVL(CONS.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)
  AND
NVL(BS.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)
  AND
NVL(ARB2.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)
  AND
NVL(CRH.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)
  AND
NVL(CRH_CURRENT.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)
  AND
NVL(CR.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)
  AND
NVL(SU.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)
  AND 	NVL(PS.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)
  AND
NVL(CRH_REMIT.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)
  AND
NVL(ARB_REMIT.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)
  AND PS.STATUS= NVL(ARP_VIEW_CONSTANTS.GET_STATUS,PS.STATUS)

This single organization view AR_PAYMENT_SCHEDULES_V in addition to the CLIENT_INFO predicate includes an additional filter condition, which must remain as is. Therefore, you must rewrite this single organization view to a reference view following the guidelines in the subsequent section.

You must remove the CLIENT_INFO predicate from the where clause, add the ORG_ID column to the view, and add the ORG_ID filter to tables in which the the ORG_ID is a composite key (as in setup tables that contain seed data replicated for every organization) because the ORG_ID is the driving key for the table (as in product system options tables) and the driving table for the view is replaced by the secured synonym (AR_PAYMENT_SCHEDULES):

CREATE OR REPLACE VIEW AR_PAYMENT_SCHEDULES_V AS
SELECT PS.ROWID,
       PS.PAYMENT_SCHEDULE_ID,
       PS.TRX_NUMBER,
       ...
       PS.ORG_ID
  FROM ar_lookups al_status,
       ar_collectors ar_coll,
       ar_cons_inv_all cons,
       ra_cust_trx_types_all ctt,
       ra_batch_sources_all bs,
       ra_customer_trx_all ct,
       hz_cust_site_uses_all su,
       hz_cust_accounts cust_acct,
       hz_parties party, 
       ar_payment_schedules ps
 WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
   AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
   AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID
   AND PS.CUSTOM ER_TRX_ID = CT.CUSTOMER_TRX_ID
   AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID 
   AND CT.ORG_ID = BS.ORG_ID
   AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
   AND CT.ORG_ID = CTT.ORG_ID
   AND PS.STATUS = AL_STATUS.LOOKUP_CODE
   AND AL_STATUS.LOOKUP_TYPE = ‘INVOICE_TRX_STATUS‘
   AND PS.COLLECTOR_LAST = AR_COLL.COLLECTOR_ID (+)
   AND PS.CONS_INV_ID = CONS.CONS_INV_ID (+)
   AND PS.STATUS = NVL(ARP_VIEW_CONSTANTS.GET_STATUS,PS.STATUS)
UNION ALL
SELECT PS.ROWID,
       PS.PAYMENT_SCHEDULE_ID,
       PS.TRX_NUMBER,
       ...	
       PS.ORG_ID
FROM   ar_lookups al_risk_receipt,
       ar_cons_inv_all cons,
       ar_receipt_methods rm,
       ar_batch_sources_all bs,
       ar_batches_all arb2,
       ar_cash_receipt_history_all crh,
       ar_cash_receipt_history_all crh_current,
       ar_cash_receipts_all cr,
       hz_cust_site_uses_all su,
       hz_cust_accounts cust_acct,
       hz_parties party,
       ar_payment_schedules ps,
       ar_cash_receipt_history_all crh_remit,
       ar_batches_all arb_remit,
       fnd_currencies fc
WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID (+)
   AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID (+) 
   AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID (+)
   AND PS.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID 
   AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
   AND CRH.FIRST_POSTED_RECORD_FLAG = ‘Y‘
   AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
   AND CRH.BATCH_ID = ARB2.BATCH_ID (+)
   AND ARB2.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID(+)
   AND ARB2.ORG_ID = BS.ORG_ID(+) 
   AND CR.CASH_RECEIPT_ID = CRH_CURRENT.CASH_RECEIPT_ID
   AND CRH_CURRENT.CURRENT_RECORD_FLAG = ‘Y‘
   AND PS.CONS_INV_ID = CONS.CONS_INV_ID (+)
   AND CR.CASH_RECEIPT_ID = CRH_REMIT.CASH_RECEIPT_ID(+) 
   AND CRH_REMIT.STATUS(+) = ‘REMITTED‘
   AND CRH_REMIT.BATCH_ID = ARB_REMIT.BATCH_ID(+) 
   AND FC.CURRENCY_CODE = CR.CURRENCY_CODE
   AND AL_RISK_RECEIPT.LOOKUP_TYPE = ‘YES/NO‘ 
   AND AL_RISK_RECEIPT.LOOKUP_CODE IN (DECODE(CRH_CURRENT.STATUS,‘CLEARED‘,‘Y‘,‘N‘), 
DECODE(NVL(ARP_VIEW_CONSTANTS.GET_INCL_RECEIPTS_AT_RISK,‘N‘),‘Y‘, ‘Y‘,NULL )  )
   AND ARP_VIEW_CONSTANTS.GET_SALES_ORDER IS NULL
   AND PS.STATUS= NVL(ARP_VIEW_CONSTANTS.GET_STATUS,PS.STATUS)

Example 5:

The following is an example of the view definition of the single organization view RA_ADDRESSES. The view depends on the RA_ADDRESSES_ALL synonym and includes the CLIENT_INFO filter. The synonym RA_ADDRESSES_ALL in turn depends on the RA_ADDRESSES_MORG view. The RA_ADDRESSES_MORG view depends on several HZ tables (HZ_CUST_ACCT_SITES_ALL, HZ_LOC_ASSIGNMENTS, HZ_LOCATIONS and HZ_PARTY_SITES). This view is created for backward compatibility to migrate customers to Trading Community Architecture.

CREATE OR REPLACE VIEW RA_ADDRESSES AS
SELECT ROW_ID,
       KEY_ACCOUNT_FLAG,

       ORG_ID,
FROM RA_ADDRESSES_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)

You must rewrite this single organization view RA_ADDRESSES as a reference view following the guidelines of reference views in the subsequent section. RA_ADDRESSES must remain a view, which depends on HZ_CUST_ACCT_SITES (secured synonym), HZ_LOC_ASSIGNMENTS, HZ_LOCATIONS and HZ_PARTY_SITES. You must also remove the CLIENT_INFO predicate from the view.

There are two additional cases, where the single organization views have either MLS logic or MRC logic embedded in the where clause in addition to the single organization predicate.

Case 2: Single Organization View with Multi-Lingual Support

Example: Original Single Organization View definition with MLS logic

CREATE OR REPLACE VIEW AR_VAT_TAX_VL AS 
SELECT B.ROWID ROW_ID,
       B.ADJ_NON_REC_TAX_CCID, 
       B.EDISC_NON_REC_TAX_CCID,
       B.UNEDISC_NON_REC_TAX_CCID,
       ...
       B.ENABLED_FLAG,
       B.TAX_CLASS, 
       B.DISPLAYEDED_FLAG,
       B.TAX_CONSTRAINT_ID
FROM   AR_VAT_TAX_ALL_TL T,
       AR_VAT_TAX_ALL_B B
WHERE B.VAT_TAX_ID = T.VAT_TAX_ID AND NVL(B.ORG_ID, -99) = NVL(T.ORG_ID, -99) 
  AND
NVL(B.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) 
AND T.LANGUAGE = userenv(‘LANG‘)

The above view definition includes the CLIENT_INFO predicate and the filter condition for MLS logic, which must remain as is. Therefore, you must rewrite the single organization view to a reference view as shown in the following code:

Modified View Definition with MLS Logic

The line AND NVL(B.ORG_ID, -99) = NVL(T.ORG_ID, -99) is not necessary as vat_tax_id is unique across organizations.

CREATE OR REPLACE VIEW AR_VAT_TAX_VL AS 
SELECT B.ROWID ROW_ID, B.ADJ_NON_REC_TAX_CCID, 
       B.EDISC_NON_REC_TAX_CCID,
       B.UNEDISC_NON_REC_TAX_CCID,
       B.ORG_ID
       ...
       B.ENABLED_FLAG, 
       B.TAX_CLASS,
       B.DISPLAYEDED_FLAG,
       B.TAX_CONSTRAINT_ID
FROM   AR_VAT_TAX_ALL_TL T,
       AR_VAT_TAX_B B
WHERE B.VAT_TAX_ID = T.VAT_TAX_ID  
AND T.LANGUAGE = userenv(‘LANG‘)

The following are the changes for single organization views with MLS logic:

• Add the ORG_ID column to view definition, if it does not exist
• Remove the Client Info predicate from the where clause
• Replace the multiple organizations base table reference with secured synonym
• Add the ORG_ID filters if the underlying multiple organizations tables in the join condition include the ORG_ID as part of the composite key or the ORG_ID is the driving key to avoid cartesian joins

In the above example, the ORG_ID filter in the where clause is not present as it is not part of the composite index for the joined tables.

Reference Views

The reference views join one or more single organization views. You must modify these views to include one secured synonym in the join condition. Use the _ALL tables to refer to the single organization views. The criteria for selecting the secured synonym are:

1. The secured synonym is a driving table
2. The secured synonym includes small volume of data (typically a setup table and not a transaction table)

You must add the ORG_ID filter to the where clause condition to avoid Cartesian products for tables that include ORG_ID as the composite index (as in tables that contain replicate seed data for every organization) because the ORG_ID is the driving key for the table (as in product system options tables).

Example 1: Original Referenced View Definition

CREATE OR REPLACE VIEW RA_CUSTOMER_TRX_PARTIAL_V AS
SELECT CT.ROWID "ROW_ID",	
       CT.CUSTOMER_TRX_ID "CUSTOMER_TRX_ID",
       CT.TRX_NUMBER "TRX_NUMBER",	
       CT.OLD_TRX_NUMBER "OLD_TRX_NUMBER",
       CT_REL.TRX_NUMBER "CT_RELATED_TRX_NUMBER" 
       ...
ARPT_SQL_FUNC_UTIL.get_salesrep_name_number(CT.PRIMARY_SALESREP_ID,‘NAME‘)
		"RAS_PRIMARY_SALESREP_NAME",
ARPT_SQL_FUNC_UTIL.get_salesrep_name_number(CT.PRIMARY_SALESREP_ID,‘NUMBER‘)
		"RAS_PRIMARY_SALESREP_NUM"
			...
FROM	RA_CUST_TRX_LINE_GL_DIST 
  		GD, RA_CUSTOMER_TRX CT,
		...
		RA_SITE_USES SU_BILL,
		RA_CUSTOMERS RAC_PAYING,	
		RA_CUSTOMERS RAC_BILL,
		RA_SITE_USES SU_BILL,
		RA_SITE_USES SU_SHIP,
		RA_SITE_USES SU_PAYING,
		...
		RA_ADDRESSES RAA_BILL,
		RA_ADDRESSES RAA_SHIP,
		RA_ADDRESSES RAA_REMIT,
		RA_CONTACTS RACO_SHIP,
		RA_CONTACTS RACO_BILL,
		AP_BANK_ACCOUNTS APBA,	
  		AP_BANK_BRANCHES APB,
		AR_RECEIPT_METHODS ARM,
		AR_RECEIPT_CLASSES ARC,
		RA_BATCH_SOURCES BS,
		RA_BATCHES RAB,
		RA_CUST_TRX_TYPES CTT,
		...
 WHERE CT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
   AND ‘REC‘ = GD.ACCOUNT_CLASS AND ‘Y‘ = GD.LATEST_REC_FLAG	
   AND CT.RELATED_CUSTOMER_TRX_ID = CT_REL.CUSTOMER_TRX_ID(+)
	AND CT.BILL_TO_CUSTOMER_ID = RAC_BILL.CUSTOMER_ID
   AND CT.SHIP_TO_CUSTOMER_ID = RAC_SHIP.CUSTOMER_ID(+)	
   AND CT.SOLD_TO_CUSTOMER_ID = RAC_SOLD.CUSTOMER_ID
   AND CT.PAYING_CUSTOMER_ID = RAC_PAYING.CUSTOMER_ID(+)
   AND CT.BILL_TO_SITE_USE_ID = SU_BILL.SITE_USE_ID	
   ...
   AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
   AND CT.BATCH_ID = RAB.BATCH_ID(+)	
   AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
   AND CTT.TYPE <> ‘BR‘ 
	...

The view definition is modified for multiple organizations access control by replacing the reference views to single organization views with the _ALL tables. The RA_CUSTOMER_TRX object is not replaced as it is the driving table by retaining the secured synonym as shown in the following code:

CREATE OR REPLACE VIEW RA_CUSTOMER_TRX_PARTIAL_V AS
SELECT CT.ROWID "ROW_ID",	
		CT.CUSTOMER_TRX_ID "CUSTOMER_TRX_ID",
		CT.TRX_NUMBER "TRX_NUMBER",	
		CT.OLD_TRX_NUMBER "OLD_TRX_NUMBER",
		CT_REL.TRX_NUMBER "CT_RELATED_TRX_NUMBER",	
ARPT_SQL_FUNC_UTIL.get_salesrep_name_number(CT.PRIMARY_SALESREP_ID,‘NAME‘’, CT.ORG_ID)
		"RAS_PRIMARY_SALESREP_NAME",
ARPT_SQL_FUNC_UTIL.get_salesrep_name_number(CT.PRIMARY_SALESREP_ID,‘NUMBER‘, CT.ORG_ID)
		"RAS_PRIMARY_SALESREP_NUM",
		CT.ORG_ID,
		...
FROM	RA_CUST_TRX_LINE_GL_DIST_ALL GD,
		RA_CUSTOMER_TRX CT,
		...
		RA_SITE_USES_ALL SU_BILL,
		RA_CUSTOMERS RAC_PAYING,
		RA_CUSTOMERS RAC_BILL,
		RA_SITE_USES_ALL SU_BILL,	
		RA_SITE_USES_ALL SU_SHIP,
		RA_SITE_USES_ALL SU_PAYING,
		...
		RA_ADDRESSES_ALL RAA_BILL,
		RA_ADDRESSES_ALL RAA_SHIP,
		RA_ADDRESSES_ALL RAA_REMIT,
		RA_CONTACTS RACO_SHIP,
		RA_CONTACTS RACO_BILL,
		AP_BANK_ACCOUNTS_ALL APBA,
		AP_BANK_BRANCHES APB,
		AR_RECEIPT_METHODS ARM,
		AR_RECEIPT_CLASSES ARC,
		RA_BATCH_SOURCES_ALL BS,
		RA_BATCHES_ALL RAB, 
		RA_CUST_TRX_TYPES_ALL CTT,
		...
WHERE	CT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID 
  AND ‘REC‘ = GD.ACCOUNT_CLASS 
  AND ‘Y‘ = GD.LATEST_REC_FLAG	
  AND CT.RELATED_CUSTOMER_TRX_ID = CT_REL.CUSTOMER_TRX_ID(+)
  AND CT.BILL_TO_CUSTOMER_ID = RAC_BILL.CUSTOMER_ID
  AND CT.SHIP_TO_CUSTOMER_ID = RAC_SHIP.CUSTOMER_ID(+)	
  AND CT.SOLD_TO_CUSTOMER_ID = RAC_SOLD.CUSTOMER_ID
  AND CT.PAYING_CUSTOMER_ID = RAC_PAYING.CUSTOMER_ID(+)
  AND CT.BILL_TO_SITE_USE_ID = SU_BILL.SITE_USE_ID	
  ...
  AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
  AND CT.ORG_ID 		  = BS.ORG_ID
  AND CT.BATCH_ID = RAB.BATCH_ID(+)
  AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID	
  AND CT.ORG_ID 		   = CTT.ORG_ID
  ...

The following are the changes you must perform for reference views:

• Add the ORG_ID column if it does not exist
• Replace single organization views with _ALL tables for all except one, which must be a secured synonym
• Include the ORG_ID filter in the where clause of the view to avoid the cartesian product, if the ORG_ID is the driving key or part of the composite key
• Include the ORG_ID parameter in the columns based on functions, if necessary

Example 2: Original Reference View Definition

CREATE OR REPLACE VIEW AR_TAX_LINES_V AS
SELECT CTL_TAX.ROWID,
		CTL_TAX.CUSTOMER_TRX_ID,
		CTL_TAX.CUSTOMER_TRX_LINE_ID,
		CTL_TAX.PREVIOUS_CUSTOMER_TRX_ID,
		CTL_TAX.PREVIOUS_CUSTOMER_TRX_LINE_ID,
		CTL_TAX.LINK_TO_CUST_TRX_LINE_ID,
		...
		CTL_TAX.ORG_ID

 FROM	RA_CUSTOMER_TRX_LINES CTL_INV_LINE, 
		RA_CUSTOMER_TRX_LINES CTL_INV_TAX,
		AR_VAT_TAX INV_VAT, 
		RA_CUSTOMER_TRX_LINES CTL_LINE,
		RA_CUSTOMER_TRX_LINES CTL_TAX,
		AR_VAT_TAX VAT 
 WHERE CTL_TAX.LINK_TO_CUST_TRX_LINE_ID = CTL_LINE.CUSTOMER_TRX_LINE_ID
   AND CTL_TAX.LINE_TYPE  = ‘TAX‘
   AND CTL_TAX.VAT_TAX_ID = VAT.VAT_TAX_ID(+)
   AND CTL_TAX.PREVIOUS_CUSTOMER_TRX_LINE_ID = CTL_INV_TAX.CUSTOMER_TRX_LINE_ID (+)
   AND CTL_INV_TAX.LINK_TO_CUST_TRX_LINE_ID  = CTL_INV_LINE.CUSTOMER_TRX_LINE_ID (+)
   AND CTL_INV_TAX.VAT_TAX_ID = INV_VAT.VAT_TAX_ID (+)

The following code describes the view definition for multiple organizations access control by adding the ORG_ID column to the view definition, replacing all single organization views as reference view by the _ALL tables and retaining the RA_CUSTOMER_TRX_LINES (CTL_TAX), which is the driving table, as the secured synonym.

CREATE OR REPLACE VIEW AR_TAX_LINES_V AS
SELECT CTL_TAX.ROWID,
		CTL_TAX.CUSTOMER_TRX_ID,
		CTL_TAX.CUSTOMER_TRX_LINE_ID,
		CTL_TAX.PREVIOUS_CUSTOMER_TRX_ID,
		CTL_TAX.PREVIOUS_CUSTOMER_TRX_LINE_ID,
		CTL_TAX.LINK_TO_CUST_TRX_LINE_ID,
		...
		CTL_TAX.ORG_ID
FROM 	RA_CUSTOMER_TRX_LINES_ALL CTL_INV_LINE,
		RA_CUSTOMER_TRX_LINES_ALL CTL_INV_TAX,
		AR_VAT_TAX_ALL INV_VAT,
		RA_CUSTOMER_TRX_LINES_ALL CTL_LINE,
		RA_CUSTOMER_TRX_LINES CTL_TAX,
		AR_VAT_TAX_ALL VAT
 WHERE CTL_TAX.LINK_TO_CUST_TRX_LINE_ID = CTL_LINE.CUSTOMER_TRX_LINE_ID
   AND CTL_TAX.LINE_TYPE = ‘TAX‘
   AND CTL_TAX.VAT_TAX_ID = VAT.VAT_TAX_ID(+)
   AND CTL_TAX.PREVIOUS_CUSTOMER_TRX_LINE_ID = CTL_INV_TAX.CUSTOMER_TRX_LINE_ID (+)
   AND CTL_INV_TAX.LINK_TO_CUST_TRX_LINE_ID = CTL_INV_LINE.CUSTOMER_TRX_LINE_ID (+)
   AND CTL_INV_TAX.VAT_TAX_ID = INV_VAT.VAT_TAX_ID (+) 

Attach Security Policy to your Database Objects

Attach the security policy to the multiple organizations synonyms to enforce security. The package MO_UTILS contains the utilities for administering policies (add policy, drop policy or check if a policy exists on an object). Use this package to administer the security policies.

Review the ad_mo_util_pkg in the /ad/patch/115/sql directory to understand how policy functions are attached to synonyms. The following is sample code that illustrates attaching security policy to the synonym.

dbms_rls.add_policy (p_apps_user_name,
			v_synonym, -- synonym name
			p_sec_policy_name, -- use ‘ORG_SEC‘ here
			p_apps_user_name, 
			‘MO_GLOBAL.ORG_SECURITY‘, -- Standard MO VPD policy
			‘SELECT, INSERT, UPDATE, DELETE‘,
			TRUE,
			TRUE,
			FALSE,
			DBMS_RLS.SHARED_CONTEXT_SENSITIVE);

In SQL*Plus, you can directly invoke the dbms_rls function using the appropriate parameters. For more details, refer to the RDBMS documentation, as the dbms_rls parameters are different for the RDBMS versions.

Enhancements to Forms

The multiple organizations setup and transaction forms must display the Operating Unit field. This allows users to select the operating unit and enter the setup or transaction for the operating unit. Oracle recommends deriving the operating units from the transaction attributes.

Multiple Organizations Initialization

Every form updated for multiple organizations access control must include a call to the multiple organizations initialization API (MO_GLOBAL.init) in the pre-form trigger. Pass S or M as the input parameter for non-multiple organizations access control enabled and multiple organizations access control enabled applications respectively.

For example, a Oracle Payables form enabled for access control, must include the following code as shown in the pre-form trigger:

BEGIN
	APP_STANDARD.EVENT(‘PRE-FORM’);
	MO_GLOBAL.init (‘SQLAP‘); can be ‘S‘ for single, ‘M‘ for multiple mode or 
	‘<custom application short code you registered in fnd_mo_product_init table>‘
END;

In the above example, SQLAP is the application short name for Oracle Payables.

If the MO: Security Profile profile option is set for multiple access, then the above code populates the temporary table with multiple operating units. The access mode is also set to MULTIPLE.

Add Operating Unit Field

The general recommendation is to place the Operating Unit field as the first field in multiple organizations enabled forms. The Operating Unit field, which is a non-base table item, is derived from the ORG_ID value of the HR tables.

You add the Operating Unit and ORG_ID fields in the form block. You do not need the Operating Unit field for blocks that do not display this field to the users. Similarly, you do not need the ORG_ID field for blocks not displaying the Operating Unit field to the user.

ORG_ID Field Details

OPERATING_UNIT Field Details

Create List of Values for Operating Unit Field

You must create a query based record group to display the operating units that are included in the security profile for a responsibility. The multiple organizations global temporary table is populated with the operating unit information depending on the MO: Security Profile profile option. To make things easier and minimize future impacts, use the APIs to obtain the operating unit name from the temporary table instead of accessing the temporary table directly.

The record group query for Operating Unit field is coded as shown below:

select 	hr.organization_id,
			org_id,
			hr.name operating_unit
FROM 		hr_operating_units hr
 WHERE 	mo_global.check_access(hr.organization_id) = ‘Y‘

Record Group Column Specifications

Create a list of values based on this record group. The list of values size must be 3 x 3 inches. You must display the operating unit name in the list of values window.

List Of Values column mapping Properties

Attach the list of values to the Operating Unit field. The property classes for operating unit record group and list of values are available in the multiple organizations object group. You must apply the property classes to your operating unit record group and list of values. There is no relation between the operating unit and legal entity in Release 12, it is not correct to display the legal entity in the operating unit list of values.

Default Operating Unit on Forms Initialization

On initializing forms, you must call the API MO_UTILS.get_default_ou to copy the global variables value to the form parameters. You must create new form parameters to store the API output and copy the default operating unit to the form block to the when-create-record trigger. The following table illustrates the new form parameters.

Pre-form Trigger

DECLARE
	l_default_org_id number;
	l_default_ou_name varchar2(240);
	l_ou_count number;
BEGIN
	...
	mo_utils.get_default_ou(l_default_org_id, l_default_ou_name, l_ou_count);
	:PARAMETER.mo_default_org_id) := l_default_org_id;
	:PARAMETER.mo_default_ou_name := l_default_ou_name;
	:PARAMETER.mo_ou_count := l_ou_count;
	-- Can also use indirect reference as given below:
	-- copy(l_default_org_id,’PARAMETER.mo_default_org_id’);	
	-- copy(l_default_ou_name,’PARAMETER.mo_default_ou_name’);
	-- copy(l_ou_count,’PARAMETER.mo_ou_count’);	
	...
END;

Block Level When-Create-Record trigger

IF :parameter.mo_default_org_id is not null and :block.org_id is null THEN
	:block.org_id := :parameter.mo_default_org_id);
	:block.operating_unit := :parameter.mo_default_ou_name;
	-- Can use copy built in as given below:
	-- copy(‘parameter.mo_default_org_id’,’block.org_id’);	
	-- copy(‘parameter.mo_default_ou_name’,’block.operating_unit’);	
END IF;

Select/Derive Operating Unit Features

Select Operating Unit: In some forms, the user must select an operating unit before entering additional data. In such cases, the operating unit dependent fields are inactive when the user opens forms and are active after the user enters or selects an operating unit. Activating the dependent fields is handled by the When-Validate-Item trigger of the Operating Unit field which initializes the operating unit specific attributes, and caches and calls the fnd_flex.event to initialize accounting flexfields, etc.

Derive Operating Unit: In some forms, the user may not enter or select the operating unit but enter some values for the transaction, which determines the operating unit. Therefore, in these forms, the Operating Unit field and the Operating Unit specific fields must remain active when the user opens the forms. The initialization of operating unit specific attributes, caching and calling the fnd_flex.event to initialize accounting flexfields etc. occur in the when-validate-item trigger of Operating Unit field and when-validate-item trigger of Operating Unit specific fields. The Operating Unit specific fields must display data for the operating units that the responsibility is assigned to. However, when the user selects an operating unit, the Operating Unit specific fields must display the data for the selected operating unit only.

Setting the Policy Context

The multiple organizations security policy function uses a shared-context-sensitive predicate to handle simple predicate when the user can access one operating unit only and a complex predicate (exists sub-query) when the user can access multiple operating units. The predicate depends on the access mode of the application context attribute value.

The "current organization" concept is introduced to salvage the existing code that works in single operating unit context. To improve performance in high volume transactional forms, you must avoid policy context. The default policy context is set on initializing the form to either Multiple, if user can access multiple operating units or Single, if the user can access only one operating unit.

Forms that Support Select Operating Unit Feature

Call the multiple organizations API to set the context to multiple or single in the following triggers:

When-Create-Record Trigger of Operating Unit Field Block

IF (:parameter.mo_default_org_id IS NOT NULL ) THEN
	-- Defaulting org_id from profile option
	:block.org_id := :parameter.mo_default_org_id;
	:block.operating_unit := :parameter.mo_default_ou_name;
	-- Set policy context
	mo_global.set_policy_context(‘S’,:block.org_id);
ELSE 
  mo_global.set_policy_context(‘M‘, null);
END IF;

IF :<your block name.org_id> is not null IF :<block name.org_id> <> nvl(:<parameter.old_org_id>,-99) THEN
   -- Get the cache for current org
 END IF;
ELSE
 -- Refresh the cache
...
END IF;

When-Validate-Item Trigger of Operating Unit field

IF (:<your block name.org_id> IS NOT NULL ) THEN
 IF :<block name.org_id> <> nvl(:<parameter.old_org_id>,-99) THEN
    mo_global.set_policy_context(‘S‘, :block.org_id);
    -- Get the cache for the current org
 END IF;
ELSE -- :block.org_id is null
 mo_global.set_policy_context(‘M‘, null);
 -- Refresh the cache
END IF; 

When-New-Record-Instance Trigger of Operating Unit Field Block

IF (:<your block name.org_id> IS NOT NULL ) THEN
 IF :<block name.org_id> <> nvl(:<parameter.old_org_id>,-99) THEN 
	mo_global.set_policy_context(‘S‘, :block.org_id);
	-- Get the cache for the current org
 END IF;
ELSE -- :block.org_id is null, so set the context to multiple
 mo_global.set_policy_context(‘M‘, null);
 -- Refresh the cache
END IF;

Pre-Insert Trigger of Operating Unit Field Block

Use this trigger if the form allows the user to commit multiple records.

IF (:<your block name.org_id> IS NOT NULL ) THEN
 IF :<block name.org_id> <> nvl(:<parameter.old_org_id>,-99) THEN
	mo_global.set_policy_context(‘S‘, :block.org_id);
	-- Get the cache for the current org
 END IF;
ELSE -- :block.org_id is null, so set the context to multiple
 mo_global.set_policy_context(‘M‘, null);
 -- Refresh the cache
END IF;

Pre-Query Trigger of Operating Unit Field Block

BEGIN
 IF :parameter.mo_ou_count = 1 THEN
	mo_global.set_policy_context(‘S’,:parameter.mo_default_org_id);
 ELSE
	mo_global.set_policy_context(‘M‘, null);
 END IF;
 -- Other Code
END;

Pre-Record Trigger of Operating Unit Field Block

use this trigger if the form forces the user to commit each record.

IF (:parameter.current_record is not null and
	 :parameter.current_record != :system.trigger_record) THEN
  IF (:system.form_status in (‘CHANGED‘,‘INSERT‘)) THEN
	 mo_global.set_policy_context(‘S‘, :parameter.old_org_id);
	 -- Get the cache for the current org
	 -- raise error message to the user to commit;
	 -- raise form_trigger_failure; 
  ELSE
	 -- No pending commits.
	 -- Reset the current record variable.
	 :parameter.current_record := ‘‘;
  END IF;
ELSE
  -- User has not navigated to another record.
  -- Do not reset the current record variable. 
  null;
END IF;

Pre-Update Trigger

Use this trigger if the form allows the user to commit multiple records commits that are in different operating units.

IF (:<your block name.org_id> IS NOT NULL ) THEN
  IF :<block name.org_id> <> nvl(:<parameter.old_org_id>,-99) THEN
    mo_global.set_policy_context(‘S‘, :block.org_id);
	 -- Get the cache for the current org
  END IF;
END IF;

Forms that Support Derive Operating Unit Feature

The select operating unit feature triggers and the When-Validate-Item trigger of Operating Unit specific fields allow you to derive the operating unit from another attribute.

Initialize Operating Unit Specific Attributes

In earlier releases:

• The responsibility assigned to a user determined the operating unit because the responsibility could access only one operating unit.
• The operating unit specific attributes such as display of operating unit dependent fields and the default operating unit appeared when initializing forms.

However, with access control, this is not the case as a responsibility can access multiple operating units. and the operating unit specific attributes are initialized when the MO: Default Operating Unit profile option is set.

You must initialize the operating unit in the When-Validate-Item trigger of the Operating Unit field and modify the operating unit initialization in the When-Create-Record trigger of the Operating Unit field block to include the default operating unit .The When-Create-Record trigger must be executed only if the user defines the default operating unit.

Forms cache the operating unit specific information. For more information, refer to Operating Unit Cache Information.

The following examples illustrate the types of initialization.

You must modify the SQL to initialize Operating Unit specific attributes as follows:

• Do not use the multiple organizations temporary table in the SQL code to check and obtain information about the operating unit assigned to a user directly, use the APIs, instead.
• Modify joins of two or more multiple organizations views by referring to one synonym and referring the remaining views to the _ALL tables (similar to reference view standards).
• Add the ORG_ID filter to the WHERE clause of the SQL to avoid cartesian joins for tables that include the ORG_ID as part of the composite key or driving key.

Example 1:

You can retrieve the information from the database after you select the operating unit for a multiple organizations instance for forms that do not cache the operating unit specific information. This must be coded in the following triggers:

When-Validate-Item of OU Field/ Post-Query/When-Create-Record/When-New-Record-Instance Trigger of OU Field Block

...
  IF :<block_name.org_id> is not null THEN
	  MO_global.set_policy_context(‘S’,:block.org_id);	
  -- Here you can verify if the old org is same as the
  -- new org. If different, then execute the above, else skip the select step
	 
	 SELECT b.ledger_id, b.chart_accounts_id, c.currency_code
		FROM <system options> a,
			 gl_ledgers_public_v b,
			 fnd_currencies c
	WHERE a.set_of_books_id = b.ledger_id
	  AND b.currency_code = c.currency_code
	  AND a.org_id = <your block name>.org_id;
  END IF;
...

Example 2:

This example demonstrates how field properties are controlled when the user selects an operating unit.

The fields that depend on the operating units are disabled when forms are initialized and enabled when the user selects the operating unit according to the business logic. You must not hide the dependent fields when not applicable, as it may confuse the user, who might access multiple operating units, each set up in a different way.

For example, the Billing Number field in Receivables depends on the operating unit. The field must remain disabled when the user initializes forms. However, if the user defines Show Billing Number in Receivables system options and includes the billing number as a part of cached operating unit specific information, then depending on the cached value, you must enable the field AR_BILLING_NUMBER_MIR in the When-Validate-Item trigger of the Operating Unit field and also in the When-Create-Record trigger of the field block.

When-Validate-Item/When-Create-Record Trigger

...
	IF :ar_world.ar_show_billing_number = ‘Y‘ THEN
	   app_item_property.set_property(‘TGW_HEADER.AR_BILLING_NUMBER_MIR‘, ENABLED, PROPERTY_ON);
	END IF;
...

Example 3:

This example illustrates how the default operating unit is retrieved from the database when selecting the operating unit.

When-Validate-Item trigger/Block Level When-Create-Record Trigger for Multiple Organizations Instance

...
 mo_global.set_policy_context(‘S‘,:block.org_id);
 /* Get Batch Source Header Defaults from the server. Pass the operating unit parameter to obtain the batch
 	 source defaults for the current operating unit. */
    arp_trx_defaults.get_header_defaults(param1, param2, … :block.org_id);
...

Operating Unit Cache Information

Product teams may wish to cache the commonly used operating unit information on starting forms. This avoids data validations that do not change often. In the setup forms, the information you cache might be less. For example, you may cache the ledger, currency, chart of accounts information and some system options columns or setup tables. For the transaction forms, you may cache the ledger, currency, chart of accounts information, and the system options columns and setup tables again. The difference between setup and transactions is that setup forms need few system options columns and transaction forms need many columns.

Before the access control feature, a user could only access one operating unit and hence, the operating unit specific information is cached on starting forms. Now, a user can access one, or multiple operating units. To accommodate this flexible security, the operating unit specific information is cached for the operating units that the application responsibility is assigned to when starting forms. Thus, there are multiple rows of cached data and to keep the memory footprint low, the number of cached columns must be limited. Therefore, product teams must identify the operating unit specific information that must be cached and cache the columns that are frequently accessed.

Product teams that wish to cache operating unit attributes on the forms tier must implement two PL/SQL packages. The first package must be created in the database and the second package in a forms library. The server-side package defines the commonly used product-specific datatypes and provides utilities that retrieve the operating unit attributes from the database. The forms library package consists of a data structure (cache) that holds the attributes and functions that the forms use to access the cached data. The forms library calls the server-side utility package to retrieve the operating unit attributes from the database and stores them in an internal data structure.

The following steps illustrate the method to implement caching in forms:

Step 1: Implement the server-side utility package

You must implement a server-side PL/SQL package to define product-specific data types and a procedure that retrieves the operating unit attributes from the database. Copy the server side API from the template, add product specific information and replace “xx” with the product short name in the templates and filenames.

You must modify the SQL to cache operating unit specific attributes as follows:

• Do not use the multiple organizations temporary table in the SQL code to check and obtain information about the operating unit assigned to a user directly, use the APIs, instead.
• A SQL statement must contain only one multiple organizations secured synonym. Modify joins of two or more views by referring to one secured synonym and referring the remaining views to the _ALL tables (similar to reference view standards).
• Add the ORG_ID filter to the WHERE clause of the SQL to avoid cartesian joins for tables that include the ORG_ID as part of the composite key or driving key.

Package File Naming Standards:
Package Specification: <XX>MOCSHS.pls
Package Body:            <XX>MOCSHB.pls
Where "XX" is the product short abbreviation.

CREATE OR REPLACE PACKAGE xx_mo_cache_utils AS

	-- 
	-- Define a record type that encapsulates one row of operating
	-- unit attributes
	--
	TYPE GlobalsRecord IS RECORD (
	--
	-- Generic columns needed by all products
	--
	ledger_id				gl_ledgers.ledger_id%TYPE,	
	ledger_name 			gl_ledgers.name%TYPE,
	chart_of_accounts_id 	gl_ledgers.chart_of_accounts_id%TYPE,	
	currency 				fnd_currencies.currency_code%TYPE

	--
	-- << Begin product-specific fields >>
	--
	-- Additional fields...
	-- <column1> 	<product system options.column name>%TYPE
	--
	-- << End product-specific fields >>

	);

	--
	-- Define data types (nested tables) for storing columns of
	-- the widely used Operating Unit attributes:
	-- Define a nested table type for storing the org_ids. This is
	-- mandatory
	--
	TYPE OrgIDTable					IS TABLE OF
hr_organization_information.organization_id %TYPE;

	-- Other nested table definitions. They should correspond to
	-- the fields of the record defined above.
	TYPE LedgerIDTable			IS TABLE OF gl_ledgers.ledger_id%TYPE;	
	TYPE LedgerNameTable		IS TABLE OF gl_ledgers.name%TYPE;
	TYPE ChartOfAccountsIDTable IS TABLE OF gl_ledgers.chart_of_accounts_id%TYPE;
	TYPE CurrencyCodeTable 		IS TABLE OF fnd_currencies.currency_code%TYPE;
	--
	-- << Begin product-specific nested tables definitions >>
	-- Additional fields...
	--
	-- TYPE <> IS TABLE OF <>%TYPE;
	-- << End product-specific nested tables definitions >>
	--
	-- Define a record type that encapsulates multiple rows of
	-- Operating Unit attributes:
	-- 
	TYPE GlobalsTable IS RECORD(
		org_id_t				OrgIDTable,
		ledger_id_t 			LedgerIDTable,
		ledger_name_t			LedgerNameTable,
		chart_of_accounts_id_t 	ChartOfAccountsIDTable,
		currency_code_t 		CurrencyCodeTable
		--
		-- << Begin product-specific fields >>
		-- Additional fields...
		--
		-- <> 		<>
		-- << End product-specific fields >>
		 );


		--
		-- This procedure retrieves Operating Unit attributes from the
		-- database and stores them into the specified data structure.
		--
		PROCEDURE retrieve_globals(p_globals OUT NOCOPY GlobalsTable);	

END xx_mo_cache_utils;

CREATE OR REPLACE PACKAGE BODY xx_mo_cache_utils AS

	--
	-- This procedure retrieves Operating Unit attributes from the
	-- database and stores them into the specified data structure.
	--
	PROCEDURE retrieve_globals(p_globals OUT NOCOPY GlobalsTable )
	IS
	BEGIN
	  --
	  -- This statement fetches Operating Unit attributes from the
	  -- database and stores them into nested tables using BULK
	  -- COLLECT
	  -- Use separate SQL for Multi-Org and Non Multi-Org
	  --
		SELECT <driving table>.org_id,
				gl.chart_of_accounts_id,
				gl.ledger_id,
				gl.name,
				fnd.currency_code
				--
				-- << Begin product-specific columns >>
				-- Additional columns
				-- <a.column1>	
				--
				-- << End product-specific columns >>
		BULK COLLECT
		INTO	p_globals.org_id_t,	
				p_globals.chart_of_accounts_id_t,
				p_globals.ledger_id_t,
				p_globals.ledger_name_t,
				p_globals.currency_code_t
				--
				-- << Begin product-specific nested tables >>
				-- Additional nested tables
				--
				-- p_globals.column1_t,
				-- << End product-specific nested tables >>
		   FROM gl_ledgers_public_v gl,
				fnd_currencies fnd
				--
				-- << Begin product-specific tables >>
				-- Additional tables (if necessary)
				-- <> a
				-- << End product-specific tables >>
				--
		 WHERE
			-- gl.ledger_id = a.set_of_books_id
			-- AND
			   gl.currency_Code = fnd.currency_code;
			--
			-- Add additional Where Clause
			--
			-- AND...
	EXCEPTION
	  WHEN no_data_found THEN
		RAISE EXCEPTION;
	  --
	  -- You should raise exception here if caching is critical to your
	  -- application. For example, the system options setup may be
	  -- incomplete or not done, in which case, the transaction form
	  -- should be closed prompting to the user to complete the
	  -- required setup first.
	  --
	END retrieve_globals;
END xx_mo_cache_utils;

Step 2: Implement the forms cache library package

You must implement a product specific version of the forms cache library. Copy the package below into your library and add other product specific information.

Library Naming Standards:
<XX>MOLOCH.pll 
where "XX" is the product short abbreviation.

PACKAGE xx_mo_local_cache IS
 
  --
  -- This procedure retrieves Operating Unit attributes and 
  -- stores them in the cache
  --
  PROCEDURE populate;
 
  --
  -- This function returns one row of cached data
  --
  FUNCTION get_org_attributes(p_org_id NUMBER)
  RETURN xx_mo_cache_utils.GlobalsRecord;
  
END xx_mo_local_cache;

PACKAGE BODY xx_mo_local_cache IS
	  
  --
  -- This index-by table is used to store rows of Operating Unit
  -- attributes
  --
  TYPE GlobalsCache IS TABLE OF xx_mo_cache_utils.GlobalsRecord
	 INDEX BY BINARY_INTEGER;
	  
  --
  -- This private variable is used as the cache
  --
  g_cache GlobalsCache;
  --
  -- This procedure retrieves Operating Unit attributes and stores  
  -- them in the cache
  --
  
  PROCEDURE populate IS
	 i    PLS_INTEGER;
	 l_gt xx_mo_cache_utils.GlobalsTable;
	
  BEGIN
	 -- First, remove existing records (if any):
	 g_cache.DELETE;
	 
    -- Next, get the data from the server:
	 xx_mo_cache_utils.retrieve_globals(l_gt);
	 
    -- Finally, store the data in the cache:
    IF l_gt.org_id_t.COUNT > 0 THEN
	   FOR i IN 1..l_gt.org_id_t.LAST LOOP
	     g_cache(l_gt.org_id_t(i)).chart_of_accounts_id	:= l_gt.chart_of_accounts_id_t(i);
	     g_cache(l_gt.org_id_t(i)).ledger_id			:= l_gt.ledger_id_t(i);
	     g_cache(l_gt.org_id_t(i)).ledger_name			:= l_gt.ledger_name_t(i);
	     g_cache(l_gt.org_id_t(i)).currency				:= l_gt.currency_code_t(i);
	     --
	     -- <>
	     -- Additional assignments ...
	     --
	     -- g_cache(l_gt.org_id_t(i)).)).<column1>		:= l_gt.<column1>_t(i);
        -- <>
	     --
	      END LOOP;
	   END IF;
	 
    END populate;
	 

	 --
	 -- This function returns one row of cached data. This function
	 -- may or may not be needed. It depends on how you access the
	 -- information in the server cache.
	 --
    FUNCTION get_org_attributes(p_org_id NUMBER)
	 RETURN xx_mo_cache_utils.GlobalsRecord
	 IS
	 
	 BEGIN
	   RETURN g_cache(p_org_id);
	 
    EXCEPTION

      WHEN no_data_found THEN
        RAISE EXCEPTION with the org_id value;
	   --
	   -- You should raise exception here if caching is critical
	   -- to your application. You will get this exception when you try
      -- to copy an org not available in PL/SQL table. 
      -- For example, if you have access to 2 orgs say org-1, org-2 and 
      -- the setup is complete for org-1, but not for org-2, then you 
      -- will get exception for org-2 since the populate API would not
      -- have populated the PL/SQL table with org-2 information.
      --
	   -- Prompting to the user with the message of which org‘s setup
	   -- is incomplete may be useful.
      -- 

      WHEN value_error THEN
	     RAISE EXCEPTION;

	 END get_org_attributes;

END xx_mo_local_cache;

Step 2a: Implement the server side cache package (Optional)

This step is essential for product teams that use server side caching, to validate the data. Product teams must not pass the cache from the client as additional parameters to the server APIs since the operating unit specific information is cached in the server.

The operating unit specific information is currently cached in the server in multiple packages depending on the functionality. The initialization code is entered in the anonymous block of the PL/SQL package and is executed when referring the package for the first time and the package remains same throughout the session.

With access control, the server side caching can be consolidated into a single package, which is basically the server package mentioned in Step 1 above. The following step explains the steps to implement the cache in the server. When invoking the form, it runs in a separate database session. Therefore, the server and client are in sync during initialization.

You must implement a product specific version of the server cache package. This is similar to step 2 except that the cache resides in the server. Copy the following package and add other product specific information.

Package File Naming Standards:
Package Specification: XXMOGLCS.pls
Package Body: XXMOGLCB.pls
Where "XX" is the product short abbreviation

CREATE OR REPLACE PACKAGE xx_mo_global_cache AS
	
  --
  -- This procedure retrieves Operating Unit attributes and
  -- stores them in the cache
  --  
  PROCEDURE populate;
	
  --
  -- This function returns one row of cached data
  --
  FUNCTION get_org_attributes(p_org_id NUMBER)
  RETURN xx_mo_cache_utils.GlobalsRecord;
	
END xx_mo_global_cache;

CREATE OR RELACE PACKAGE BODY xx_mo_global_cache AS
  --
  -- This index-by table is used to store rows of Operating Unit
  -- attributes
  --
  TYPE GlobalsCache IS TABLE OF xx_mo_cache_utils.GlobalsRecord INDEX BY BINARY_INTEGER;
  --
  -- This private variable is used as the cache
  --
  g_cache GlobalsCache;
  --
  -- This procedure retrieves Operating Unit attributes and stores
  -- them in the cache
  --
  PROCEDURE populate IS
  	 i	 PLS_INTEGER;
	 l_gt xx_mo_cache_utils.GlobalsTable;
  BEGIN
  	 -- First, remove existing records (if any): 
    g_cache.DELETE;
 
    -- Next, get the data from the server: 
    xx_mo_cache_utils.retrieve_globals(l_gt);
	 -- Finally, store the data in the cache:
	 IF l_gt.org_id_t.COUNT > 0 THEN
	   FOR i IN 1..l_gt.org_id_t.LAST LOOP
        g_cache(l_gt.org_id_t(i)).chart_of_accounts_id	:= l_gt.chart_of_accounts_id_t(i); 
        g_cache(l_gt.org_id_t(i)).ledger_id				:= l_gt.ledger_id_t(i);
	     g_cache(l_gt.org_id_t(i)).ledger_name			:= l_gt.ledger_name_t(i);
        g_cache(l_gt.org_id_t(i)).currency				:= l_gt.currency_code_t(i); 
	     --
        -- <>
        -- Additional assignments ...
        --
        -- g_cache(l_gt.org_id_t(i)).<column1>	:= l_gt.<column1>_t(i);
        -- <>
        --
      END LOOP;
    END IF;
  END populate;


  --
  -- This function returns one row of cached data. This function 
  -- may or may not be needed. It depends on how you access the
  -- information in the server cache.
  --
  FUNCTION get_org_attributes(p_org_id NUMBER)
  RETURN xx_mo_cache_utils.GlobalsRecord
  IS

  BEGIN
    RETURN g_cache(p_org_id);

  EXCEPTION
    WHEN no_data_found THEN
      RAISE EXCEPTION with the org_id value;
    --
    -- You should raise exception here if caching is critical
    -- to your application. You will get this exception when you try
    -- to copy an org not available in PL/SQL table. 
    -- For example, if you have access to 2 orgs say org-1, org-2 and
    -- the setup is complete for org-1, but not for org-2, then you
    -- will get exception for org-2 since the populate API would not
    -- have populated the PL/SQL table with org-2 information.
    --
    -- Prompting to the user with the message of which org‘s setup
    -- is incomplete may be useful.
    --

    WHEN value_error THEN
      RAISE EXCEPTION;
  
  END get_org_attributes;

END xx_mo_global_cache;

Step 3: Modify the pre-form trigger

In your pre-form trigger, you must include code to call the client package (xx_mo_local_cache) to populate the client side PL/SQL table with cache.

If your product uses server side cache, then you must include the code to call the server package (xx_mo_global_cache) to populate the server side PL/SQL table with the cache.

Pre-Form Trigger

DECLARE
   l_gr xx_mo_cache_utils.GlobalsRecord;
   FDRCSID(‘$Header: ARXTWMAI.fmb 115.80 2000/10/05 10:34  jskhan ship
$‘);                                                                                                                                                                                                                                                                   $‘); 
   FND_STANDARD.FORM_INFO(‘$Revision: 115.80
   $‘, ‘ARXTWMAI.fmb‘, ‘AR‘,
	                       ‘$Date: 2000/10/05 10:34  $‘, ‘$Author: djancis $‘);
	
	   app_standard.event(‘PRE-FORM‘);
	
	   -- Initialize client cache
	   xx_mo_local_cache.populate;  
	
	   -- If you make use of server side cache in the forms then
      -- initialize the server cache here
	   xx_mo_global_cache.populate;
	
	   -- Add Other code here ...
	   -- Copying current org data for Multi-Org case is handled in When
      -- Validate-Item trigger.
...
END;

Step 4: Modify the WHEN-CREATE_RECORD trigger of the form block

You must modify the when-create-record trigger of your operating unit block to copy the current operating unit specific information from the cache to the parameter or non-base table block. When the default operating unit is available, the caching depends on the default organization.

If you set the correct dynamic policy context, then you can obtain the current_org_id by calling the Multiple Organizations API mo_global.get_current_org_id for product teams that must initialize the server side caching for validations on the server.

When_Create-Record Trigger

DECLARE
	   l_gr  xx_mo_cache_utils.GlobalsRecord;
	
BEGIN
    
   -- Check if the default OU is available.
   -- If so, copy default OU to form block 
   IF :parameter.mo_default_org_id is not null and 
	   :block.org_id is null then
	   :block.org_id = :parameter.mo_default_org_id;
	   :block.operating_unit := :parameter.mo_default_ou_name;
	END IF;
	    
	-- Check if the block org is set. Then check if the operating 
   -- unit available as default is the same as the one available in 
   -- parameter or a non-base table block. If same, then do not copy 
   -- again from cache. This ensures that you do not refresh the 
   -- parameter or a non base table block if you continue to enter 
   -- transactions for the org which is same as the default org.
	IF : is not null     
	  IF : <> nvl(:,-99)
THEN
       -- Get the current Org attributes from client side cache
	    l_gr := xx_mo_local_cache.get_org_attributes(:<>.org_id);
	      
	    -- Copy from cache to parameter block or non base table block
	    -- You can replace parameter block shown here with a non 
       -- base table block      
	    :parameter.chart_of_accounts_id	:= l_gr.chart_of_accounts_id;
	    :parameter.ledger_id			:= l_gr.ledger_id;
	    :parameter.ledger_name			:= l_gr.ledger_name;
	    :parameter.currency_code		:= l_gr.currency_code;
	    /* <> */
	    -- Additional assignments...
	    :parameter.<column1>			:= l_gr.column1;
	    /* <> */
	 
	    -- Copy the block org_id to parameter.old_org_id
	    :parameter.old_org_id			:= <:block name.org_id>;
	  END IF;
	ELSE
	  -- Copy null to parameter columns
	END IF;
	
	-- Pass the ORG_ID to server code to use the server cache for the     
   -- current org for the record validations 
	
	-- Get Batch Source Header Defaults 
   arp_trx_defaults.get_header_defaults(param1, param2, ...,:block.org_id);
	
 -- Other Code --
...
END;

Step 5: Modify the WHEN-VALIDATE-ITEM trigger of the Operating Unit field (as well as Operating Unit specific fields used in the derive operating feature)

After the user selects an operating unit, the current operating unit record must be copied from the cache to the parameter or non-base table block.

If you set the correct policy context, then you can obtain the current_org_id by calling the Multiple Organizations API mo_global.get_current_org_id for product teams that must initialize server side caching for validations on the server.

When-Validate-Item Trigger

DECLARE
		l_gr xx_mo_cache_utils.GlobalsRecord;
BEGIN
  -- Check if the new Operating Unit selected by the user is the same
  -- as the old Operating Unit that is available in the parameter or
  -- a non-base table block. If same then do not copy again from
  -- cache
  IF :<block name.org_id> is not null THEN
    IF :<block name.org_id> <> nvl(:<parameter.old_org_id>,-99) THEN
    
       -- Get the current Org attributes from client side cache
		l_gr := xx_mo_local_cache.get_org_attributes(:<>.org_id); 

		-- Copy from cache to parameter block or a non base table block
		-- You can replace parameter block shown here
		-- with any non base table block
		:parameter.chart_of_accounts_id	:= l_gr.chart_of_accounts_id;
		:parameter.ledger_id			:= l_gr.ledger_id;
		:parameter.ledger_name			:= l_gr.ledger_name;
		:parameter.currency_code		:= l_gr.currency_code;
		/* <> */
		-- Additional assignments...
		:parameter.<column1>			:= l_gr.column1;
		/* <> */
		-- Copy the block org_id to parameter.old_org_id
		:parameter.old_org_id			:= <:block name.org_id>; 

	 END IF;
  ELSE
	 -- Copy null to parameter columns
  END IF;

  -- Pass the ORG_ID to server code to use the server cache for the
  -- current org for the record validations 
  -- Get Batch Source Header Defaults
  arp_trx_defaults.get_header_defaults(param1, param2, ...,:block.org_id);
  
  -- Other code --

END;

Step 6: Modify the block level When-New-Record-Instance trigger of the Operating Unit field block

When the user tries to modify any attribute of a transaction after saving it, the operating record must be copied from the cache to the parameter or non-base table block, and use the operating unit to validate and control the display properties of the items in the record. The parameter or non-base table block is populated with the current organization cache when the user navigates for one record to another after querying the records.

If you set the correct dynamic policy context, then you can obtain the current_org_id by calling the Multiple Organizations API mo_global.get_current_org_id for product teams that must initialize server side caching for validations on the server.

Use the when-new-record-instance trigger to detect the updates and refresh the cache.

When-New-Record-Instance Trigger

DECLARE
		l_gr xx_mo_cache_utils.GlobalsRecord;
BEGIN
  
  -- Check if the new Operating Unit selected by the user is the
  -- same as the old Operating Unit that is available in the
  -- parameter or non-base table block. If same then do not copy
  -- again from cache
  IF :<block name.org_id> is not null THEN
	 IF :<block name.org_id> <> nvl(:<parameter.old_org_id>, -99) THEN
  
  		-- Get the current Org attributes from client side cache
		l_gr := xx_mo_local_cache.get_org_attributes(:<>.org_id);
 
		-- Copy from cache to parameter block or non base table block
		-- You can replace parameter block shown here
  		-- with any non-base table block
		:parameter.chart_of_accounts_id	:= l_gr.chart_of_accounts_id;
		:parameter.ledger_id			:= l_gr.ledger_id;
		:parameter.ledger_name			:= l_gr.ledger_name;
		:parameter.currency_code		:= l_gr.currency_code;
		/* <> */

		-- Additional assignments...
		:parameter.<column1>			:= l_gr.column1;
		/* <> */
		-- Copy the block org_id to parameter.old_org_id
		:parameter.old_org_id			:= <:block name.org_id>;
    END IF;
  END IF;
  -- Pass the ORG_ID to server code to use the server cache for the
  -- current org for the record validations
  -- Get Batch Source Header Defaults
  arp_trx_defaults.get_header_defaults(param1, param2, ...,:block.org_id);

  -- Other code --
END;

Step 7: Modify the block level Post-Query trigger of the Operating Unit field block

In forms if the operating unit specific display fields are populated after the post query trigger, you must synchronize the cache based on the record’s operating unit.

If you set the correct policy context, then you can obtain the current_org_id by calling the Multiple Organizations API mo_global.get_current_org_id for product teams that must initialize server side caching for validations on the server.

Post-Query Trigger

DECLARE
		l_gr xx_mo_cache_utils.GlobalsRecord;
BEGIN
  
  -- Check if the new Operating Unit selected by the user is the
  -- same as the old Operating Unit that is available in the
  -- parameter or non-base table block. If same then do not copy
  -- again from cache
  IF :<block name.org_id> is not null THEN
	 IF :<block name.org_id> <> nvl(:<parameter.old_org_id>, -99) THEN
  
  		-- Get the current Org attributes from client side cache
		l_gr := xx_mo_local_cache.get_org_attributes(:<>.org_id);
 
		-- Copy from cache to parameter block or non base table block
		-- You can replace parameter block shown here
  		-- with any non-base table block
		:parameter.chart_of_accounts_id	:= l_gr.chart_of_accounts_id;
		:parameter.ledger_id			:= l_gr.ledger_id;
		:parameter.ledger_name			:= l_gr.ledger_name;
		:parameter.currency_code		:= l_gr.currency_code;
		/* <> */

		-- Additional assignments...
		:parameter.<column1>			:= l_gr.column1;
		/* <> */
		-- Copy the block org_id to parameter.old_org_id
		:parameter.old_org_id			:= <:block name.org_id>;
    END IF;
  END IF;
  -- Pass the ORG_ID to server code to use the server cache for the
  -- current org for the record validations
  -- Get Batch Source Header Defaults
  arp_trx_defaults.get_header_defaults(param1, param2, ...,:block.org_id);

  -- Other code --
END;

Step 8: Modify the block level Pre-Insert trigger of the Operating Unit field block

You need this trigger if your form allows you to commit multiple records, by synchronizing the cache.

Pre-Insert Trigger

DECLARE
		l_gr xx_mo_cache_utils.GlobalsRecord;
BEGIN
  
  -- Check if the new Operating Unit selected by the user is the
  -- same as the old Operating Unit that is available in the
  -- parameter or non-base table block. If same then do not copy
  -- again from cache
  IF :<block name.org_id> is not null THEN
	 IF :<block name.org_id> <> nvl(:<parameter.old_org_id>, -99) THEN
  
  		-- Get the current Org attributes from client side cache
		l_gr := xx_mo_local_cache.get_org_attributes(:<>.org_id);
 
		-- Copy from cache to parameter block or non base table block
		-- You can replace parameter block shown here
  		-- with any non-base table block
		:parameter.chart_of_accounts_id	:= l_gr.chart_of_accounts_id;
		:parameter.ledger_id			:= l_gr.ledger_id;
		:parameter.ledger_name			:= l_gr.ledger_name;
		:parameter.currency_code		:= l_gr.currency_code;
		/* <> */

		-- Additional assignments...
		:parameter.<column1>			:= l_gr.column1;
		/* <> */
		-- Copy the block org_id to parameter.old_org_id
		:parameter.old_org_id			:= <:block name.org_id>;
    END IF;
  END IF;
  -- Pass the ORG_ID to server code to use the server cache for the
  -- current org for the record validations
  -- Get Batch Source Header Defaults
  arp_trx_defaults.get_header_defaults(param1, param2, ...,:block.org_id);

  -- Other code --
END;

Step 9: Modify the block level Pre-Update trigger of the Operating Unit field block

You need this trigger if your form allows you to commit multiple records, by synchronizing the cache.

Pre-Update Trigger

DECLARE
		l_gr xx_mo_cache_utils.GlobalsRecord;
BEGIN
  
  -- Check if the new Operating Unit selected by the user is the
  -- same as the old Operating Unit that is available in the
  -- parameter or non-base table block. If same then do not copy
  -- again from cache
  IF :<block name.org_id> is not null THEN
	 IF :<block name.org_id> <> nvl(:<parameter.old_org_id>, -99) THEN
  
  		-- Get the current Org attributes from client side cache
		l_gr := xx_mo_local_cache.get_org_attributes(:<>.org_id);
 
		-- Copy from cache to parameter block or non base table block
		-- You can replace parameter block shown here
  		-- with any non-base table block
		:parameter.chart_of_accounts_id	:= l_gr.chart_of_accounts_id;
		:parameter.ledger_id			:= l_gr.ledger_id;
		:parameter.ledger_name			:= l_gr.ledger_name;
		:parameter.currency_code		:= l_gr.currency_code;
		/* <> */

		-- Additional assignments...
		:parameter.<column1>			:= l_gr.column1;
		/* <> */
		-- Copy the block org_id to parameter.old_org_id
		:parameter.old_org_id			:= <:block name.org_id>;
    END IF;
  END IF;
  -- Pass the ORG_ID to server code to use the server cache for the
  -- current org for the record validations
  -- Get Batch Source Header Defaults
  arp_trx_defaults.get_header_defaults(param1, param2, ...,:block.org_id);

  -- Other code --
END;

Step 10: Modify the block level Pre-Record trigger of the Operating Unit field block

You need this trigger if your form forces users to commit the record before navigating to the next record.

Pre-Record Trigger

DECLARE
		l_gr xx_mo_cache_utils.GlobalsRecord;
BEGIN
  
  -- Check if the new Operating Unit selected by the user is the
  -- same as the old Operating Unit that is available in the
  -- parameter or non-base table block. If same then do not copy
  -- again from cache
  IF :<block name.org_id> is not null THEN
	 IF :<block name.org_id> <> nvl(:<parameter.old_org_id>, -99) THEN
  
  		-- Get the current Org attributes from client side cache
		l_gr := xx_mo_local_cache.get_org_attributes(:<>.org_id);
 
		-- Copy from cache to parameter block or non base table block
		-- You can replace parameter block shown here
  		-- with any non-base table block
		:parameter.chart_of_accounts_id	:= l_gr.chart_of_accounts_id;
		:parameter.ledger_id			:= l_gr.ledger_id;
		:parameter.ledger_name			:= l_gr.ledger_name;
		:parameter.currency_code		:= l_gr.currency_code;
		/* <> */

		-- Additional assignments...
		:parameter.<column1>			:= l_gr.column1;
		/* <> */
		-- Copy the block org_id to parameter.old_org_id
		:parameter.old_org_id			:= <:block name.org_id>;
    END IF;
  END IF;
  -- Pass the ORG_ID to server code to use the server cache for the
  -- current org for the record validations
  -- Get Batch Source Header Defaults
  arp_trx_defaults.get_header_defaults(param1, param2, ...,:block.org_id);

  -- Other code --
END;

Modify Record Groups for Operating Unit Specific Fields

You must modify the Operating Unit specific field records as follows if the Operating Unit field select or derive operating unit features:

• Do not use the multiple organizations temporary table directly in the SQL query. Instead, use the PL/SQL functions to check the operating unit access and obtain the operating unit information, .
• Modify the limit reference to one multiple organizations secured synonym and the rest of the references to _ALL tables (similar to Reference view standards) for record group SQL joining two or more views.
• Add the ORG_ID filter to the WHERE clause of the record group SQL to avoid cartesian joins for tables that include ORG_ID as the composite or driving key.
• Do not include the form block ORG_ID in the record group SQL as the policy context handles single and multiple operating units data. See Setting the Policy Context.
• Set the policy context in the When-Validate-Item trigger of the Operating Unit field and the Operating Unit specific fields to derive the operating units for forms that support the "Derive Operating Unit" feature.

Example 1: Record groups using the derive operating feature

The list of values is enabled for record groups that use the “Derive Operating Unit” feature. If the operating unit field is empty, the current organization is not set and the access mode is multiple. Therefore, the record group SQL returns data for multiple operating units.

If the access mode is single when you select the operating unit and define the current organization, then the list of values returns the data for the selected operating unit.

select bs.name source, bs.batch_source_id batch_source_id, 
	
		bs.description description,
		bs.auto_trx_numbering_flag auto_trx_numbering_flag,
		bs.batch_source_type batch_source_type,
		bs.default_inv_trx_type default_inv_trx_type,
		ctt.name default_type_name, bs.org_id,
		mo_global.get_ou_name(bs.org_id)
  from ra_cust_trx_types_all ctt,
		ra_batch_sources bs
 where bs.default_inv_trx_type = ctt.cust_trx_type_id(+)
	and bs.org_id = ctt.org_id(+)
	and nvl(:tgw_header.ctt_class,‘-99‘) = decode(:tgw_header.ctt_class, null, ‘-99‘, ctt.type(+) )
	and nvl(:tgw_header.trx_date,trunc(sysdate)) between nvl(bs.start_date,nvl(:tgw_header.trx_date,trunc(sysdate)))
	and nvl(bs.end_date, nvl(:tgw_header.trx_date, trunc(sysdate)))
	and nvl(:tgw_header.trx_date,trunc(sysdate)) between nvl(ctt.start_date(+),nvl(:tgw_header.trx_date,trunc(sysdate)))
	and nvl(ctt.end_date(+), nvl(:tgw_header.trx_date, trunc(sysdate)))
	and nvl(bs.status, ‘A‘) = ‘A‘
	and ( bs.batch_source_type =‘INV‘ or :tgw_header.ctt_class = ‘CM‘ )
	and bs.batch_source_id not in (11, 12)
	and ( :tgw_header.trx_number is null
		  or bs.auto_trx_numbering_flag = nvl(:tgw_header.bs_auto_trx_numbering_flag,‘N‘))
	order by bs.name, bs.description, bs.batch_source_id

In the above example, the ORG_ID filter is added to avoid cartesian join.

Example 2: Record groups using the derive operating feature

In this example, it is not necessary to add the ORG_ID filter in the where clause to join RA_SITE_USES and AR_CONS_INV views as the site_use_id is unique and sufficient to determine the organization.

select ci.cons_billing_number,
		ci.customer_id,
		ci.site_use_id,
		cu.customer_name,
		cu.customer_number,
		su.location,
		su.org_id,
		mo_global.get_ou_name(su.org_id)
  from ar_cons_inv_all ci,
		ra_customers cu,
		ra_site_uses su
 where ci.currency_code = :rgw_folder.currency_code
   and ci.site_use_id = su.site_use_id
   and ci.customer_id = cu.customer_id
 order by cons_billing_number

Example 3: Record groups using select operating feature

The list of values is disabled for record groups using the "Select Operating Unit" feature until the user selects an operating unit. The current organization and the access mode are defined after the user selects the operating unit. Therefore, the record group SQL always returns data for one operating unit.

select max(tc.name) name,
		lc.displayed_field type,
		tc.description
  from ap_lookup_codes lc,
		ap_tax_codes tc
 where lc.lookup_type = ‘TAX TYPE‘
   and tc.tax_type != ‘OFFSET‘
   and tc.tax_type != ‘AWT‘
   and lc.lookup_code = tc.tax_type
   and nvl(tc.enabled_flag,‘Y‘)=‘Y‘
 group by tc.name, lc.displayed_field, tc.description

You can rewrite the previous statement to use the ALL tables instead of secured synonyms by passing the form block ORG_ID as shown in the following code:

select max(tc.name) name,
		lc.displayed_field type,
		tc.description
		from ap_lookup_codes lc,
		ap_tax_codes_ALL tc
 where lc.lookup_type = ‘TAX TYPE‘
	and tc.tax_type != ‘OFFSET‘
	and tc.tax_type != ‘AWT‘
	and lc.lookup_code = tc.tax_type
	and nvl(tc.enabled_flag,‘Y‘)=‘Y‘
	and tc.org_id = :<block_name.org_id>
 group by tc.name, lc.displayed_field, tc.description

Add ORG_ID Predicate in Client/Server Code

On the client side and server side application code, SQL statements issue Data Manipulation Language (DML) for multiple organizations views. The APIs that validate the data in an operating unit benefit from using the current organization ID set by the triggers before firing the validation logic, rather than passing the ORG_ID parameter. If you use the same API in the reference view and server side validation for forms, then you must modify the API to include ORG_ID input parameter as described in Reference Views.

• Do not use the multiple organizations temporary table directly in the SQL query. Instead, use the PL/SQL functions to check the operating unit access and obtain the operating unit information.
• Rewrite SQL joins with two or more views to use one secured synonym based on the driving table for the query and the rest of the views replaced by _ALL tables. High selective and low data volume objects are good candidates for the driving object.
• Add the ORG_ID filter to the WHERE clause of the record group SQL to avoid cartesian joins for tables that include ORG_ID as the composite or driving key.

Example 1:

BEGIN
  SELECT
		NVL(copy_doc_number_flag, ‘N‘)
  INTO
		l_copy_doc_number_flag
  FROM
		ra_batch_sources
  WHERE
		batchsource_id = l_ct_rec.batch_source_id
EXCEPTION
  WHEN NO_DATA_FOUND THEN
	 l_copy_doc_number_flag := ‘N‘;
END;

Example 2:

l_trx_str := ‘select ra_trx_number_‘ ||
				REPLACE(p_trx_rec.batch_source_id, ‘-‘, ‘N‘) ||
				l_org_str||
				‘_s.nextval trx_number ‘ ||
				‘from ra_batch_sources ‘ ||
				‘where batch_source_id = ‘ ||
				p_trx_rec.batch_source_id ||
				‘ and auto_trx_numbering_flag = ‘‘Y‘‘‘
 EXECUTE IMMEDIATE l_trx_str
	 INTO l_trx_number;

Modify table handlers

The default value that refers to the CLIENT_INFO organization context for ORG_ID column is used in the base _ALL tables in releases prior to Release 12. However, with the multiple organizations access control feature, this database default is no longer valid as the user can access multiple operating unit sand the CLIENT_INFO will not be set. Therefore, the ORG_ID column value must be explicitly specified in the table handlers.

For insert statements, you must pass the ORG_ID column value to the table handlers. For update statements, if you use a primary key column in your selection criteria, then you do not need the ORG_ID value in the table handler. The following are examples of insert, update and delete statements:

Example 1: An insert statement

insert into <table*>
			 (<column1>
			 <column2>
			 ...
			 <org_id>)
values ( <value1>,
		  <value2>,
		  ...
		  p_org_id)

* indicates that a synonym is attached to the multiple organizations security policy.

Example 2: An update statement

update <table>
	set <column1> = <value1>
where primary_key = <value>

Example 3: A delete statement

In this example, the primary key is used to update and delete statements. Hence, the ORG_ID filter is not added.

DELETE FROM ra_customer_trx
WHERE customer_trx_id = p_customer_trx_id;

Allow Query on Operating Unit field

The operating unit field is a non-database item. To query by operating units, the operating unit value must be derived from the ORG_ID database column. You do this in the pre-query and post-query triggers by using the API: MO_UTILS.Get_Org_Name.

Block Level Post-Query Trigger

  :<your block name>.operating_unit :=
mo_utils.get_org_name(:<your block name>.org_id);
  set_record_property(:system.trigger_record,:system.trigger_block,STATUS,QUERY_STATUS);

MO_UTILS.Get_Org_Name

FUNCTION Get_Org_Name( p_org_id NUMBER )
RETURN VARCHAR2
IS
  l_return hr_all_organization_units_tl.name%TYPE;
BEGIN
  SELECT name
	 INTO	l_return
    FROM	hr_all_organization_units_tl
    WHERE	organization_id = p_org_id
	 AND	language = userenv(‘LANG‘);
 
  IF SQL%NOTFOUND
  THEN
    l_return := NULL;
  END IF;
 
  RETURN l_return;
 
END Get_Org_Name;

You must modify the PRE-QUERY trigger of the operating unit block forms to enable the Query Enter functionality for the operating unit name. The trigger must dynamically modify the DEFAULT_WHERE property of the block to append a LIKE sub-query that examines the hr_operating_units view for records that matches the string entered in the Operating Unit field.

Block Level PRE-QUERY Trigger

DECLARE
  block_id Block := FIND_BLOCK(‘<block name>‘);

  sub_where VARCHAR2(512);
  def_where VARCHAR2(512);

  -- Local function definition:
  FUNCTION add_and(p_where IN VARCHAR2) RETURN VARCHAR2
  IS
  BEGIN
    IF (NVL(NVL(length(p_where), 0), 0) != 0) THEN
	   RETURN( p_where || ‘ AND ‘);
	 ELSE
	   RETURN( p_where );
	 END IF;

	 RETURN NULL;
  END;

BEGIN
  sub_where := NULL;

  IF (<block name>.operating_unit IS NOT NULL) THEN
	 sub_where := add_and(sub_where) || ‘(NAME LIKE ‘‘‘|| :<block name>.OPERATING_UNIT||‘‘‘)‘;
  END IF;

  IF (sub_where IS NOT NULL) THEN
	 def_where := add_and(def_where) || ‘((ORG_ID) IN ‘||‘(SELECT
ORGANIZATION_ID‘||‘FROM
HR_OPERATING_UNITS WHERE ‘|| sub_where || ‘))‘;
  END IF;

  -- Specify the default WHERE clause for the block.
  -- This will NOT override a value established at design time
  -- via the Property Palette for the block‘s WHERE clause property.

  set_block_property(block_id, DEFAULT_WHERE, def_where);

END;

Handle Flexfields

Accounting Key Flexfields

The chart of accounts ID associated with the accounting flexfields depends on the ledger associated with the operating unit. To ensure accounting flexfields work properly, you must pass the chart of accounts ID as an input parameter to the API that defines key flexfield. When you change the operating unit by selecting the operating unit from the list of values or by deriving the operating unit from any operating unit specific attributes of the transaction, the chart of accounts value must be refreshed from the cache.

The ledger ID or chart of accounts ID is determined when the user selects or derives an operating unit. The user cannot know the ledger and chart of accounts ID when initializing the forms if the user can access multiple operating units.

To enable access control for accounting flexfields, complete the following:

1. Add a new item CHART_OF_ACCOUNTS_ID to your form block of your canvas. Use this item and not the parameter chart_of_accounts_id as using this item calls to fnd_key_flex.define.
2. Call the fnd_key_flex.define in the following triggers:
   1. Block Level: When-Create-Record
   2. Item Level: When-Validate-Item of Operating Unit field and Operating Unit specific fields for “Derive Operating Unit” feature.
   3. Block Level: Post-Query
   4. Block Level: Pre-Query (to query accounting flexfields)
3. Disable calling the fnd_flex.define at the form level and block level Pre-Query trigger. Otherwise, the following error appears: APP-FND-01016: Routine FDFRKS: Unknown structure ID for flexfield code GL number with application ID 101 during blind query for multiple organizations case.

The following code defines the key flexfield structure for inserts, updates and queries. Block Level Post-query trigger/When-Create-Record trigger/When-Validate-Item trigger on the Operating Unit field and other Operating Unit specific fields for deriving Operating Unit

Block Level Post-query Trigger/When-Create-Record Trigger/When-Validate-Item Trigger on the Operating Unit Field and Other Operating Unit Specific Fields for Deriving the Operating Unit

IF (:<your block name.org_id> IS NOT NULL) AND (:<your block name.operating_unit> IS NOT NULL) THEN
  
  IF :<block name.org_id> <> nvl(:<parameter.old_org_id>, -99) THEN
  
	 l_gr := <>_MO_local_cache.get_org_attributes(:<>.org_id);
	 :<block name>.chart_of_account_id := l_gr.chart_of_account_id;
    /* Initialize Other Parameters */
    ...
    FND_KEY_FLEX.DEFINE( 
						BLOCK=>‘<your block name>‘,
						FIELD =>‘<your field name>‘,
						TITLE =>:<block name>Field_name, …
						NUM => <block name>.chart_of_account_id, ...);
	 ...
  END IF;
END IF;

Querying on Accounting Flexfields

You must disable the key flexfield (fnd_flex.event call) in the form and block level pre-query triggers and re-enable in the block level post-query trigger to not query the accounting flexfields in your form. Set the "QUERY ALLOWED" item property to No to not allow enter-query on accounting flexfields.

To query accounting flexfields, add additional logic in the block level pre-query trigger to handle enter query. Use the accounting flexfield in the query if the operating unit is specified. In other words, the accounting flexfield field is dependent on the Operating Unit field. However, the control item properties, cannot be controlled to set dependent items during enter-query and a message is displayed asking users to enter a unique Operating Unit when they execute the enter-query.

The following table lists the circumstances for displaying the messages:

The following code handles enter-query on accounting flexfields:

PROCEDURE PRE_QUERY IS
		 l_org_id		NUMBER(15);
		 l_gr			AP_MO_cache_utils.GlobalsRecord;
		 l_no_ou_found	EXCEPTION;
BEGIN
	 IF :invoices_folder.operating_unit IS NULL THEN
	   IF :invoices_folder.liability_account IS NOT NULL THEN
		 fnd_message.set_name(‘FND‘,‘MO_SRCH_OU_REQUIRED‘);
		 fnd_message.error;
		 RAISE FORM_TRIGGER_FAILURE;
	   END IF;
	 ELSEIF :invoices_folder.operating_unit IS NOT NULL THEN
		IF :invoices_folder.liability_account IS NOT NULL THEN
		  BEGIN
			SELECT organization_id
			  INTO l_org_id
			  FROM hr_operating_units
			 WHERE organization_name like :invoices_folder.operating_unit
			   AND mo_global.check_access(organization_id) = ‘Y‘;
		  EXCEPTION
			 WHEN TOO_MANY_ROWS THEN
				fnd_message.set_name(‘FND‘,‘MO_SRCH_MULT_OU_FOUND‘);
				fnd_message.error;
			 WHEN NO_DATA_FOUND THEN
  fnd_message.set_name(‘FND‘,‘MO_SRCH_NO_OU_FOUND‘);
  fnd_message.error;
  
  END;
 
  l_gr := ap_mo_local_cache.get_org_attributes(l_org_id);
  :invoices_folder.chart_of_accounts_id := l_gr.chart_of_accounts_id;
  fnd_key_flex.define(
		 BLOCK=>‘INVOICES_FOLDER‘,
		 FIELD=>‘LIABILITY_ACCOUNT‘,
		 DESCRIPTION=>‘LIABILITY_DESCRIPTION‘,
		 ID=>‘ACCTS_PAY_CODE_COMBINATION_ID‘,
		 APPL_SHORT_NAME=>‘SQLGL‘,
		 CODE=>‘GL#‘,
		 NUM=>‘:invoics_folder.CHART_OF_ACCOUNTS_ID‘,
		 VDATE=>‘:INVOICES_FOLDER.GL_DATE‘,
  		 VRULE=>‘GL_global\\nDETAIL_POSTING_ALLOWED\\nI\\nAPPL=SQLAP;NAME=AP_ALL_POSTING_NA\\nY\\0\\nSUMMARY_FLAG\\nI\\nAPPL=SQLAP;
		 
		 NAME=AP_ALL_PARENT_FLEX_NA\\nN\\0GL_ACCOUNT\\nGL_ACCOUNT_TYPE\\nI\\nNAME=AP_ALL_ONLY_LIAB\\nL‘,REQUIRED=>‘N‘);
  
		 fnd_flex.event(‘PRE-QUERY‘);
      END IF;
    END IF;
  EXCEPTION
	 WHEN l_no_ou_found THEN
	   NULL;
	 WHEN OTHERS THEN
	   RAISE FORM_TRIGGER_FAILURE;
END;

For master-detail blocks, that include the accounting flexfield in both master and detail blocks, the pre-query trigger must call the key flexfields in the master block and not in the detail block. However, the pre-query trigger in the detail block must have the code to update the block‘s chart of accounts ID and call the fnd_flex.event as shown in the following code:

Block Level Pre-Query trigger

IF (:<your block name.org_id> IS NOT NULL) AND
(:<your block name.operating_unit> IS NOT NULL) THEN
  IF :<block name.org_id> <> nvl(:<parameter.old_org_id>, -99) THEN
    l_gr := <>_MO_local_cache.get_org_attributes(:<>.org_id);
  
	 :<block name>.chart_of_account_id := l_gr.chart_of_account_id;
	 fnd_flex.event(‘Pre-Query‘);
  END IF;
END IF;

Descriptive Flexfields

Descriptive flexfield segments have global or operating unit specific information. The descriptive flexfield contents must be cleared when changing the Operating Unit field. You must modify the value sets and default value for the descriptive segments based on multiple organization views (single organization or reference views) to perform the following:

• Do not use the multiple organizations temporary table directly in the SQL query. Instead, use the PL/SQL functions to check the operating unit access and obtain the operating unit information.
• Handle ORG predicate for views that depend on tables that include the ORG_ID as the composite or driving key.
• Rewrite SQL joins with two or more views to use one secured synonym based on the driving table for the query and the remaining views replaced by _ALL tables.
• Add form block ORG_ID reference in the where clause to synchronize the data with the selected or derived operating unit.

Handle Operating Unit value change

The user can change the operating unit anytime before committing the record to the database. The Operating Unit field is disabled after committing the record to the database and prevents users from updating the record.

Post-Insert Trigger of Operating Unit field Block

app_item_property.set_property(‘BLOCK_NAME.OPERATING_UNIT‘, ENABLED, 
PROPERTY_OFF);

The Operating Unit field must not be enabled for the queried records.

Post-Query Trigger of Operating Unit field Block

app_item_property.set_property(‘BLOCK_NAME.OPERATING_UNIT", ENABLED, 
PROPERTY_OFF);

When-New-Record-Instance Trigger of Operating Unit field Block

IF :system.record_status = ‘QUERY‘ THEN
  app_item_property.set_property(‘BLOCK_NAME.OPERATING_UNIT‘, ENABLED, 
  PROPERTY_OFF);
    ELSE
  app_item_property.set_property(‘BLOCK_NAME.OPERATING_UNIT‘, ENABLED, 
  PROPERTY_ON);
END IF;

If the user changes the operating unit when entering transactions, before committing the record to the database, then the fields are either cleared or a message appears requesting the user to clear the record or form depending on the number of operating unit specific fields in the form.

The product team can set the same property in the ‘ON-COMMIT‘ trigger. If the database fails to commit, then the records must be rolled back and the system administrator must fix the issue.

Drill down Access

When drilling down one form to another, in a product or between products that use different or same security, access is limited to the transaction that the user drills down and the query returns the drilled down transaction ID. The user can query only the transaction in the same or different operating unit.

You must set the current organization when drilling down using the following API, so that synonyms return data:

mo_global.set_policy_context(‘S‘, :parameter.org_id)

For example, in the Invoice Overview form when drilling to the Suppliers form, the Suppliers form must display only the supplier in the invoice and the site. If the supplier has sites in different operating units, they must not be displayed and the drill down must confine to supplier and supplier site ID.

If APIs are used in the drilled down form, you must invoke mo_global.init routine to initialize the global temporary table, as some APIs depend on the data in the temporary table.

Synchronization of Master-Detail Blocks

You must refer the detail block base table to use the unsecured _ALL table instead of the secured synonym for a master-detail relationship form that contains two blocks in the same window and the operating unit context in the master block. Otherwise, synchronization errors appear when navigating from one record to another in the master block when the records are present in different operating units. Oracle does not recommend that you set the policy context for the current organization in the pre-query trigger of the detail block.

Delete Records Behavior

When you enter information for different operating units, delete one record and then save, the cursor moves to the next record, which could apply to a different operating unit. However, the record is not deleted unless you code the ON-DELETE trigger to delete the record using the unsecured ALL table. Oracle does not recommend that you set the policy context in pre and post delete triggers to workaround this problem, for performance issues.

Remove NVL function for ORG_ID and CLIENT_INFO reference

You must NOT refer to CLIENT_INFO logic in the forms code or in the PL/SQL packages. You must also remove the NVL function for ORG_ID, since multiple organizations is mandatory for Release 12.

Enhancements to Reports

Overview

You must remove references of CLIENT_INFO and NVL function to the ‘ORG_ID‘ column in the reports.

Single Organization Reports

The operating unit mode for single organization reports are flagged as ‘SINGLE‘ in the Define Concurrent Programs page. The parameter – Operating Unit is available for single request and request sets. You cannot enter any value in this field if the Operating Unit mode is Multiple or none. When submitting the report, the concurrent program captures the current organization specified in Operating Unit parameter.

Cross Organization Reports

The Operating Unit mode for cross organization reports are flagged as ‘MULTIPLE‘ in the Define Concurrent Programs page. At runtime, multiple organizations initialization populates the temporary table with one or multiple operating units depending on the access control status of the product that owns the cross organization report.

Enhancements to Concurrent Programs

Overview

This section describes the changes for single and multiple organization concurrent programs.

Single Organization Concurrent Programs

The Operating unit mode for single organization concurrent programs is flagged as ‘SINGLE‘ in the Define Concurrent Programs form. The special parameter – Operating Unit is available for a single request as well as request sets. The operating unit is a required field and the default value is derived from the MO_UTILS.get_default_org_id() API. The enhancement ER 2420755 populates the multiple organizations temporary table MO_GLOB_ORG_ACCESS_TMP when the user selects the single organization concurrent program.

You must explicitly call multiple organizations initialization as multiple organizations is not initialized when opening forms or web pages. The multiple organizations initialization routine populates the temporary table depending on the application that owns the concurrent program. When a user selects an operating unit, Oracle Applications invokes the Multiple Organizations API MO_GLOBAL.set_policy_context() to set the VPD policy context and ensures that a simple equality predicate is used for the policy function and the organization specific value sets (based on secured synonyms) returns data without any changes to the code.

When you submit the concurrent program, Oracle Applications captures the organization that is specified in the Operating Unit parameter and populates the FND_REQUESTS table. When executing the concurrent request, Oracle Applications calls the MO_GLOBAL.set_policy_context() API to initialize the temporary table.

Enhancements to Public APIs

This section describes the changes to Public APIs. Public APIs must consider operating unit as an input parameter to the API.

Generally, the Multiple Organizations API and MO_GLOBAL.init(‘<ACCESS_MODE‘) – ACCESS_MODE is S or M – must be executed before executing the public API when executing from SQL*Plus or any other tools and when multiple organizations is not initialized,. Multiple organizations is initialized when APIs are invoked through the user interface.

A new API, MO_GLOBAL.validate_orgid_pub_api, is available to meet the following requirements:

1. Standardize validation logic.
2. Backward compatibility for public APIs to use the MO: Operating Unit profile option if customers choose not to enable the access control feature.

Product teams call this API in the public APIs (for public APIs only) to handle the validations by the central routine, which reduces the effort to modify APIs for the development team. The APIs validate the value of the org_id against the value in the MO: Operating Unit profile option without executing the multiple organizations initialization (mo_global.init). This API allows customers to continue using the public APIs without calling MO_GLOBAL.INIT in Release 12, if they choose not to enable the access control feature.

Public APIs should conform to the following rules:

• Do not use the multiple organizations temporary table directly in the SQL query.
• Rewrite the SQL joins with two or more views to use just one secured synonym depending on the driving table for the query and replace the remaining views by _ALL tables.
• Add the ORG_ID to the WHERE clause of the SQL to avoid cartesian joins for tables that include ORG_ID the composite or driving key.
• Provide usage of group APIs to the owner of public APIs to product teams IF the uptake team is not secure with multiple organizations or the user cannot access to the ORG_ID context that is passed into the API. Group APIs must not refer to multiple organizations synonyms or functions depending on multiple organizations temporary table.
• Accept value from the MO: Operating Unit profile option if MO: Security Profile is not set and the MO_GLOBAL.init is not executed for backward compatibility.

Add ORG_ID Parameter

Public APIs must allow ORG_ID as a parameter for callers to manipulate organization-striped entities. If a user having access to multiple operating units executes a Public API, then the API either takes the default operating unit specified in the MO: Default Operating Unit profile option or explicitly passes the operating unit value.

There are multiple ways to pass the ORG_ID as a parameter:

Expose the ORG_ID on the record type for an API that takes in a record type parameter. This propagates the ORG_ID to low level APIs that work on the record.

Add a new parameter to the API to take multiple record types that map to multiple parent-child entities, which are created in the same operating unit. The record type needs additional validation to expose the operating unit by consistently passing the ORG_ID to various record types.

Example:

The Trading Community Architecture package HZ_CUST_ACCOUNT_SITE_V2PUB provides APIs to create and update Customer Account Sites. These APIs work on the record type HZ_CUST_ACCOUNT_SITE_V2PUB.cust_acct_site_rec_type.

You must modify the API to add an ORG_ID attribute to the record type as follows:

HZ_CUST_ACCOUNT_SITE_V2PUB.cust_acct_site_rec_type(
...
ORG_ID			NUMBER);

Get Current ORG_ID

Several public APIs are used in user interfaces and SQL*Plus or other third party tools. An example is the Receivables APIs. In the Receipt Workbench, the current organization is set in the form triggers and the ORG_ID is not passed as an input. The ORG_ID is a mandatory input for public APIs and if not passed, it defaults from the MO: Default Operating Unit, if valid, with the MO: Security Profile.

Default ORG_ID

Similar to the user interfaces that are enabled with the multiple organizations access control feature, the public APIs must default the operating unit specified in the MO: Default Operating Unit profile option. When passing the operating unit value to a public API, the value must override the default value. If the operating unit value is checked for its current organization and the operating unit is defaulted when the operating unit parameter is blank and current organization is not set then common API MO_UTILS.get_default_org_id defaults the operating unit in such situations.

If you do not pass the ORG_ID value, do not set either the MO: Security Profile or MO: Default Operating Unit profile options, and set only the MO: Operating Unit profile option, then the value in the MO: Operating Unit becomes the default value for public APIs.

The Multiple Organizations API checks the MO: Security Profile or MO: Default Operating Unit and the MO: Operating Unit if access control is on. The utility returns a null value if you do not specify a value in the MO: Default Operating Unit profile option, which is not mandatory.

The MO_GLOBAL.get_valid_org, org_id parameter returns a default value.

Validate ORG_ID input

You must supply an operating unit to a public API by passing the operating unit as a parameter, default the operating unit from the current organization or from the MO: Default Operating Unit profile option. The RDBMS defaulting for ORG_ID column for the multiple organizations tables is removed. To prevent inserting records with no ORG_ID, the operating unit value is mandatory in public APIs otherwise, you end up inserting data but cannot query the data, as the multiple organizations security policy will not allow you to query any global data (NULL ORG_ID value).

In the user interface pages, the operating unit list of values displays valid values to the user. The public APIs must validate the operating unit to confirm that the caller can access the operating unit that the user passes as a parameter.

You must validate the valid ORG_ID before validating any product specific validations in a public API logic flow.

The Multiple Organizations API MO_GLOBAL.get_valid_org determines the valid ORG_ID in the following hierarchy. Public APIs call the common utility MO_Global.get_valid_org() to ensure that the ORG_ID is NOT NULL and that the user can access the organization.

1. Uses the ORG_ID if passed
2. Searches for the current organization value if the ORG_ID is not passed
3. Searches for the default operating unit if ORG_ID is not passed and current organization is not set. The MO: Default Operating Unit value must be a valid value in MO: Security Profile.
4. Calls the check_valid_org() API to validate ORG_ID is valid. This API returns a NULL value for the ORG_ID and displays a message if the ORG_ID is NULL or cannot default from the current organization or the default operating unit profile option and if the ORG_ID does not exist in the Multiple Organizations temporary table, which initializes based on the MO: Security Profile or MO: Operating Unit profile option, the API returns a NULL value for the ORG_ID and displays a message that the value is INVALID. If the MO: Security Profile is not defined and current_org_id context is not set, the value in MO: Operating Unit is used as the default value for backward compatibility.

Since ORG_ID is a key attribute, a public API must raise an exception and do no processing if the validation fails.

Product teams may also invoke the MO_GLOBAL.validate_orgid_pub_api in addition to the coding validation logic as shown above in a public API. The following is the API specification:

1. ORG_ID IN OUT NUMBER
2. ERROR_MESG_SUPPR IN VARCHAR2(1) DEFAULT N
3. Status OUT VARCHAR2(1)

The Org_ID parameter accepts a value passed into public APIs. Leave it null if no value is passed, and we try to derive a default value, if possible. A default org_id value is returned if the system derives a value. If the validation fails, the validate_orgid_pub_api raises an application error. However, the product team may choose to pass Y to ERROR_MESG_SUPPR to prevent the validate_org_id_pub_api from raising an application error and allow custom error handling. By default, this option is N. The status value is S (Success) or F (Fail).

Remember that the API has build-in logic to validate the org_id value if the MO_GLOBAL.init procedure is not executed for a database session. Therefore, it must be used only in public APIs. Using this API at other situations may result in compromising data access security.

Create Operations

Create Operations must validate the ORG_ID by calling the common validation utility.

Example:

TCA API HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Acct_Site indirectly calls the validation procedure HZ_ACCOUNT_VALIDATE_V2PUB.validate_cust_acct_site()???.This procedure validates the ORG_ID as shown:

l_org_id:=
MO_GLOBAL.get_valid_org(p_cust_acct_site_rec.org_id);
IF l_org_id is NULL THEN
  x_return_status := FND_API.G_RET_STS_ERROR;
  RAISE FND_API.G_EXC_ERROR;
END IF;

Update Operations

Update Operations must validate the ORG_ID using the same rules as create operations. This means that the update APIs must include the ORG_ID as the parameter where ever needed. The following rules apply for update operations:

1. Use the ORG_ID if explicitly passed.
2. Use the current org value if ORG_ID is not passed.
3. Use the default operating unit if the ORG_ID is not passed and current organizations is not set.
4. Call the check_valid_org() API to validate the ORG_ID.

Call the Multiple Organizations API MO_GLOBAL.get_valid_org() to execute steps 1 to 4. Use this routine to validate the ORG_ID before any further process.

Example:

The TCA package HZ_CUST_ACCOUNT_SITE_V2PUB contains APIs to update the customer account sites. These APIs work on the record type HZ_CUST_ACCOUNT_SITE_V2PUB.cust_acct_site_rec_type. Modify this API to add an ORG_ID attribute to the record type as shown in following code (similar to the create operations)

HZ_CUST_ACCOUNT_SITE_V2PUB.cust_acct_site_rec_type(
  ...
ORG_ID					NUMBER);

Before the TCA API, HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_acct_site calls the validation procedure, the ORG_ID must be validated by calling the MO_GLOBAL.get_valid_org() API. If the ORG_ID is valid, then the ORG_ID restricts the data as additional filter in the query (based on _ALL tables). The processing stops if the ORG_ID is invalid. These queries are not based on secured synonyms, though the current organization is set to minimize performance overhead.

The following example describes setting the current organization – as discussed in the section Set Policy Context.

BEGIN
  l_org_id:=
  MO_GLOBAL.get_valid_org(p_cust_acct_site_rec.org_id);
  IF l_org_id is NULL THEN
  x_return_status := FND_API.G_RET_STS_ERROR;
   RAISE FND_API.G_EXC_ERROR;
  END IF;

  MO_GLOBAL.set_policy_context(‘S’,l_org_id);

  SELECT ROWID, OBJECT_VERSION_NUMBER
  INTO l_rowid, l_object_version_number
  FROM HZ_CUST_ACCT_SITES_ALL
  WHERE CUST_ACCT_SITE_ID
		   =p_cust_acct_site_rec.cust_acct_site_id
	AND ORG_ID = l_org_id;
   FOR UPDATE NOWAIT;

  IF NOT (
    ( p_object_version_number IS NULL AND
      l_object_version_number IS NULL ) OR
    ( p_object_version_number IS NOT NULL AND
      l_object_version_number IS NOT NULL AND
      p_object_version_number =
      l_object_version_number ) )
  THEN
    FND_MESSAGE.SET_NAME( 
       ‘AR‘, ‘HZ_API_RECORD_CHANGED‘ );
    FND_MESSAGE.SET_TOKEN( 
       ‘TABLE‘, ‘hz_cust_acct_sites‘ );
    FND_MSG_PUB.ADD;
    RAISE FND_API.G_EXC_ERROR;
  END IF;

  p_object_version_number
		:= NVL(l_object_version_number, 1 ) + 1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
	 FND_MESSAGE.SET_NAME( ‘AR‘, ‘HZ_API_NO_RECORD‘ );
	 FND_MESSAGE.SET_TOKEN( ‘RECORD‘, ‘customer account site‘ );
	 FND_MESSAGE.SET_TOKEN( ‘VALUE‘,
    NVL(TO_CHAR(p_cust_acct_site_rec.cust_acct_site_id ), ‘null‘ ) );
    FND_MSG_PUB.ADD;
    RAISE FND_API.G_EXC_ERROR;
END;

Set Policy Context

Public APIs must accept the ORG_ID value and must be coded using _ALL tables without depending on the current organization mechanism. However, some products, such as Receivables, share the code and are called directly from third party tools. These products must support the current organization in public APIs, which necessitates the product APIs to set the current organization, though the product code depends on the _ALL tables. If the ORG_ID value is mandatory, then the products that need the current organization must set the ORG_ID and not enforce the products to set it.

However, this is not the right solution as user interface and public APIs must support the default operating unit. Therefore, the public APIs first validates the ORG_ID and then sets the current organization policy for single mode operations as shown in the following code:

BEGIN
  l_org_id:=
  MO_GLOBAL.get_valid_org(p_cust_acct_site_rec.org_id);
  IF l_org_id is NULL THEN
   x_return_status := FND_API.G_RET_STS_ERROR;
    RAISE FND_API.G_EXC_ERROR;
  END IF;

  MO_GLOBAL.set_policy_context(‘S‘,‘l_org_id);
...
END;

Add ORG_ID Filters to Foreign Key Validations top org-striped entities

When creating or updating an entity, a Public API must validate the foreign keys (to org-striped entities) that point to entities in the same organization as the original entity.

The foreign key validates the org-striped views. With multiple organizations access control, when querying the organization, the queries may go against the _ALL entities to reduce the overhead of the security policy though the current organization is set in public APIs and this necessitates adding the ORG_ID filters to avoid cartesian joins for tables that include the ORG_ID as the composite unique index.

Example:

TCA provides public APIs to create and maintain Customer Site Uses. Users can specify an Order Type on a Customer Site Use and this Order Type must exist in the same operating unit as the Customer Site Use. Therefore, the Order Type query validates and filters the ORG_ID that the Site Use belongs to or is created in. As the ORG_ID is validated using the common validation utility, the query works on the _ALL table (with the additional ORG_ID filter) and not on the synonym.

Modify the low-level validation procedure that validates the Order Type to include an additional ORG_ID parameter.

PROCEDURE check_ord_type (
	 p_column			 IN VARCHAR2,
	 p_column_value		 IN VARCHAR2,
	 p_org_id			 IN NUMBER,
	 x_return_status	 IN OUT VARCHAR2) IS
BEGIN
  SELECT ‘Y’
	 INTO l_dummy
	 FROM OE_TRANSACTION_TYPES_ALL ot
	WHERE ot.order_type_id = p_column_value
	  AND ot.transaction_type_code = ‘ORDER‘
	  -- only select order types
	  AND ot.ORG_ID = p_org_id
	  AND sysdate BETWEEN
				nvl(ot.start_date_active, sysdate)
	  AND nvl(ot.end_date_active, sysdate);
END;

You must pass the ORG_ID to the modified validation procedure:

check_ord_type (
	p_column=>’order_type_id’,
	p_column_value=http://www.mamicode.com/> p_cust_site_use_rec.order_type_id,>p_org_id=> p_cust_site_use_rec.org_id,
	x_return_status=> x_return_status );

Add ORG_ID Filters to Enforce Uniqueness

Certain attributes (or combination of attributes) on a record must be unique in an operating unit. The entity level validations that enforce this constraint work against the org-striped views. With multiple organizations access control, the validation queries go against the _ALL tables (with the additional ORG_ID filter) instead of the synonym, though the current organization is set in Public APIs. The ORG_ID is validated upstream and the ORG_ID filters are added to avoid cartesian joins for tables that include ORG_ID as the composite or driving key.

Example:

TCA APIs checks if an account has one account site pointing to a given party site in an operating unit when creating Customer Account Sites. The query enforcing this constraint filters the ORG_ID and works in the _ALL table.

BEGIN
  SELECT ‘Y‘
	 INTO l_dummy
	 FROM HZ_CUST_ACCT_SITES_ALL
	WHERE CUST_ACCOUNT_ID = p_cust_acct_site_rec.cust_account_id
	 AND PARTY_SITE_ID = p_cust_acct_site_rec.party_site_id
	 AND ORG_ID = p_cust_acct_site_rec.org_id
	 AND ROWNUM = 1;
  FND_MESSAGE.SET_NAME(‘AR‘, ‘HZ_API_DUPLICATE_COLUMN‘ );
  FND_MESSAGE.SET_TOKEN(‘COLUMN‘, ‘cust_account_id - party_site_id‘ );
  FND_MSG_PUB.ADD;
  x_return_status := FND_API.G_RET_STS_ERROR;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
	 NULL;
END;

Modify Table Handlers

With multiple organizations access control, the default values of the ORG_ID column (using CLIENT_INFO org context) in the Multiple Organizations tables are removed. Therefore, you must explicitly pass the ORG_ID value to the table handlers otherwise you end up inserting data but cannot query again, as the Multiple Organizations security policy will not allow you to query any global data (NULL ORG_ID value).

As the ORG_ID is validated upstream and explicitly specified, you must insert the insert handlers in the _ALL entity. The insert handler takes an additional parameter of the ORG_ID with the value specified as NO DEFAULT.

Example:

Add the following parameter to the procedure Insert_Row() procedure:

X_ORG_ID 	IN				NUMBER

The insert handler inserts the ORG_ID into the _ALL table and explicitly passes the ORG_ID value as shown in the following code:

INSERT INTO HZ_CUST_ACCT_SITES_ALL (
. ...
APPLICATION_ID,
ORG_ID
) 
VALUES
... ... ...
DECODE( X_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID ),
X_ORG_ID ) RETURNING
			 CUST_ACCT_SITE_ID ....

Update handlers can go against _ALL tables, as a public API must validate the existing record that is updated for a secured synonym (with no organization filter).

Select and Delete table handlers works on the secured synonyms if they do not validate upstream. While using secured synonyms, yo must rewrite the SQL to use one secured synonym and the _ALL tables reference for the rest. You must add the ORG_ID filter to the WHERE clause of the SQL query to avoid cartesian joins for tables that include the ORG_ID as a composite or driving key.

Prevent ORG_ID from Updating

You cannot update the operating unit (ORG_ID) field in user interfaces after committing the record as public APIs prevent the caller from updating the ORG_ID value for existing records.

The public APIs validates whether the ORG_ID is valid and the caller can access the updated record. This query works on the _ALL table and uses the ORG_ID filter.

Example:

When the TCA API HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_acct_site calls the validation procedure, it calls MO_GLOBAL.get_valid_org() API to validate the ORG_ID. If ORG_ID is invalid, then processing should stop. If the ORG_ID is valid, then it must use an additional filter in the query (based on _ALL tables) to restrict the data. Such queries must not be based on secured synonyms though the current organization is set due to the security policy overhead.

BEGIN
  l_org_id:=
  MO_GLOBAL.get_valid_org(p_cust_acct_site_rec.org_id);
  IF l_org_id is NULL THEN
  x_return_status := FND_API.G_RET_STS_ERROR;
  RAISE FND_API.G_EXC_ERROR;
  END IF;
  MO_GLOBAL.set_policy_context(‘S’,’l_org_id);
  SELECT ROWID, OBJECT_VERSION_NUMBER
  INTO l_rowid, l_object_version_number
  FROM HZ_CUST_ACCT_SITES_ALL
  WHERE CUST_ACCT_SITE_ID=p_cust_acct_site_rec.cust_acct_site_id
  AND ORG_ID = l_org_id;
  FOR UPDATE NOWAIT;
  IF NOT (
  ( p_object_version_number IS NULL AND
  l_object_version_number IS NULL ) OR
  ( p_object_version_number IS NOT NULL AND
  l_object_version_number IS NOT NULL AND
  p_object_version_number = l_object_version_number ) )
  THEN
  FND_MESSAGE.SET_NAME( ‘AR‘, ‘HZ_API_RECORD_CHANGED‘ );
  FND_MESSAGE.SET_TOKEN( ‘TABLE‘, ‘hz_cust_acct_relate‘ );
  FND_MSG_PUB.ADD;
  RAISE FND_API.G_EXC_ERROR;
  END IF;
  p_object_version_number := NVL( l_object_version_number, 1 ) + 1;  
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
  FND_MESSAGE.SET_NAME( ‘AR‘, ‘HZ_API_NO_RECORD‘ );
  FND_MESSAGE.SET_TOKEN( ‘RECORD‘, ‘customer account relate‘ );
  FND_MESSAGE.SET_TOKEN( ‘VALUE‘,
  NVL( TO_CHAR( p_cust_acct_relate_rec.cust_account_id ), ‘null‘ ) || ‘,‘ || NVL( 
  TO_CHAR( p_cust_acct_relate_rec.related_cust_account_id ), ‘null‘ ) );
  FND_MSG_PUB.ADD;
  RAISE FND_API.G_EXC_ERROR;
  END;

Use Secured Synonyms

The API must go against secured synonyms when populating a product specific cache for system_parameters (that are org-striped) if there is NO additional ORG_ID filter.

Enhancements to Workflows

This section describes the changes to workflows. With multiple organizations access control, workflow processes must set the current organization ID instead of the CLIENT_INFO organization context. The workflow activities are processed for each record and the ORG_ID of the item key sets the Multiple Organizations application contexts – the access mode and current organization ID – which are used in the simple equality predicate of the Multiple Organizations security policy.

The access must be validated at the begriming when processing the workflow using the multiple organizations profile options: MO: Security Profile, MO: Default Operating Unit, and not MO: Operating Unit. This behavior is limited to workflows only, as administrators and approvers must be able to process a workflow, although they have access privileges different from the initiator.

Before Release 12, the workflow processes initialize the organization context (CLIENT_INFO) in the following methods:

• Set context manually
• Set context using callback functions

Setting context manually is not very efficient as it sets the context in every activity. Callback functions are executed once per session per item key and are more efficient. The callback functions checks that the organization context the user sets and the organization that the workflow must process are the same. If same, then the workflow proceeds with the process otherwise, the workflow process resets the context that runs in the background engine. However, the workflow process does not reset the context for foreground jobs, but sets the process to a deferred mode so that the background engine processes the job later.

Set Context using Callback Functions

Oracle Workflow supports the callback function to reset or test item type context information. Oracle Workflow calls the callback function using the following command.

• SET_CTX – Establish context information for an item type and item key combination which the function activity in the item type must execute. The workflow engine calls the callback function with this command each time it encounters a new item type and item key combination, to ensure the correct context information is always set.
• TEST_CTX – Validate the current database session context before the workflow engine executes an activity.

For example, when the user creates a new expense report in ORG_ID 204, the workflow engine executes the Expenses workflow callback function in the TEST_CTX mode. If the context information is correct, then the callback function returns TRUE, and the workflow engine does not reset the context. If the context information is incorrect, for example, if the expense report is approved in ORG_ID 458, the callback function calls the SET_CTX mode and resets the context information to CLIENT_INFO organization context. However, if the workflow process is executed synchronously, the callback function does not call the SET_CTX mode when the TEST_CTX returns FALSE and the process is set to deferred mode and executed asynchronously by the background engine.

With multiple organizations access control, you must set the current organization ID and not the CLIENT_INFO org context. You must derive the current organization ID from item keys. Do not rely on MO: Security Profile, MO: Default Operating Unit, and MO: Operating Unit profile options when setting the organization context because the operating unit must be validated before initiating the workflow.

SET_CTX Mode

In SET_CTX mode, call the Multiple Organizations API MO_GLOBAL.set_policy_context() to set the organization context to single and the current organization ID in function activities.

Pseudo Code – SET_CTX Mode

BEGIN
.. Retrieve Org Id of Item Key;
   IF (p_command = ‘SET_CTX‘ THEN
     mo_global.set_policy_context(
                   g_access_mode =>  ‘S‘,
                   p_org_id 	  =>  Org Id;
   END IF;
END;

TEST_CTX Mode

In TEST_CTX mode, check the values of access mode and the current organization ID. If the values are not set, you return FALSE to set the application contexts by calling the SET_CTX function, otherwise return TRUE to use the existing context values.

Pseudo Code – TEST_CTX Mode

BEGIN
  Ret rive Org Id of Item Key;

  IF (p_command = ‘TEST_CTX‘ THEN

    IF (NVL(mo_global.get_access_mode,‘NULL‘) <> ‘S‘) OR
       (NVL(mo_global.get_current_org_id,-99) <> Org Id)
    THEN
      p_result := ‘FALSE‘;
    ELSE
      p_result := ‘TRUE‘;
    END IF;
END;

Access Multiple Operating Units

Some workflow requires accessing multiple operating units to process a single transaction. When you set the current organization ID, the organization context is reset if the context is different from the previous transaction.

Pseudo Code – Get Order Lines

BEGIN
  lOperUnit := mo_global.get_current_org_id;

  for l_next_rec IN c_work_order_eligible LOOP
    if (lOperUnit <> l_next_rec.org_id ) then
      fnd_global.apps_initialize; (if necessary)

      mo_global.set_policy_context (
                p_access_mode => ‘S‘,
                p_org_id => l_next_rec.org_id);
    end if;

    lOperUnit := l_next_rec.org_id;
    ...
  end loop;

END;

Remove NVL Logic for ORG_ID and CLIENT_INFO reference

Workflows must not refer to the CLIENT_INFO for org_id context in the code. The NVL function for the ORG_ID must be removed for better performance.

Enhancements to OA Framework Pages

The multiple organizations setup and transaction pages in OA Framework must expose the Operating Unit field. This allows users to select the operating unit and enter the setup or transaction for the operating unit and provide simple operating unit derivations from some attributes of the transaction, wherever possible.

Product teams must change the setup and transaction pages for multiple organizations access control as described in the following sections.

Multiple Organizations Initialization

Every transaction that requires multiple organizations must call the Multiple Organizations initialization in the root Application Module (AM).

Use the following declarative mechanism to initialize the multiple organizations settings for application teams to implement multiple organizations:

1. To enable multiple organizations for the root application module , go to the BC4J Application Module wizard - Properties section and specify the property as MULTIORG_ENABLED and value as either S (single operating unit mode) or M (Multiple operating unit mode).
2. Click Add, then Apply or OK.

On specifying this property, the OA Framework automatically initializes the multiple organizations context at the following appropriate program event points:

1. When reserving or activating the application module.
2. When initializing or validating the Oracle Applications user session.

You initialize the context once for each transaction and session and not instantiate for every page. If your transaction retains the root AM, then the above steps are the easiest to initialize multiple organizations.

If a transaction has multiple pages and the root AM is not retained, then you must call the method OADBTransaction.setMultiOrgAccess to initialize the multiple organizations context to help the user select an operating unit for a transaction. If the operating unit the user selected must appear in the subsequent pages, then pass the curr_org_id to the page and use OADBTransaction.setMultiOrgPolicyContext method to set the operating unit context for the pages that need multiple organizations.

Add Operating Unit Field

Per the BLAF guidelines, the Operating Unit field is the first field in the page as the operating unit is the controlling field.

You must add the operating unit and the organization ID (ORG_ID) fields to your page not expose the Operating Unit field for regions that are not needed and not display the organization ID field in any region. Please refer to the functional examples in Section 5.3.1 Recommendations for Operating Unit Field in OA Pages about regions that display the Operating Unit field.

Use the attribute sets, provided for Operating Unit and Organization ID fields, to define these fields as shown:

Operating Unit Field Details

Org Id Field Details

Create List of Values for Operating Unit Field

You must attach a list of values (LOV) to the Operating Unit field by using the external LOV in /oracle/apps/fnd/multiorg/lov/webui/OperatingUnitsLovRN. The LOV mappings must be created as shown:

Mapping for OperatingUnit Field

Mapping for OrgID Field

Default Operating Unit on Opening the Page

You must default the operating unit (if set and valid) on the opening page by calling the Multi-Org java wrapper as shown. This method resides in the server side package as a method of the root Application Module. This method is executed before an operating unit is initialized.

rootAMImpl

public class ExpensesAMImpl extends OAApplicationModuleImpl
{
  public void getDefaultOu() throws java.sql.SQLException
  {
   OADBTransactionImpl trx = 
  	    (OADBTransactionImpl)getOADBTransaction();
  
   Number[] defOrgId = {new Number()};
   String[] defOuName = {new String()};
   Number[] ouCount = {new Number()};
   
 try
  {
  	 MoUtils.getDefaultOu(trx,defOrgId,defOuName,ouCount);
  }
  catch (SQLException sqlE){
			throw OAException.wrapperException(sqlE);
  }
  }
  // Populate OU transaction value with default
  // Render OU dependent fields
  ...
}

Handle Select/Derive Operating Unit feature

Select Operating Unit: For page flows that support the select operating unit feature the dependent fields must appear after selecting the operating unit. Partial Page Rending (PPR) controls the behavior of the dependent fields. If the default operating unit is available, the Operating Unit field is populated and the dependent fields appear after opening the page.

To enable PPR, you must create a special "application properties" view object and add it to your page‘s root Application Module. You must enable PPR on Operating Unit dependent fields, by specifying the property of the dependent fields.

The following is an example of an initialization code that controls the appearance of the Operating Unit dependent fields in the Expense Entry page flow. The expense template field appears depending on the default operating unit.

The method init is invoked from the ProcessRequest method within the Controller Object (CO).

public void init()
  {
    OAViewObject appPropsVO =
    (OAViewObject)findViewObject("GeneralInfoPVO1");
    if (appPropsVO != null)
    {
    // If the VO already has a row, skip its initialization.
    if (appPropsVO.getFetchedRowCount() == 0)
    {
      appPropsVO.setMaxFetchSize(0);
      // You must create and insert a row in the VO before you can
      // start setting properties.
      appPropsVO.insertRow(appPropsVO.createRow());
      // Set the primary key value for this single-rwo VO.
      OARow row = (OARow)appPropsVO.first();
      row.setAttribute("RowKey", new Number(1));
    }
handleOuChangeEvent((String)getOADBTransaction().getValue("ouName"));
    }
  }
  
public void handleOuChangeEvent(String ou)
  {
     // Get the special, single-row application properties and make
     // first row current
     OAViewObject vo = 
(OAViewObject)findViewObject("GeneralInfoPVO1");
     OARow row = (OARow)vo.first();
     // Set the application property values based on the PO Approval
     // status value.
     if (ou==null)
     {
row.setAttribute("MO_OU_SELECTED_RENDERED_EXPENSE_TEMPLATE_TEXT", 
Boolean.FALSE);
     }
     else
     {
    row.setAttribute("MO_OU_SELECTED_RENDERED_EXPENSE_TEMPLATE_TEXT", 
Boolean.TRUE);
     }
  }

Derive Operating Unit: For pages that support derive operating unit feature, the Operating Unit specific fields appear with the operating unit depending on the attributes of the transaction. You must code these pages using PPR, so that when you select the operating unit the Operating Unit specific fields appear and vice versa.

Setting the Policy Context

To save the existing page that works in the context of one operating unit, you remove or set the current organization and access mode policy contexts using the java wrappers available in OADBTransactionImpl class shown below. The multiple organizations security policy function uses a dynamic predicate to handle simple predicate when the access is limited to one operating unit and a complex predicate (exists sub-query) when the access involves multiple operating units. The predicate depends on the application context attribute value for access_mode.

For example, in the Process Form Request method in the Controller Object (CO), call the multiple organizations methods depending on the operating unit selected from the list of values.

PageCO

String event = pageContext.getParameter("event");
  if (pageContext.isLovEvent())
  { 
    // Form was submitted because the user selected
    // a value from the LOV modal window,
    // or because the user tabbed out of the LOV input.
  
    // Find out which LOV input triggered the event.
    String lovInputSourceId = pageContext.getParameter(SOURCE_PARAM);
    if ("OperatingUnit".equals(lovInputSourceId))
    {
      // Find out the result values of the LOV.
      Hashtable lovResults = pageContext.getLovResultsFromSession(lovInputSourceId);
      if (lovResults != null)
      {
        // Update the page depending on the value chosen by
        // by user to set context
        String orgIdS = (String)lovResults.get("OrgId");
        Integer orgId = new Integer(orgIdS);
        OADBTransaction trx = generalInfoAM.getOADBTransaction();
        trx.putValue("org_id",orgId);
        trx.putValue("OuName", (String)lovResults.get("OperatingUnit"));
        trx.setMultiOrgPolicyContext("S", orgId.longValue());
        Serializable[] params = {orgIdS};
		 rootAM.invokeMethod("ouSelected",params);

generalInfoAM.invokeMethod("handleOUChangeEvent",params);
      }
	   else
	   {//OU cleared
		 Serializable[] params = {null};

generalInfoAM.invokeMethod("handleOUChangeEvent",params);
      }
    }
  }

The above code can be invoked from the ProcessRequest method when a default operating unit is available.

Initialize Operating Unit Specific Attributes

In Release 11i, operating unit specific attributes are initialized when opening a page as a responsibility has access to one operating unit. With multiple organizations access control, a responsibility can access multiple Operating Units, so initialization must happen after the user selects an Operating Unit or a default value appears.

For example, in the Expense entry page flow the multiple organizations specific attributes are initialized in the ProcessFormRequest method after the user selects the operating unit or in the ProcessRequest method if there is a default operating unit. You can create a new method and consolidate all multiple organizations initialization in that method as shown below:

public void ouSelected(String ou) throws java.sql.SQLException
 
  {
  
  initGeneralInfoInTrxn(getEmployeeIdInTransaction());
  
  initExpenseTemplatesVO();
  
  initExpenseTypesVO();
  
  setup.setFunctionalCurrencyInfo(this);
  
  }

Cache Operating Unit Information

With multiple organizations access control, the cached operating unit information includes multiple operating units. OA Framework handles caching using the BC4J layer. To utilize OA caching, you must create an Entity Object (EO) and View Object (VO) for the operating unit specific attributes to cache, and make the ORG_ID the key attribute. To retrieve the operating unit attributes, you must use findByKey() method on the VO after selecting the operating unit using ORG_ID as the key. Here is an example:

OAViewObject ouCacheVO =
  (OAViewObject)findViewObject("ouCacheVO1");
    Object [] keyValues = new Object[2];
    keyValues[0] = new Number(ou);
    keyValues[1] = null;
    Row[] rows = ouCacheVO.findByKey(new
    Key(keyValues), 1);
    ouCacheVORowImpl row = (ouCacheVORowImpl)rows[0];
    ...

In this example, there are two keys because the VO joins 2 views, AP_SYSTEM_PARAMETERS and GL_LEDGERS_PUBLIC_V. The key value for AP_SYSTEM_PARAMETERS is ORG_ID, and the key value for GL_LEDGERS is LEDGER_ID.

The first time findByKey() is invoked for a record, it is retrieved from the database and is cached. Any subsequent call for the record is retrieved from the cache.

Modify EOs/VOs for Operating Unit specific fields

You must modify the EOs and VOs for LOVs used for Operating Unit specific fields according to the following the rules:

• Do not use the multiple organizations temporary table directly in the SQL query. Instead, use the PL/SQL functions to check the operating unit access and obtain the operating unit information.
• Add the ORG_ID filter to the WHERE clause of the VOs SQL to avoid cartesian joins for tables that include ORG_ID as the composite or driving key.

Add ORG_ID Predicate in Client/Server Code

In the client side and server side application codes, SQL statements issue Data Manipulation Language (DML) to multiple organizations views. The APIs that validate the data in an operating unit benefit by using the current organization ID set by the OA Framework pages before firing the validation logic, rather than passing the ORG_ID as a parameter. However, if the API is used in the reference view and server side validation for OA Framework pages, then modify the API to include the ORG_ID parameter as described in the reference views section.

You must follow these rules:

• Do not use the temporary table directly in the SQL query. Instead, Use the PL/SQL functions to check the operating unit access and obtain the operating unit information.
• Rewrite SQL joins with two or more views to use one secured synonym depending on the driving table for the query and replace the remaining views with _ALL tables.
• Add the ORG_ID filter to the WHERE clause of the VOs SQL to avoid cartesian joins for tables that include the ORG_ID as the composite or the driving key.

Modify Table Handlers

Similar to forms, you must explicitly pass the ORG_ID value to the table handlers and remove the RDBMS default value (CLIENT_INFO logic) for ORG_ID column for the multiple organizations tables.

Refer to Modify Table Handlers for examples on table handlers.

Handle Operating Unit Value Change

The user can change the operating unit anytime before committing the record to the database. The Operating Unit field appears read only after committing the record to the database, preventing users from updating the operating unit.

Product teams must decide whether clearing the fields should clear the entire record or only the values in the Operating Unit specific fields, as changing the operating unit value clears the Operating Unit specific fields. The change is captured in the ProcessFormRequest method using PPR.

Remove NVL logic and CLIENT_INFO reference

You must not refer to the CLIENT_INFO for organization context in the framework code and also remove NVL function for ORG_ for better performance.

Enhancements to JTT Pages

A JTT-based application is a stateless application. Multiple organizations must be explicitly initialized for JTT-based pages. The developer must specify if the page is enabled for single operating unit mode or multiple operating units by indicating S or M, respectively, when calling the mo_global.init routine.

When the operating unit context is determined for a given transaction flow, the current org_id context value must be captured by invoking theMultiOrgUtils.setMoPolicyContext() method. The developer must store the original state if the context is reset, if necessary, when changing the policy context (from multiple to single for a org_id value or vice versa),. This method sets application context at database and ICX session level.

The methods, MultiOrgUtils.getCurrentOrgId and MultiOrgUtils.clearCurrentOrgContext, help the developer in controlling the context value.

When establishing a new ICX session for the first time, the current organization ID in ICX session attributes must be null. Multiple organizations initialization depends on the multiple organizations mode, whether S (Single) for MO: Operating Unit profile option or M (Multiple) for MO: Security Profile, if one operating unit is defined.

When initializing a JTT-based page, the multiple organizations initialization sets a policy context with the value stored in ICX table if the ICX session is already established. The access mode declared in the page (initMultiOrg()) is ignored in this case. If no current organization ID value is stored in ICX table, then multiple organizations initialization honors the access mode declared in the page.

For example, a developer can call the mo_global.init with an appropriate access mode to a JTT-based page because the mo_global.init can be called from this page or from another page in a transaction flow. If the mo_global.init is called from another page (the calling page must invoke a new method for the set_policy_context that stores a single org_id value in the database and ICX session), then the page must contain a value from the ICX table.

All context value are reinitialized and the access mode set by initMultiOrg() is applied when switching responsibilities.

Specification for available API for JTT-based application

Below is a list of APIs for handling multi organizations context in JTT applications.

package oracle.apps.jtf.util;
  public class GeneralPreference {
  /*
  * initialize multi-org context. if only one org is accessible, set it in
  * database context and ICX session
  * reserve connection for the entire duration of the HTTP request.
  */
  public static void initMultiOrg(String pAccessMode)
  
  (Invoke this method at the beginning of a JSP page after ServletSessionManager.startRequest(). 
  This method calls MultiOrgUtils.initMultiOrg()to set org-context. It also reserves 
  the database connection for the duration of the HTTP request, so that subsequent 
  calls to TransactionScope.getConnection() within the request returns the same 
  connection with org-context initialized.)
  }
package oracle.apps.fnd.multiorg.server;
public class MultiOrgUtils
  {
  /*
   * initialize multi-org context. if only one org is accessible, set it in
   * database context and ICX session
   */
  public void initMultiOrg(WebAppsContext ctx,
 							OracleConnection dbConnection,
							String pAccessMode)
							throws SQLException
  ...

"(When a new session is established, the org context is null. MO initialization 
  is executed depending on the access mode - ‘S‘ for single (MO: Operating Unit 
  profile) or ‘M‘ for multiple (MO: Security Profile if defined). 
  The method should not be called if org context is not necessary for a given 
  transaction flow.)"


  /*
   * set org policy context in database connection as well as in ICX session
   */
  public void setMOPolicyContext(WebAppsContext ctx,
                                 OracleConnection dbConnection,
	    						  String pAccessMode,
								  long pOrgId)

"Invoke this method to store ‘current org_id‘ context when user chooses an org 
from org LOV. The method will invoke MO API set_policy_context. This method will 
set policy context at database and ICX session level."  


  /*
   * get current org context from ICX session
   */
  public long getCurrentOrgId(WebAppsContext ctx,
                   OracleConnection dbConnection)
  
  /*
   * clear org context from ICX session as well as database connection
   */
  public void clearCurrentOrgContext(WebAppsContext ctx,
                     OracleConnection dbConnection)


"(Invoke this method to clear ‘current org_id‘ context. Product team is responsible 
to call it within their program when required, JTT / MO will not clear it for you. (eg. 
clicking on Global button to Main menu does not clear curr_org_id automatically)."

When Responsibility is switched, org context value should be cleared)
}

Initializing Multi Organizations Context

For all pages in JTT based applications, call API ServletSessionManager.startRequest() at the beginning of a page. For most applications, the API is called from include page jtfsrnfp.jsp

In Release 12, multiple organizations enabled applications must call GeneralPreference.initMultiOrg() after ServletSessionManager.startRequest()in pages that require organizations context.

<%@ include file = "jtfincl.jsp" %>
...
<%@ include file = "jtfsrnfp.jsp" %>
<% GeneralPreference.initMultiOrg(accessMode) %>
...
...
<%@ include file = "jtfernlp.jsp" %>
...

Additional Information on Search Services:

1. Connections established by calling TransactionScope.getNewConnection() must call MultiOrgUtils.initMultiOrg(...).
2. To implement the JTT Search Service , call MultiOrgUtils.initMultiOrg(..) on connecting by calling TransactionScope.getNewConnection().

Setting Policy Context

GeneralPreference.initMultiOrg() initializes the context with multiple organizations if called in ‘M’ mode and the user accesses more than one organization. To set the “current org_id” in the context, application must call MultiOrgUtils.setMoPolicyContext(). This is typically required when a user chooses one organization from the multiple organizations list of values.

Get Current Organization Context

To obtain the organization context, applications must call MultiOrgUtils.getCurrentOrgContext().

Clearing Current Org Context

To clear the organization context, applications must call MultiOrgUtils.clearCurrentOrgContext(). JAVA wrappers are available in /fnddev/fnd/12.0/java/multiorg/server directory.

List of Public APIs

Integration Repository for Multiple Organizations Access Control:

Path in Integration Repository: Applications Technology > Application Object Library > Application Security Context

Multiple Organizations Access Control Global APIs

Name: Multi-Org Access Control Global APIs
Internal Name: MO_GLOBAL
Product: Application Object Library
Type: PL/SQL
Status: Active
Description: Multiple Organizations Access Control initialization related APIs
Source file: patch/115/sql/AFMOGBLS.pls

Procedures and Functions

Internal Name: JTT_INIT
Interface: MO_GLOBAL
Description: The java API for initializing multiple organizations in JTT-based application. This API also initializes the ICX session attribute, JTTCURRENTORG, and tracks the current organization (ORG) context.

Parameters

Internal Name: INIT
Interface: MO_GLOBAL
Description: Call this procedure to initialize the operating unit context. It is called when a new database session is established after calling FND_GLOBAL.apps_initialize routine.

Parameters

Internal Name: is_multi_org_enabled
Interface: MO_GLOBAL
Description: This function determines whether the current instance is a multiple organizations database. 
Return Value: Y if database instance is multiple organizations, else N.
Return Type: VARCHAR2

Internal Name: check_access
Interface: MO_GLOBAL
Description: This functions checks if the user can access the specific operating unit.

This function checks the following:

1. If the operating unit exists in the PL/SQL array. The PL/SQL array is populated by the set_org_access multiple organizations API.
2. If the operating unit is same as the current organization ID for single (‘S‘) organization initialization.

Parameters

Return Value: Y if the operating unit is accessible, else N.
Return Type: VARCHAR2

Internal Name: get_ou_name
Interface: MO_GLOBAL
Description: This function returns the operating unit name when provided with the ID.

Parameters

Return Value: Operating unit name.
Return Type: VARCHAR2

Internal Name: check_valid_org
Interface: MO_GLOBAL
Description: This function checks if the specified operating unit exists in the session‘s access control list. This function is similar to the check_access function. But this function also displays an error message if the specified operating unit is null or not present in the access list. The calling application can check the value of the function and display an error message if the operating unit is not present.

Parameters

Return Value: Y if the operating unit is present in the access control list, else N.
Return Type: VARCHAR2

Internal Name: set_policy_context
Interface: MO_GLOBAL
Description: This function sets the application context for the current organization and the access mode that must be set in server side code and multiple organizations security policy function for validations.

Parameters

Internal Name: get_current_org_id
Interface: MO_GLOBAL
Description: This function returns the current_org_id stored in the application context.
Return Value: current_org_id stored in the application context.
Return Type: NUMBER

Internal Name: get_access_mode
Interface: MO_GLOBAL
Description: This function returns the access mode stored in the application context.
Return Value: Access mode (S, M or A) stored in the application context.
Return Type: VARCHAR2

Internal Name: get_ou_count
Interface: MO_GLOBAL
Description: This function returns the number of records (operating units) stored in the multiple organizations temporary table.
Return Value: Number of accessible operating units .
Return Type: NUMBER

Internal Name: get_valid_org
Interface: MO_GLOBAL
Description: This function determines and returns the valid ORG_ID. The organization ID is obtained in the following order:

1. Parameter from the caller.
2. Current organization ID.
3. Default organization ID.

Parameters

Return Value: Returns the valid organization ID if found, or when the organization ID is passed, else returns NULL.
Return Type: NUMBER

Internal Name: validate_orgid_pub_api
Interface: MO_GLOBAL
Description: This function is used in public API‘s for backward compatibility. The status is success (S) if the org_id is:

1. Either valid with MO: Security Profile or the current organization or MO: Operating Unit
2. Derived from the current organization or MO: Default Operating Unit or MO: Operating Unit

The status is failure (F) if org_id:

1. Is either invalid with both MO: Security Profile and the current organization and MO: Operating Unit
2. Could not be derived.

To suppress the error, pass the ERROR_MESG_SUPPR as Y.

Arguments:
ORG_ID org_id for the operating unit
ERROR_MESG_ SUPPR Error message suppresser
STATUS Validation/derivation result

Parameters

Internal Name: is_mo_init_done
Interface: MO_GLOBAL
Description: This functions checks if multiple organizations is initialized. The order is Temp table -> Current Org -> Access Mode (e.g. S, M or A)
Return Value: Y if initialization is done, else N.
Return Type: VARCHAR2

Multiple Organizations Access Control Utility APIs

Name: Multi-Org Access Control Utility APIs
Internal Name: MO_UTILS
Product: Application Object Library
Type: PL/SQL
Status: Active
Description: Multi-Org Access Control utility APIs
Source file: patch/115/sql/AFMOUTLS.pls

Procedures and Functions

Internal Name: Get_Ledger_Name
Interface: MO_UTILS
Description: This function returns a primary ledger name associated with an operating unit.

Parameters

Return Value: primary ledger name.
Return Type: VARCHAR2

Internal Name: Get_Ledger_Info
Interface: MO_UTILS
Description: This function returns information about a primary ledger, such as ledger name and ledger ID, which is associated with an operating unit.

Parameters

Internal Name: get_default_ou
Interface: MO_UTILS
Description: This function returns the default operating unit based on the MO: Default Operating Unit, MO: Security Profile and MO: Operating Unit profile options.

Parameters

Internal Name: get_default_org_id
Interface: MO_UTILS
Description: This function returns the ID of the default operating unit. The default operating unit is determined by the MO: Default Operating Unit profile. If there is one operating unit in the access control list then the ID of that operating unit is returned as the default operating unit ID.
Return Value: Organization ID of the default operating unit.
Return Type: NUMBER

Internal Name: check_org_in_sp
Interface: MO_UTILS
Description: This functions checks whether an operating unit is included in the security profile. FND_GLOBAL.apps_initialize() must be called before calling this API, since the profiles are read from the cache.

Parameters

Return Value: Returns Y if an organization exists in the MO: Security Profile, else N if an organization does not exist in the MO: Security Profile or the profile option is not set.
Return Type: VARCHAR2

Internal Name: check_ledger_in_spt
Interface: MO_UTILS
Description: Use this function to determine if the user has access to all operating units for a given Ledger_ID.

Parameters

Return Value: Returns Y if all operating unit under a given ledger are accessible, else N.
Return Type: VARCHAR2

Internal Name: Get_Org_Name
Interface: MO_UTILS
Description: This function returns the operating unit name for the passed operating unit ID.

Parameters

Return Value: Operating unit name
Return Type: VARCHAR2

Internal Name: get_orgid_fr_ledger
Interface: MO_UTILS
Description: Get the operating unit ID and the number of operating units in the given ledger ID.

Parameters

Multi-Org Access Control Java Wrappers

Name: Multi-Org Access Control Java Wrappers
Internal Name: MoGlobal
Product: Application Object Library
Type: Java
Status: Active
Description: Multi-Org Access Control Java Wrappers for PL/SQL APIs
Source file: java/multiorg/server/MoGlobal.java

Methods

Method Name: init
Description: This method initializes the multiple organizations for the current database session.

Parameters

Wrapper for the PL/SQL API of MO_GLOBAL package: INIT

Method Name: setPolicyContext
Description: This method sets the multiple organizations policy context/application context.

Parameters

Wrapper for the PL/SQL API of MO_GLOBAL package: set_policy_context

Name: Multi-Org Access Control Java Wrappers
Internal Name: MoUtils
Product: Application Object Library
Type: Java
Status: Active
Description: Multi-Org Access Control Java Wrappers for PL/SQL APIs
Source file: java/multiorg/server/MoUtils.java

Methods

Method Name: checkLedgerInSp
Description: This method checks if all operating units belonging to the ledger are included in security profile and are accessible.

Parameters

Return Value: Returns Y if all operating unit in a given ledger are accessible, else N.
Return Type: java.lang.String
Wrapper for the PL/SQL API of MO_UTILS package: check_ledger_in_sp

Method Name: checkOrgInSp
Description: This method checks if the operating unit is included in the security profile.

Parameters

Return Value: Returns Y if an organization exists in the MO: Security Profile, else N if an organization does not exist in the MO: Security Profile or the profile option is not set.
Return Type: java.lang.String
Wrapper for the PL/SQL API of MO_UTILS package: check_org_in_sp

Method Name: getDefaultOrgId
Description: This method returns the default operating unit ID.

Parameters

Return Value: Organization ID of the default operating unit.
Return Type: oracle.jbo.domain.Number
Wrapper for the PL/SQL API of MO_UTILS package: get_default_org_id

Method Name: getDefaultOu
Description: This method finds the default operating unit for the current multiple organizations context.

Parameters

Wrapper for the PL/SQL API of MO_UTILS package: get_default_ou

Method Name: getLedgerInfo
Description: This method returns the ledger ID and ledger name of the operating unit.

Parameters

Wrapper for the PL/SQL API of MO_UTILS package: get_ledger_info

Method Name: getLedgerName
Description: The method returns the ledger name of an operating unit.

Parameters

Return Value: Ledger name of the operating unit.
Return Type: java.lang.String
Wrapper for the PL/SQL API of MO_UTILS package: get_ledger_name

Method Name: getOrgName
Description: This method returns the name of an operating unit on providing its ID.

Parameters

Change Record

Oracle Corporation

Author and Date 
Ramasubramanian Balasundaram, Julianna Dodick, Jason Liu

July 2007

Copyright Information 
Copyright 2007, 2008 Oracle. All rights reserved.

Disclaimer 
This document is provided for information purposes only and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission.

Trademark Information 
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

REFERENCES

Oracle Applications Multiple Organizations Access Control for Custom Code