Can VPD be used to filter Records of the Report

[moshe]

Objective:

To Use a common Report in the Master Domian and filter its Records based on Vpd for each of the user avaialble under the Master Domain.

I wrote a VPD for a particular user to restrict a few records of a Report available at the Master Domain(admin)


VPD

Table :SHIPMENT

External Predicate:

SHIPMENT_GID IN (SELECT SHIPMENT_GID FROM SHIPMENT_REFNUM WHERE SHIPMENT_REFNUM_QUAL_GID ='RAJ01EPUDO.PUDO_PROVIDER_ID' AND SHIPMENT_REFNUM_VALUE LIKE '%KIALA%') OR SHIPMENT_GID IN (SELECT SHIPMENT_GID FROM SHIPMENT_INVOLVED_PARTY WHERE INVOLVED_PARTY_QUAL_GID ='RAJ01EPUDO.SHIPPER' AND INVOLVED_PARTY_CONTACT_GID LIKE '%KIALA%')

The vpd which I wrote for the USER got reflected both in the ADMIN and the USER
Inspite of associating the Vpd only to the User Role of the USER, the Report in the ADMIN was also affected .

Inorder to solve this issue we tried restarting the server.

Restarting the Server cancelled the effect of VPD on the Report.

Note:
The Vpd is still working perfectly for the Shipment Search Screens pulling the related shipments for USER based on the conditions specified in VPD.But failing to work for the Report.

Is there any reason for this inconsistent behaviorof OTM?
Can you also kindly help me in solving this issue?

[moshe]

Can Vpd's be used to filter Records of the Report

Is it possible to restrict records fetched by the Report available at the Admin , in the User level by writting VPDs?

Objective:
I ve a report which fetches all the available shipments at the Admin level.

I ve to call the same report in the user Level such that the report fetches only the shipments which has Shipment Refnum Qual "Company_name" and Shipment Refnum Value "ABCD"

[cunninghamg]

Re: Report And Vpd

Hi moshe

First of all - It's not very wise to use wildcard searches in VPD's where they invalidate indexes.
The VPD acts as a layer between the User and the Database, on top of the Domain VPD Filter. Therefore this would make for wildly inefficient querying.

I have in the past had problems getting Roles to work correctly when first configured but it is usually Levels that cause the issue as they have some caching to flush.

VPD filters should work in Roports too, provided the OTM Report Templates have been used.
It is important that the table in the VPD (Shipment in your case) is part of the report query also.

Hope this helps

Gary

[chrisplough]

Re: Can Vpd's be used to filter Records of the Report

Honestly, I've never seen VPD used in quite this way -- instead I've always seen it used to restrict the data that a user is able to see based on table/row combinations.

It may, in fact, be possible, though instead of utilizing VPD, wouldn't it be easier to simply prepend those restrictions (Shipment Refnum Qual "Company_name" and Shipment Refnum Value "ABCD") to the report query itself? If not easier, I'm sure it would perform better, since VPD can have significant performance implications, depending on the use.

--Chris

[cheekythought]

Re: Report And Vpd

As far as i am aware, report can have VPD profiles successfully applied without using report templates provided the correct table (with VPD) is joined in the report query.

May be i am missing something here?

[[email protected]]

Re: Report And Vpd

Hi

I am having trouble with setting the VPD for an OTM Report using Oracle Reports Builder
I want to set the VPD according to the multiple roles for a user. The issue is that no matter which role I switch to it returns values based on the primary role.
Currently I am using the below instruction in the report trigger
set_vpd(: p_gl_user)

Appreciate any help i can get.

Regards,

Varun