Understanding Multi-Organization Structure in EBS(Technical) - Part II

Posted on July 14th, 2007 by Sanjit Anand | Print This Post | Email This Post

The multiple organization support feature uses native database features to build a security layer on top of a single installation of Oracle Applications. This layer of security provides the necessary data partitioning; while at the same time minimizes the number of potentially destabilizing changes to the application code itself. The security layer is provided using database views, which allow access to the partitioned data without any changes to the applications code.

For ERP applications, data partitioning is performed by database views. These views reside in the APPS Oracle schema and derive the appropriate operating unit context from an RDBMS variable.

All applications code is run against the APPS schema. The Applications database architecture is now the same for a Multiple Organizations and non-Multiple Organizations implementation.

Multi Organization Tables

• Multiple Organizations in Oracle Applications is enabled by partitioning some database tables by operating unit. Other tables are shared across operating units (and thus across sets of books).
• In general, the following criteria determine if a table would be partitioned:
  • The table contains a GL Account Code (code combination ID).
  • There is a business reason for the table to be partitioned (for example, the entity should not be shared).
  • The table contains transaction data.
  • The table is an interface table where data being loaded is partitioned.

Applications with Partitioned tables

o Oracle Cash Management
o Oracle Order Management and Shipping Execution
o Oracle Payables
o Oracle Property Manager
o Oracle Projects
o Oracle Purchasing
o Oracle Release Management
o Oracle Receivables
o Oracle Sales Compensation
o Oracle Sales and Marketing
o Oracle Service
o European Localizations
o Latin America Localizations
o Regional Localizations

RDBMS Variable
A global variable exists in the Oracle database called CLIENT_INFO, which is 64 bytes long. The first 10 bytes are used to store the operating unit ID (or ORG_ID) for the Multiple Organization Support feature. The CLIENT_INFO context is derived from a profile option that the user sets for each responsibility as part of the Multi-Org setup steps.

• Org_id column in the warehouse contains the Operating Unit ID
• Organization_id column contains the warehouse / Inventory Org.

select * from <table name>_ALL
where ORG_ID = SUBSTRB(USERENV(’CLIENT_INFO’),1,10);