1 Reply Latest reply on May 14, 2019 11:15 PM by Vamsi Kasina

    NVL  logic for same parameter more times


      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