文档 ID 420787.1
White Paper
Oracle Applications Multiple Organizations Access Control for Custom Code
Checked for relevance on 12-JAN-2011
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.
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:
'org_id = substrb(userenv(''CLIENT_INFO''),1,10)'
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.
|
Note: The Operating Unit Mode field is added to the Define Concurrent Program in OA Framework pages only. |
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.
| Application_Short_Name | Status |
|---|---|
| AR | Y |
| JTF | Y |
| <Custom application short code> | Y or N |
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.
|
Note: Multiple Organizations API FND_MO_PRODUCT_INIT_PKG.register_application is available to register this information in the Multiple Organizations table. See Multiple Organizations Public APIs for details regarding this API. |
Products must call the MO_GLOBAL.init() API to execute the multiple organizations initialization.
Multiple organizations initialization performs the following:
- Initializes the security policy predicate
- Populates a global temporary table that is used in the user interfaces and the security policy function.
|
Attention: Do not access the global temporary table directly. Instead, use the PL/SQL functions to access data from the temporary table. The FND_GLOBAL.APPS_INITIALIZE routine does NOT automatically call mo_global.init routine. You must explicitly invoke the mo_global.init routine to initialize the organization context. |
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.
| Column Name | Type | Null | Unique | Column Description | Translatable |
|---|---|---|---|---|---|
| ORGANIZATION_ID | Number(15) | Not Null | Yes | Operating unit identifier | No |
| NAME | Varchar2(240) | Null | Name of the operating unit | Yes |
A unique index MO_GLOB_ORG_ACCESS_TMP_U1 exists on ORGANIZATION_ID column.
|
Note: The operating unit is stored in the language set at client environment or server environment. The legal entity information is no longer stored in the Multiple Organizations temporary table. The org_classification column is not added to the temporary table as the temporary table contains only operating units. |
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.
| Column Name | Type | Null | Unique | Column Description | Translatable |
|---|---|---|---|---|---|
| APPLICATION_SHORT_NAME | Varchar2(50) | Not Null | Yes | Application Short Name | No |
| CREATION_DATE | Date | Not Null | Creation Date | No | |
| CREATED_BY | Number(15) | Not Null | Created By | No | |
| LAST_UPDATED_BY | Number(15) | Not Null | Last Updated By | No | |
| LAST_UPDATE_DATE | Date | Not Null | Last Update Date | No | |
| LAST_UPDATE_LOGIN | Number(15) | Last Update Login | No |
A unique index FND_MO_PRODUCT_INIT_U1 exists on APPLICATION_SHORT_NAME column.
|
Note: The APPLICATION_ID column is not used in this table, since ID column values are not portable. |
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).
| Package | Application Module | Generate Java Files for AM | View Object Instances | View Object Query | Generate Java Class for View Object | Generate Java Class for View Row | Generate Accessors |
|---|---|---|---|---|---|---|---|
| oracle.apps.fnd.multiorg.lov.server | OperatingUnitLovAM | False | OperatingUnitsVO |
select ou.organization_id org_id, |
False | True | True |
OperatingUnitsRN
OperatingUnitsTable
OUName
OrganizationId
OperatingUnitsRN
| ID | Region Style | AM Definition |
|---|---|---|
| OperatingUnitsRN | ListOfValues | oracle.apps.fnd.multiorg.lov.server.OperatingUnitLovAM |
OperatingUnitsTable
| ID | Region Style |
|---|---|
| OperatingUnitsTable | Table |
OUName
| ID | Item Style | Attribute Set | Search Allowed | View Instance | View Attribute |
|---|---|---|---|---|---|
| OUName | messageStyledText | /oracle/apps/fnd/attributesets/HrOperatingUnits/OperatingUnitName_Persistent | True |
OperatingUnitsVO1 |
OperatingUnit |
OrganizationId
| ID | Item Style | Data Type | View Instance | View Attribute |
|---|---|---|---|---|
| OrganizationId | formValue | Number |
OperatingUnitsVO1 |
OrgId |
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
| Property | Property Description | Value |
|---|---|---|
| Prompt | Text label for the component | Operating unit |
| Columns | Item display length | 30 |
| Comment | Describes attribute set usage | Operating unit name for transient items |
| Data type | Data type | VARCHAR2 |
| Document Name | Property Name | OperatingUnitName_Transient |
| Maximum Length | Maximum number of characters allowed in the item value | 240 |
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
| Property | Property Description | Value |
|---|---|---|
| Prompt | Text label for the component | Operating unit |
| Columns | Item display length | 30 |
| Comment | Describes attribute set usage | Operating unit name for persistent items |
| Document Name | Property Name | 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.
|
Attention: Do not attach the security policy to base tables directly because there is code around the base tables (_ALL, ALL_B, _ALL_TL) that must access the operating units. |
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.
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:
- The secured synonym is a driving table
- 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).
|
Attention: Every reference view must have only one secured synonym. Limiting the number of secured synonyms to one synonym improves performance. |
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.
|
Note: The policy name parameter accepts any value. However, for easy maintenance, you must use the ORG_SEC for multiple organizations access control security. |
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.
|
Note: For convenience, the sample code is entered in the trigger in the examples. |
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.
|
Attention: The app_standard.event() call in the pre-form trigger executes the AOL initialization (fnd_global.apps_initialize()). You must execute the multiple organizations initialization after this call. If you do not follow this order, then the MO: Operating Unit and MO: Security Profile profile options are not cached for the right context, which results in incorrect initialization for the session. |
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
| Item Type | Data Type | Maximum Length | Database Item | Canvas | Width |
|---|---|---|---|---|---|
| Text Item | Number | 15 | Yes |
Null |
<Anything> |
OPERATING_UNIT Field Details
| Item Type | Datatype | Maximum Length | Database Item | Canvas | Width |
|---|---|---|---|---|---|
| Text Item | Char | 240 | No |
<You Canvas Name> |
1.5 |
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
| Column Name | Datatype | Length |
|---|---|---|
| OPERATING_UNIT | Char | 240 |
| ORG_ID | Number | 0 |
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
| Column Name | Display Width | Return Item | Column Title |
|---|---|---|---|
| OPERATING_UNIT | 1.5 | <block name>.operating_unit | Operating Unit |
| ORG_ID | 0 | <block name>.org_id | Organization ID |
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.
| Parameter Name | Datatype | Maximum Length |
|---|---|---|
| MO_DEFAULT_ORG_ID | Number | 15 |
| MO_DEFAULT_OU_NAME | Char | 240 |
| MO_OU_COUNT | Number | 15 |
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.
|
Attention: Do not set the "current organization" in different triggers for the new forms. Use the _ALL tables and include the form block ORG_ID to restrict data for the operating unit that the user selects and avoid Virtual Private Database context switching. |
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;
|
Note: The defaulting API returns data, though the MO: Default Operating Unit profile option is not set when the responsibility can access one operating unit only. Therefore, the ELSE condition for setting the policy context does not need to check the parameter.ou_count value. |
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;
|
Note: You must set the policy context in the When-Validate-Item trigger of the Operating Unit field if the Find window displays the Operating Unit field. If the value of mo_ou_count is more than one for forms that use row list of values in the Find windows, then you must set the policy context to multiple to view the operating units information. |
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;
|
Note: Some forms such as the Receivables Receipt Workbench may need the On-Lock trigger instead of the Pre-Update trigger. |
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.
|
Note: You must initialize the When-Validate-Item trigger of the Operating Unit field and When-Validate-Item triggers of the Operating Unit specific fields for forms that support the derive operating unit feature. For more information, refer toDerive Operating Unit feature. |
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.
|
Attention: If you use the post-change trigger to populate the operating unit specific fields, you must move the post-query trigger. |
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_codeAND a.org_id = <your block name>.org_id;END IF; ...
|
Attention: You do not need to join to form block ORG_ID in the where clause as the policy context is single. Forms that allow the user to commit multiple records must include the initialization in the pre-insert and pre-update triggers and forms that force the user to commit the record when the user navigates out of the record must include the initialization in the pre-record trigger. |
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.
|
Note: The APIs in the server do not validate The ORG_ID in the form block, as the security policy context is single. |
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.
|
Note: Bulk collect is good for performance. However, it cannot be used with the PL/SQL version in the client, so a server side package is implemented to take care of bulk collect. The cache (PL/SQL table of records) is created in the client to reduce network trips between the server and the client. |
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.
|
Note: If you do not have an existing library, then create a new library and attach the library directly to the form. |
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.
|
Note: You must include the code to copy the cache to the parameter or non-base table block in the When-Validate-Item trigger of the Operating Unit field and the When-Validate-Item triggers of the Operating Unit specific fields to derive the operating unit for forms that support the derive operating unit feature. For more information, see Derive Operating Unit feature. |
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
|
Attention: Post-Query trigger fires for every record on a blind query and therefore you must rewrite your SQL to use _ALL tables and use the ORG_ID join condition (based on the form block ORG_ID). You do not need to synchronize the cache in the post-query trigger. The WNRI synchronizes the cache. |
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;
|
Note: For forms such as the Receivables Receipt Workbench, you need the ON_LOCK trigger instead of the Pre-Update trigger. |
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
|
Note: It is the product team's discretion to implement the select operating unit or derive the operating unit feature for the record groups based on the business logic. There is no difference to the record group SQL for the select operating unit and derive operating unit because of the policy context. The operating unit dependent fields are grayed out for forms supporting the select operating unit, until the user selects an operating unit. The records groups of these fields depend on the_ALL tables and not the secured synonym. |
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.
|
Note: The table does not allow you to drop the database default value. Therefore, you must replace the default function with 'null'. |
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>
|
Note: You must add the ORG_ID filter to the WHERE clause to update tables that include the ORG_ID as the composite or driving key for the table. |
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.
|
Note: The queries performed on the hr_operating_units view queries include the user's current language context. |
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:
- 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.
- Call the fnd_key_flex.define in the following triggers:
- Block Level: When-Create-Record
- Item Level: When-Validate-Item of Operating Unit field and Operating Unit specific fields for “Derive Operating Unit” feature.
- Block Level: Post-Query
- Block Level: Pre-Query (to query accounting flexfields)
- 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;
|
Note: The parameter NUM to the flexfield API is changed to enable access control. If you implement the "Derive Operating Unit" feature, then you must call the fnd_key_flex.define (from the WVI triggers of Operating Unit specific fields) before populating the default key flexfield value from the operating unit specific fields of the transaction. You must implement the code in the triggers of master and detail blocks for accounting flexfields in master and detail blocks. |
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:
| Operating Unit Field | Accounting Flexfield Field | Result |
|---|---|---|
| Any Value | Null | Execute the standard enter query. |
| Null | Not Null | Display message “Please enter Operating Unit” for multiple organizations. |
| Not Null (cannot identify the operating unit) | Not Null | Display message “The system cannot identify KFF structure. Please enter the full Operating Unit name”. |
| Not Null (can identify operating unit) | Not Null | Execute the standard enter-query. |
| Not Null (cannot find any operating unit matching the condition) | Not Null | Execute the standard enter query. |
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.
|
Note: Oracle Applications provides the ability to pass the current organizations to populate fnd_requests.submit_request() API for single organization concurrent programs submitted from transaction windows. The details of invoking the fnd_request.submit_request() are documented later. Do not refer to CLIENT_INFO logic and remove the NVL function for ORG_ID in the concurrent programs. |
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:
- Standardize validation logic.
- 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.
|
Note: The operating unit is passed only if the operating unit is different from the operating unit specified in the MO: Default Operating Unit profile option. |
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.
- Uses the ORG_ID if passed
- Searches for the current organization value if the ORG_ID is not passed
- 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.
- 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:
- ORG_ID IN OUT NUMBER
- ERROR_MESG_SUPPR IN VARCHAR2(1) DEFAULT N
- 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.
|
Note: The current organization or the defaulting utility can return a null value. Therefore, the Multiple Organizations API validates the API values and the value that the caller passes. |
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;
|
Attention: Capture the function output in a new variable since the Multiple Organizations API may return NULL if the ORG_ID value is INVALID. |
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:
- Use the ORG_ID if explicitly passed.
- Use the current org value if ORG_ID is not passed.
- Use the default operating unit if the ORG_ID is not passed and current organizations is not set.
- 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;
|
Attention: Multiple Organizations API MO_GLOBAL.set_policy_context compares the current organization with the new organization value passed in the API. If the value is the same, the API does not reset the application context for access mode and current organization, otherwise it resets the application context. This is done for performance reasons as resetting the context invalidates the security policy predicate, which must be re-parsed again. This operation must be avoided. Products that code new public APIs or own public APIs not shared in the user interface layer where the current organization is set must rewrite the code to use _ALL tables for efficient operations. When processing records in a batch that contain different operating units records, it is better to sort the data by operating unit and then process the data belonging to the same organization and reset the context. |
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=> 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.
|
Note: Some foreign key validations continue to work similar to secured synonyms/reference views, as the synonyms/views have many product specific filters (other than the org_id) to protect you from the external product’s complexity. When using secured synonyms, you must rewrite the SQL to use one secured synonym and _ALL table references for the rest. You must add the ORG_ID joins to the WHERE clause of the SQL statement to avoid cartesian joins, for Multiple Organizations tables that include the ORG_ID as a composite or driving key. |
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.
|
Attention: With multiple organizations access control, use callback functions to set the current organization ID and not the CLIENT_INFO organization context. |
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.
|
Attention: This document supplements the OA coding standards. |
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:
- 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).
- 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:
- When reserving or activating the application module.
- 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
| ID | Item Style | Attribute Set |
|---|---|---|
| OperatingUnit | messageLovInput | /oracle/apps/fnd/attributesets/HrOperatingUnits/OperatingUnitName_Transient |
Org Id Field Details
| ID | Item Style | Data Type |
|---|---|---|
| OrgId | formValue | NUMBER |
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
| ID | LOV Region Item | Return Item | Criteria Item |
|---|---|---|---|
| FromToOperatingUnit | OUName | OperatingUnit | OperatingUnit |
Mapping for OrgID Field
| ID | LOV Region Item | Return Item |
|---|---|---|
| ToOrganizationId | OrganizationId | OrgId |
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
|
Attention: You MUST NOT set the current org_id in the controller object code as shown for new OA Framework page flows. For new pages, you use the _ALL base tables and include the organization ID for the page (formValue bean) to restrict data to the operating unit that the user selects. |
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.
|
Note: Product teams must not modify the RDBMS default ORG_ID value to use the current organization. The current_org is introduced to minimize the code change for the product code that is executed in one operating unit. The operating unit is validated up front in OA Framework pages. Therefore, it is safe to use this value in the table handlers rather than using the RDBMS default value. |
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" %> ...
|
Note: Multiple organizations context must be initialized by calling the initMultiOrg() method when using TransactionScope.getNewConnection(). |
Additional Information on Search Services:
- Connections established by calling TransactionScope.getNewConnection() must call MultiOrgUtils.initMultiOrg(...).
- 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
| Name | Internal Name | Status | Description |
|---|---|---|---|
| Initialize | INIT | Active | Set up multiple organizations context. |
| JTT initialize | JTT_INIT | Active | Initialize multiple organizations for JTT based application. |
| Is multi-org enabled | is_multi_org_enabled | Active | Check if the multiple organizations instance is enabled. |
| Check access | check_access | Active | Check if the operating unit is accessible. |
| Get OU name | get_ou_name | Active | Get the operating unit name. |
| Check valid org | check_valid_org | Active | Check if the organization is valid. |
| Set policy context | set_policy_context | Active | Set the application policy context. |
| Get current org Id | get_current_org_id | Active | Get the current organization ID in the application context. |
| Get access mode | get_access_mode | Active | Get the application context mode. |
| Get OU count | get_ou_count | Active | Get the operating unit count on the access control list. |
| Get valid org | get_valid_org | Active | Get the current default/valid organization. |
| Validate org id public API | validate_orgid_pub_api | Active | Get the default organization and check if the organization is valid. |
| Is MO initialization done | is_mo_init_done | Active | Check if the multiple organization is initialized. |
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
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| p_appl_short_name | NUMBER | IN | Pass 'S' or 'M' to initialize the operating unit for single or multiple operating unit context. | ||
| p_icx_session_id | NUMBER | IN | ICX session ID |
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
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| p_appl_short_name | VARCHAR2 | IN | Pass 'S' or 'M' to initialize the operating unit for single or multiple operating unit context. |
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:
- If the operating unit exists in the PL/SQL array. The PL/SQL array is populated by the set_org_access multiple organizations API.
- If the operating unit is same as the current organization ID for single ('S') organization initialization.
Parameters
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| p_org_id | NUMBER | IN | Pass the operating unit ID to check if the operating unit is present in the access list. |
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
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| p_org_id | NUMBER | IN | Pass the operating unit ID to get the name. |
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
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| p_org_id | NUMBER | IN | Pass the operating unit ID to check if it is present in the access control list. Display an error message if required and the value is N. |
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
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| p_access_mode | VARCHAR2 | IN | Pass the access mode S or M to set the multiple organizations policy context for single or multiple operating units respectively. | ||
| p_org_id | NUMBER | IN | If access mode is S, pass an operating unit ID to set the current organization context. |
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:
- Parameter from the caller.
- Current organization ID.
- Default organization ID.
Parameters
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| p_org_id | NUMBER | IN | Pass the operating unit ID and validate it against the access control list. |
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:
- Either valid with MO: Security Profile or the current organization or MO: Operating Unit
- Derived from the current organization or MO: Default Operating Unit or MO: Operating Unit
The status is failure (F) if org_id:
- Is either invalid with both MO: Security Profile and the current organization and MO: Operating Unit
- 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
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| org_id | NUMBER | IN - OUT | Pass an operating unit ID that must be validated. When the org_id is not passed, the system derives the org_id. | ||
| Error_mesg_suppr | VARCHAR2 | IN | N | A flag to suppress or raise an error when the organization ID passed is not valid or the system could not derive the org_id. | |
| Status | VARCHAR2 | OUT | S (success) if the org_id passed is valid or the system derives the org_id. F (failure) if org_id passed is invalid or system could not derive the org_id. |
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
| Name | Internal Name | Status | Description |
|---|---|---|---|
| Get Ledger Name | Get_Ledger_Name | Active | Returns the ledger name. |
| Get ledger information | Get_Ledger_Info | Active | Returns information about the ledger. |
| Get Default operating unit | get_default_ou | Active | Gets the default operating unit from MO: Default Operating Unit profile or from current organization. |
| Get Default operating unit Id | get_default_org_id | Active | Returns the organization ID of the default operating unit. |
| Check org in security profile | check_org_in_sp | Active | Checks if the specified organization is present in the security profile. |
| Check ledger in security profile | check_ledger_in_sp | Active | Checks if all operating units of a ledger are included in the security profile. |
| Get organization name | Get_Org_Name | Active | Returns the operating unit name for an organization ID. |
| Get organization IDs for the ledger | get_orgid_fr_ledger | Active | Returns the operating unit ID and the number of operating units in the given ledger. |
Internal Name: Get_Ledger_Name
Interface: MO_UTILS
Description: This function returns a primary ledger name associated with an operating unit.
Parameters
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| p_operating_unit | NUMBER | IN | Operating unit ID. |
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
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| p_operating_unit | NUMBER | IN | Operating unit ID. | ||
| p_ledger_id | NUMBER | OUT | Ledger ID of an operating unit. | ||
| p_ledger_name | VARCHAR2 | OUT | Ledger Name of an operating unit. |
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
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| p_default_org_id | NUMBER | OUT | Operating unit ID. | ||
| p_default_ou_name | VARCHAR2 | OUT | Default operating unit name. | ||
| p_ou_count | NUMBER | OUT | Number of operating units in the access list. |
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
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| p_org_id | NUMBER | IN | Operating unit ID. | ||
| p_org_class | VARCHAR2 | IN | Organization is classified either OPERATING_UNIT or HR_BG. |
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
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| p_ledger_id | NUMBER | OUT | Ledger ID. |
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
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| p_org_id | NUMBER | IN | Operating unit ID. |
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
| Name | Type | Direction | Precision/Size | Default Value | Description |
|---|---|---|---|---|---|
| sob_or_ledger_id | NUMBER | IN | Ledger ID. | ||
| org_count | NUMBER | OUT | Number of operating units in the ledger. | ||
| org_Ids | TABLE | OUT | Table of operating unit IDs. |
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
| Name | Internal Name | Status | Description |
|---|---|---|---|
| Initialization | init | Active | Initializes multiple organizations. |
| Set Multi-Org policy context | setPolicyContext | Active | Sets the multiple organizations policy context/application context to single or multiple operating units. |
Method Name: init
Description: This method initializes the multiple organizations for the current database session.
Parameters
| Name | Type | Description |
|---|---|---|
| dbtransaction | oracle.jbo.server.DBTransaction | Database transaction. |
| p_appl_short_name | java.lang.String | Application Short Name |
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
| Name | Type | Description |
|---|---|---|
| dbtransaction | oracle.jbo.server.DBTransaction | Database transaction. |
| p_access_mode | java.lang.String | Pass access mode - S or M. |
| p_org_id | oracle.jbo.domain.Number | Operating unit ID if the access mode is S. |
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
| Name | Internal Name | Status | Description |
|---|---|---|---|
| Check ledger in security profile | checkLedgerInSp | Active | Checks if all operating units assigned to the ledger are included in the security profile. |
| Check organization in security profile | checkOrgInSp | Active | Checks if the organization is included in the security profile. |
| Get default operating unit id | getDefaultOrgId | Active | Returns the default operating unit for the current multiple organizations session. |
| Get default operating unit | getDefaultOu | Active | Obtain the default operating unit for the current multiple organizations context. |
| Get Ledger information | getLedgerInfo | Active | Returns the ledger information of the operating unit. |
| Get Ledger name | getLedgerName | Active | Returns the ledger name of the operating unit. |
| Get organization name | getOrgName | Active | Returns the organization name, on providing its ID. |
Method Name: checkLedgerInSp
Description: This method checks if all operating units belonging to the ledger are included in security profile and are accessible.
Parameters
| Name | Type | Description |
|---|---|---|
| dbtransaction | oracle.jbo.server.DBTransaction | Database transaction. |
| p_ledger_id | oracle.jbo.domain.Number | Ledger ID |
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
| Name | Type | Description |
|---|---|---|
| dbtransaction | oracle.jbo.server.DBTransaction | Database transaction. |
| p_org_id | oracle.jbo.domain.Number | Operating unit ID. |
| p_org_class | Java.lang.String | Organization classification code. |
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
| Name | Type | Description |
|---|---|---|
| dbtransaction | oracle.jbo.server.DBTransaction | Database transaction. |
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
| Name | Type | Description |
|---|---|---|
| dbtransaction | oracle.jbo.server.DBTransaction | Database transaction. |
| p_default_org_id | oracle.jbo.domain.Number | Organization ID of the default operating unit. |
| p_default_ou_name | java.lang.String | Name of the default operating unit. |
| p_ou_count | oracle.jbo.domain.Number | Number of operating units in the access control list. |
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
| Name | Type | Description |
|---|---|---|
| dbtransaction | oracle.jbo.server.DBTransaction | Database transaction. |
| p_operating_unit | oracle.jbo.domain.Number | Organization ID of the operating unit that is entered. |
| p_ledger_id | oracle.jbo.domain.Number | Displays the ledger ID. |
| p_ledger_name | java.lang.String | Displays the ledger name. |
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
| Name | Type | Description |
|---|---|---|
| dbtransaction | oracle.jbo.server.DBTransaction | Database transaction. |
| p_operating_unit | oracle.jbo.domain.Number | Operating unit ID that is entered. |
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
| Name | Type | Description |
|---|---|---|
| dbtransaction | oracle.jbo.server.DBTransaction | Database transaction. |
| p_org_id | oracle.jbo.domain.Number | Operating unit ID that is entered. |
Change Record
| Date | Description of Change |
|---|---|
| July 06, 2007 | Published document. |
| January 29, 2008 | Implemented remarks and published document. |
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.