Dashboards, Reporting & Business Intelligence

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')

The below tables are used to store the descriptive flexfield definition

FND_DESCRIPTIVE_FLEXS stores setup information about descriptive
flexfields. Each row includes the name of the table that contains the
descriptive flexfield columns, the name and title of the flexfield, the
identifier of the application with which the flexfield is registered,
whether the flexfield is currently frozen, whether this is a protected
descriptive flexfield, the name of the structure defining column for the
flexfield (CONTEXT_COLUMN_NAME), and other information about
how the flexfield is defined. You need one row for each descriptive
flexfield in each application. Oracle Application Object Library uses this
information to generate a compiled definition for a descriptive flexfield.

FND_DESCR_FLEX_COLUMN_USAGES stores the correspondences
between application table columns and the descriptive flexfield
segments. Each row includes an application identifier, the name of a
descriptive flexfield, and a column name. The context field value, also
known as the structure name, is in
DESCRIPTIVE_FLEX_CONTEXT_CODE. Each row also includes the
segment name (END_USER_COLUMN_NAME), the display
information about the segment such as prompts, display size, type of
default value, whether the segment is required or not, whether the
segment is part of a high, low segment pair, whether security is enabled
for the segment, whether to display the segment or not, and the value
set the segment uses. You need one row for each segment for each
context value (structure), including global data element segments, for
each descriptive flexfield of each application. Oracle Application Object
Library uses this information to create a compiled descriptive flexfield
definition to store in the FND_COMPILED_DESCRIPTIVE_FLEXS table.

FND_CONCURRENT_PROGRAMS stores information about concurrent programs. Each row includes a name and description of the concurrent program. Each row also includes the execution methods for the program (EXECUTION_METHOD_CODE), the argument method (ARGUMENT_METHOD_CODE), and whether the program is constrained (QUEUE_METHOD_CODE).

If the program is a special concurrent program that controls the concurrent managers,
QUEUE_CONTROL_FLAG is set to Y. Each row also includes flags that indicate whether the program is enabled and defined as run–alone, as well as values that specify the print style the concurrent manager should use to print program output, if any. There are also values that identify the executable associated with the concurrent program and the application with which the executable is defined, and flags that specify whether the concurrent program is a parent of a report set, whether to save the output file, and whether a print style is required.

Information such as printer name and number of rows and columns on each page of
the output file for the concurrent program is also included in the table.
You need one row for each concurrent program in each application. Oracle Application Object Library uses this information to run concurrent programs

FND_CONCURRENT_PROCESSES stores information about concurrent managers. Each row includes values that identify the ORACLE process, the operating system process, and the concurrent manager (QUEUE_APPLICATION_ID and CONCURRENT_QUEUE_ID). You
need one row for each instance of a running concurrent manager (each process), as well as one row for the Internal Concurrent Manager.
Oracle Application Object Library uses this table to keep a history of
concurrent managers. You should never update this table manually. .
You can use the Purge Concurrent Request and/or Managers Data
program to delete history information periodically.