ORACLE APPS R12 -report Parameters WITH MULTI SELECT VALUES 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 Vendors. Please note, there is a value in the list 'Clear'. Usage of the value '0' is explained below.

Case

So we use two input

1-     Select the Vendor

2-     Collect all the selected values

 

 

Solution

1-     Main point is Create DB Pkg as link 

2-     Create Parameters first for all Vendors as any Normal VS we use

Note:- TOKEN VALUE USE ANY CHAR IT JUST TO SHOW FOR THE USER NOT USE IN THE REPORT QUERY

v_selected_list. If the value 0  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.

 

3-     Create the main Parameter of collect the selected vendors (also I used Dummy Parameter’x2’ not use on query or Report for the User and the user can see the selected values) note I faced Duplicated Value in the values so I created a function by regular Expression to Avoid Duplication

240 Characters

Default Type: 

SQL Statement

Default Value : 

Sql Statement = select xxparam_pkg.xxget_no_duplicate(  xxparam_pkg.get_selected_list(:$FLEX$.XXvendorList) ) from dual  

4-     Same previos Paramters only I added the Real Parameter value in Token with update on the

Sql Statmnet = select 'and  APS.segment1 IN  ('||xxparam_pkg.get_selected_list(:$FLEX$.XXvendorList)||')' from dual

And in report I add the Parameter using &P_vendor_id_xx this is the Only Update on The Report  

 

5-     Rdf report ONLY WE UPDATE THE PARAMETERS &P_NAME AS WE SAID

6- REPORT QUERY HERE