Creating VPD profile to access Order releases in Servprov

[surender]

Hi

I followed the following steps to add the External predicate to SERVPROV

Opened the VPD profile for Servprov and put in the following query

exists (select order_release_gid from v30_shipment_order_release where v30_shipment_order_release.shipment_gid in (select shipment.shipment_gid from shipment where shipment.servprov_gid IN( (SELECT user_association.user_association_value FROM user_association WHERE user_association.gl_user_gid = sys_context('gl_user_ctx', 'gl_user_gid') AND user_association.user_association_qual = 'SERVPROV') UNION (SELECT tcs.servprov_gid FROM user_association ua, tender_collab_servprov tcs WHERE ua.gl_user_gid = sys_context('gl_user_ctx', 'gl_user_gid') AND ua.user_association_value = tcs.dispatch_location_gid AND ua.user_association_qual = 'DISPATCH LOCATION') )))

I basically took the query under servprov for Shipment and put a query around it to retrieve order_releases.

Use External predicate , Use Domain Rule and Use Servprov Rule are all checked

I saved this profile and tried to open the following id
SERVPROV.CRP-77777

I see the one shipment associated with the service provider but I am seeing all the order release (even the ones in another domain).

How can this be fixed?

[surender]

Re: Creating VPD profile to access Order releases in Servprov

To access only order releases for a specific carrier in the carriers login the following query can be used
ORDER_RELEASE.ORDER_RELEASE_GID IN (select order_release_gid from v30_shipment_order_release where v30_shipment_order_release.shipment_gid in (select shipment.shipment_gid from shipment where shipment.servprov_gid IN( (SELECT user_association.user_association_value FROM user_association WHERE user_association.gl_user_gid = sys_context('gl_user_ctx', 'gl_user_gid') AND user_association.user_association_qual = 'SERVPROV') UNION (SELECT tcs.servprov_gid FROM user_association ua, tender_collab_servprov tcs WHERE ua.gl_user_gid = sys_context('gl_user_ctx', 'gl_user_gid') AND ua.user_association_value = tcs.dispatch_location_gid AND ua.user_association_qual = 'DISPATCH LOCATION') )))