I am working on oracle EBS custom report. One of the parameter (Invoice Type) i have to use 5 times in order for the user to pass 5 values if needed as there is no Multi select in parameter LOV.
So I create same parameter 5 times and in each parameter user select different values. i use the following
I have pararmeters P_TYPE1, P_TYPE2, P_TYPE3, P_TYPE4, P_TYPE5 . Same lOV value set for each of them
Where condition is following
AND (cust_trx_type_id = nvl(:P_TYPE1,cust_trx_type_id)) OR (cust_trx_type_id = :P_TYPE2) OR (cust_trx_type_id = :P_TYPE3) OR (cust_trx_type_id =:P_TYPE4)
OR (cust_trx_type_id =:P_TYPE5)
in the above if The user enter value for each , it is fine. if the user enter value for P_TYPE1 and leave otherS blank its fine . if everything blank it brings all, that is fine as well.
However if the user leave the P_TYPE1 empty and enter for TYPE2 - TYPE5 . Then there is a problem. it will still bring all values.
What I wanted is if the user leave all parameters empty , bring all values. If any one or two or three is entered, bring that values only.
How can build a logic for that. sounds like simple , But Not coming to my brain. Please help
Try the below
cust_trx_type_id IN (:P_TYPE1, :P_TYPE2, :P_TYPE3, :P_TYPE4, :P_TYPE5)
OR cust_trx_type_id = NVL(COALESCE(:P_TYPE1, :P_TYPE2, :P_TYPE3, :P_TYPE4, :P_TYPE5),cust_trx_type_id)