[namitsinghi]

Hi,

I have a requirement to restrict the data that user can view using Predicates. I cannot use Domain/Sub-domain to restrict the data view due to some business requirement.

I want to restrict the data that user can view to within a corporation using predicates. I have defined a VPD context variable 'CORPORATION_GID' for the corporation to which I wish to restrict the user and defined following predicate (for example) on OB_ORDER_BASE table:

Predicate:
EXISTS (SELECT 1 FROM ob_line obl WHERE obl.order_base_gid = OB_ORDER_BASE.order_base_gid and SOURCE_LOCATION_GID IN (SELECT LOCATION_GID FROM LOCATION_CORPORATION WHERE CORPORATION_GID = SYS_CONTEXT('gl_user_ctx','CORPORATION_GID')))

Test Query:
SELECT * from OB_ORDER_BASE
WHERE EXISTS
(SELECT 1 FROM ob_line obl
WHERE obl.order_base_gid = OB_ORDER_BASE.order_base_gid
and SOURCE_LOCATION_GID IN (SELECT LOCATION_GID FROM LOCATION_CORPORATION WHERE CORPORATION_GID = SYS_CONTEXT('gl_user_ctx','CORPORATION_GID')))

When running the above test query on sql developer, the data shown is only for that specified corporation. But when defininig the predicate and opening the order base page, I see all the order bases, immaterial of corporation. Can you please verify and let me know what is wrong with the above predicate and how can I restrict the search window to show only those order bases that belong to specific corporation.

I similarly would then define predicates for all the entities within OTM, Order base is just one example.

Thanks.
Namit