The LOV below is defined in data foundation.
LOV name: Cust LOV
Definition (custom SQL):
SELECT DISTINCT F_Cust(c.name,@Variable(‘BOUSER’))
FROM customer c
INNER JOIN cust_rel cr ON (c.cust_id = cr.cust_id)
INNER JOIN fact f ON (f.rel_id = cr.fact_rel_id)
The parameter below is defined in data foundation.
Parameter name: Cust Par
Cust Par is a prompt to users using Cust LOV.
The parameter allows multiple values and keeps the result of the last answer.
There is VPD implemented on customer table in the Oracle data base. The VPD settings use @Variable(‘BOUSER’) to identify the user.
The Web Intelligence query uses Cust Par parameter for filtering the customers.
Running the report and refreshing the values of Cust LOV sometimes we get the expected list, other times the result is empty. In fact the result is unpredictable.
There is an object defined on customer.name column with default LOV in the business layer. The default LOV is perfect, we always get the expected list of customer names.
The value of @Variable(‘BOUSER’) is passed to the database with BEGIN_SQL parameter.
Has anybody already faced such an issue? A hint would be really helpful.