Oracle Applications Multiple Organizations Access Control for Custom Code_mo: security profile table lov

文档 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.

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

---

Introduction

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

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

Overview of Multiple Organizations Architecture (prior to Release 12)

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

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

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

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

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

---

Technical Details

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

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

Access Control Architecture

Background

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

Figure 1: Database Schema

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

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

Virtual Private Database (VPD)

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

Figure 2: Database Schema - Access to one operating unit

Figure 3: Database Schema - Access to multiple operating units

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

Multiple Organizations Security Policy Predicate

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

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

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

MO_GLOBAL.Org_Security function:

FUNCTION org_security(obj_schema VARCHAR2
					   obj_name VARCHAR2)RETURN VARCHAR2
IS
BEGIN
  --
  -- Returns different predicates based on the access_mode
  -- The codes for access_mode are
  -- M - Multiple OU Access
  -- A - All OU Access
  -- S - Single OU Access
  -- Null - Backward Compatibility - CLIENT_INFO case
  --
 IF g_access_mode IS NOT NULL THEN
   IF g_access_mode = 'M' THEN
     RETURN 'EXISTS (SELECT 1
                       FROM mo_glob_org_access_tmp oa
                       WHERE oa.organization_id = org_id)';
   ELSIF g_access_mode = 'A' THEN -- for future use
     RETURN NULL;
   ELSIF g_access_mode = 'S' THEN
     RETURN 'org_id = sys_context(''multi_org2'',''current_org_id'')';	
   END IF;
 ELSE
   RETURN 'org_id = substrb(userenv(''CLIENT_INFO''),1,10)';
 END IF;	
END org_security;

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

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

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

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

The complex predicate is used for these cases:

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

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

A simple query by the user:

SELECT trx_number from ra_customer_trx

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

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

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

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

Multiple Organizations Initialization

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

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

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

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

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

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

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

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

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

Multiple organizations initialization performs the following:

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

Data Model Design

New Tables

MO_GLOB_ORG_ACCESS_TMP

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

A unique index MO_GLOB_ORG_ACCESS_TMP_U1 exists on ORGANIZATION_ID column.

FND_MO_PRODUCT_INIT

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

A unique index FND_MO_PRODUCT_INIT_U1 exists on APPLICATION_SHORT_NAME column.

Multiple Organizations Public APIs

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

BC4J Objects

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

Operating Unit LOV View Object

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

OperatingUnitsRN
  OperatingUnitsTable
    OUName
    OrganizationId

OperatingUnitsRN

OperatingUnitsTable

OUName

OrganizationId

AttributeSets

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

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

OperatingUnitName_Transient

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

OperatingUnitName_Persistent

The attributesets are available in this file and location:

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

Multiple Organizations Views/Tables Changes

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

NOT NULL Logical Constraint on ORG_ID column

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

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

Modify Your Database Views

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

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

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

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

Single Organization Views

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

Case 1: Single Organization view

Example 1:

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

CREATE OR REPLACE VIEW RA_BATCHES AS
SELECT "BATCH_ID",
       "LAST_UPDATE_DATE", 
       "LAST_UPDATED_BY",
       "CREATION_DATE",
       ...
       "ORG_ID",
       "PURGED_CHILDREN_FLAG",
       "ISSUE_DATE",
       "MATURITY_DATE",
       "SPECIAL_INSTRUCTIONS",
       "BATCH_PROCESS_STATUS",
       "SELECTION_CRITERIA_ID" 
  FROM RA_BATCHES_ALL
 WHERE
NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)

A synonym replaces the single organization view RA_BATCHES.

CREATE SYNONYM RA_BATCHES FOR AR.RA_BATCHES_ALL

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

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

Example 2:

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

CREATE OR REPLACE VIEW AR_VAT_TAX_B AS
SELECT "VAT_TAX_ID",
       "SET_OF_BOOKS_ID",
       "TAX_CODE",
       ...
       "ORG_ID",
   ...
  FROM AR_VAT_TAX_ALL_B
 WHERE
NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
= NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)

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

CREATE SYNONYM AR_VAT_TAX_B FOR AR.AR_VAT_TAX_ALL_B

Example 3:

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

CREATE OR REPLACE VIEW AP_CARD_SUPPLIERS AS
SELECT ROWID,
       CARD_ID,
       VENDOR_ID,	
       ORG_ID,
...
  FROM AP_CARD_SUPPLIERS_ALL
WHERE NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),	
' ', NULL,SUBSTRB(USERENV('CLIENT_INFO'),1 ,10))), -99)) =
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)

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

CREATE SYNONYM AP_CARD_SUPPLIERS FOR AP.AP_CARD_SUPPLIERS_ALL

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

Example 4:

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

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