Oracle Apps : Selecting Multiple values for a parameter in Concurrent Program

We had a requirement to select multiple values from a list when submitting the Concurrent Program.

Few of the options suggested were as below:

1. Create a custom Form and capture the values and then submit the Concurrent Program form a button click .  

• This was ruled out as we didn't want to create a new Form

2. Let the users enter a comma separated values in the parameter field and split the values in the code.

• This was ruled out as there is a probability that the user may enter wrong values

We found a blog where someone suggested some solution with some limitations. 

Selecting Multiple Values in Concurrent Program Paramater

The solution given below is without the limitation mentioned in the above link. The limitation we have is the length of the parameter field (240 characters). I have suggested a workaround for that also below.

Edited: This solution needs to be read along with the below post.

Oracle Apps: How to set Concurrent Program Parameter Disabled / Readonly

The Concurrent Program example given below has 2 Parameters. One is the original list and the second is the selected values from the list.

1. Create a Valueset with the needed values. Make sure that you have an extra value in the list, which will be user to clear the selected list. In my example I have created a valueset with the list of Fruits. Please note, there is a value in the list 'Clear'. Usage of the value 'Clear' is explained below.

2. Create the Executable and the Concurrent Program.

3. Add the original list as the first parameter. This parameter is used only as the selection list. The actual program ignores this parameter. 

    

4. Now is the interesting part of this solution. Create another parameter which will get populated using the values you select from the first parameter. Before creating this parameter, you need to have the package and procedure ready.  This parameter should have the below properties.

Valueset : 

240 Characters

Default Type: 

SQL Statement

Default Value :  

select xxaj_fruit_platter_pkg.get_selected_list(:$FLEX$.XXAJ_FRUIT_LIST) from dual

 

The code for the function get_selected_list is as below :

 FUNCTION get_selected_list(p_fruit VARCHAR2)  
 RETURN VARCHAR2  
 IS  
 BEGIN
   IF p_fruit = 'Clear' THEN
     v_selected_list := NULL;
    
   ELSE 
     --Concatenate the selected value to the existing list  
     SELECT NVL2(v_selected_list,v_selected_list ||',',v_selected_list) || p_fruit   
     INTO v_selected_list   
      FROM dual;  
    END IF;
    
    RETURN v_selected_list;  
  END get_selected_list;  

v_selected_list is a package level variable and everytime when this function gets called, it just keep appending the values in the variable v_selected_list. If the value Clear  selected in the first parameter, the function will clear the package variable and then also remove the values from the second parameter. Package level variable is visible only on the session, so if multiple users try to submit the job at the same time, this will not cause any issues.

The full code of the package is given below.

Now we can see how this works when you try to submit the job.

Select one value 'Apple' from the first parameter.

Select another value 'Orange' from the first paramater.

Now if you really interested in this solution, create a program as above and try to select 'Clear'. Then you can see how that works :)

From the program log, you can see that you have got the comma separated values inside the program. Now you use just PLSQL code to extract the individual values as use it as per your requirement.

Few points which you might be interersted in :

• User can modify the list before submitting. If they want to remove one specific value , they could just modify the value in the second parameter before submitting.
• If you don't want user to modify the second parameter manually, just remove the 'Display' property from the second parameter in the concurrent program definition.
• If the list is too big and the values exceeds 240 Characters, then try to pass a code  with lesser characters instead of the full value and then translate that in the code.
• You could also add a new parameter called, remove list, so that user can select from this list to remove a value selected by mistake.

The package code with the variable declaration is given below:

Package Spec:

 CREATE OR REPLACE PACKAGE xxaj_fruit_platter_pkg AS  
  PROCEDURE create_platter(x_errbuf      OUT VARCHAR2  
                          ,x_retcode     OUT VARCHAR2  
                          ,p_dummy_fruit IN  VARCHAR2  
                          ,p_fruit_list  IN  VARCHAR2);  
  FUNCTION get_selected_list(p_fruit VARCHAR2)  
  RETURN VARCHAR2;  
 END xxaj_fruit_platter_pkg;  

Package Body:

 CREATE OR REPLACE PACKAGE BODY xxaj_fruit_platter_pkg AS  
  --Package level variable which holds the value  
  v_selected_list VARCHAR2(1000);  
  PROCEDURE create_platter (x_errbuf      OUT VARCHAR2  
                           ,x_retcode     OUT VARCHAR2  
                           ,p_dummy_fruit IN  VARCHAR2  
                           ,p_fruit_list  IN  VARCHAR2)  
  IS  
  BEGIN  
   --This will have the last selected fruit. Just ignore it :)  
   fnd_file.put_line(fnd_file.log ,'p_dummy_fruit : '|| p_dummy_fruit);   
   fnd_file.put_line(fnd_file.log ,'p_fruit_list  : '|| p_fruit_list);   
  END create_platter;  
  FUNCTION get_selected_list(p_fruit VARCHAR2)  
  RETURN VARCHAR2  
  IS  
  BEGIN  
    IF p_fruit = 'Clear' THEN  
     v_selected_list := NULL;  
    ELSE   
     --Concatenate the selected value to the existing list  
     SELECT NVL2(v_selected_list,v_selected_list ||',',v_selected_list) || p_fruit   
     INTO v_selected_list   
     FROM dual;  
    END IF;  
      
    RETURN v_selected_list;  
  END get_selected_list;  
 END xxaj_fruit_platter_pkg;  

If there is anything which you think will be an issue in the above solution, feel free to post a comment below.

Feel free to point out if anything is missing/wrong in this blog.