Multi Org Structure Virtual Private Database
Please read the Multi-Org Background information
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.
Database Schema - Access to one operating unit
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_trxWHERE (EXISTS (SELECT 1FROM mo_glob_org_access_tmp oaWHERE 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_trxORG_ID = sys_context('multi_org2','current_org_id')
Multi Org Structure
Aug 11, 2008 ERP
Overview of Multiple Organizations Architecture for release 11 and prior
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.
Here is some background information
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.
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.
Continue reading Multi-Org structure - Vitual Private Database
Data Conversion for Oracle Applications
Aug 5, 2008 ERP
Data Conversion for Oracle Applications
This topic is shared to provide an overview of Data Conversion for Oracle ERP.Approach used for Data Conversion with details on each and every steps as below find here
data-conversion-for-oracle-applications
1. Conversion Data Mapping
2. Download Programs
3. ASCII Flat File
4. Upload Program
5. Interface
Interface Table
Creation of Interface Table
6. Translation Programs
7. Interface Programs
8. Application Production Table
9. Testing
10. Write and Perform Conversion Execution Plan