Discoverer 11g Querying Against An E-Business Suite 12 Multi-Org VPD Protected View With ORG_ID Returns No Data (Doc ID 732826.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

Oracle Discoverer - Version 10.1.2.2 to 11.1.1.4.0 [Release 10.1.2 to 11g]
Information in this document applies to any platform.
*** Checked for relevance on 04-Dec-2014 ***


Symptoms


When creating a custom Discoverer workbook or using SQL*Plus (or equivalent) against a custom view or a standard pre-seeded view (e.g. - oe_orders_headers_v) that is protected with a Virtual Private Data Base (VPD) context policy based on org_id,  no data is returned.

When using SQL*Plus (or equivalent), no data is returned when run via a similar way that Discoverer runs.  For example, following: <Note 279736.1> 'How To Run SQL From An Applications Mode Discoverer Workbook In SQL*PLUS'.

exec FND_GLOBAL.APPS_INITIALIZE (user_id, resp_id, appl_id, security_group_id );

select count(*) from apps.oe_order_headers_v





However, if the VPD policy is passed with the organization ID, then data is returned.

For example:

exec FND_GLOBAL.APPS_INITIALIZE (user_id, resp_id, appl_id, security_group_id );

exec apps.mo_global.set_policy_context('S',81);

select count(*) from apps.oe_order_headers_v



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms