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.
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
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.
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
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.
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.
