Selecting Multiple Values in Concurrent Program Paramater

Requirement:
To have a concurrent program parameter where he can select multiple value from the value set. Solution: Create 2 parameter, first one with the value set which will have the values from which the values need to be selected. The second parameter will be a free text field which will hold the selected values from the first value set.

Steps to achieve the desired solution 
1) Create a value set which will hold the values. Let say the name is “XXTK_MULTIPLE_VALUE_SET”
Explanation This value set will be holding the values from which the user will be selecting the values into the second parameter.

2) Create a concurrent program program and create PARAMETER-1 and assign the above value set to it.

3) Create second parameter and do the following setup
3.1) Value Set: 240 Characters
3.2) Default Type: SQL Statement
3.3) Default Value: SELECT xxoa_multiple_parameter.return_value(:$FLEX$.XXTK_MULTIPLE_VALUE_SET) FROM DUAL

ExplanationThe select statement will take the input as the selected value from the first parameter and will return the concatenated values.

4) In the Default value we have used a function, so now we have to create that function.

CREATE OR REPLACE PACKAGE xxoa_multiple_parameter

AS

g_var VARCHAR2 (3200) := NULL;

g_time DATE;

 

FUNCTION return_value (i_para VARCHAR2)

RETURN VARCHAR2;

END;

/

 

CREATE OR REPLACE PACKAGE BODY xxoa_multiple_parameter

AS

FUNCTION return_value (I_para VARCHAR2)

RETURN VARCHAR2

AS

l_len NUMBER := LENGTH (I_para);

BEGIN

IF g_time IS NULL

THEN

g_time := SYSDATE;

g_var := i_para;

ELSE

IF (((SYSDATE - g_time) * 60 * 60 * 24) > 30)

THEN

g_time := SYSDATE;

g_var := i_para;

ELSE

IF ( NVL (LENGTH (g_var), 0)

- NVL (LENGTH (REPLACE (g_var, i_para, NULL)), 0)

- l_len != 0

)

THEN

IF g_var IS NULL

THEN

g_var := i_para;

ELSE

g_var := i_para || ',' || g_var;

END IF;

ELSE

g_var := REPLACE (g_var, i_para);

END IF;

END IF;

END IF;

 

g_var := REGEXP_REPLACE (REGEXP_REPLACE (g_var, '^,|,$', ''), ',,', ',');

RETURN g_var;

END return_value;

END xxoa_multiple_parameter;

Explanation This function takes the input from the first parameter (Value Set) and initialize the GLOBAL PARAMETER of TIME and parameter. It checks whether the initialize is done before 90 sec or not. If no, then it will concatenate the values and will return the new string of values. If the user wants to remove selected value value then he has to select the value again and the function will remove the value. Also before returning the final string of values the function will remove the comma from starting or at the end of the string.

Explanation The reason for creating 2 parameters are that the first parameter will be holding the parameter from the value set which is of no use. The second parameter is of use as that will hold the selected values.

Steps to Use
1) Select the concurrent program and select the Value from the Value set

After selecting the first value

2) Selecting the second value from the value set

3) Now Select a value to remove it from selected list. Selecting EMP

4) Once the value is selected, that value will be removed from the list

Limitations 
1) After submitting the values, user has to close the form of concurrent program and again has to open it. To Avoid this a time frame of 90 second is given. After 90 seconds the values will be reset to null.