0 Replies Latest reply: Jul 27, 2013 1:24 PM by AndyLou RSS

    Multi Select LOV Query for Apex

    AndyLou

      Hi

       

        I have a requirement to create a multi select LOV that will be used to create the WHERE clause of my report region. The choices in the list must be All, null, list1,list2  or any combination of those values. The list values will come from rows in a system reference table a list for couriers types and a list for agents types. So the lists can be expanded in the future. I am struggling to create the sql to manage all these combination of options in the WHERE clause and also create the select statement for the LOV?

       

       

      I have  rows in  system_reference tables

      insert into system_reference(name,value,description)

      values('list1','U','Unknown');

       

      insert into system_reference(name,value,description)

      values('list2','A||B||C||D||E','Courier Types');

       

      insert into system_reference(name,value,description)

      values('list3','F||G','Agent Types');

       

      Select * from system_reference

      list1  U

      list2  A||B||C||D||E

      list3  F||G

       

      What I want to do is in apex create a multi list LOV where the following items are available to multi select

      Display               Value

      All                      A||B||C||D||E||F||G||U -- a concatenated list

      Courier Types      A||B||C||D||E

      Agent  Types      F||G

      Unknown            U

       

      in the where clause of my report region the LOV values will determine the WHERE clause the options can be

       

      where  ----delivery_channel is (null so do not include in the where clause)

                 ----delivery_channel is in this list of values A||B||C||D||E Courier

                 ----delivery_channel is in this list of values F||G Agent

                 ----delivery_channel equals this list of values U Unknown

                 -- delivery_channel is equal to All concatenate the rows into a single in statement  A||B||C||D||E||F||G||U