Dynamically enabling and disabling Concurrent Program Parameters

Dynamically enabling and disabling Concurrent Program Parameters

Suppose a concurrent program has three parameters – ParamA, ParamB and ParamC. If the value for ParamA is ‘ENABLE_B’, then ParamB should be enabled and if the value fo ParamA is ‘ENABLE_C’, then ParamC should be enabled. Assume that the values for the second and third parameters are fetched from a table.

The first approach that might come immediatly to mind is to setup the three parameters ParamA, ParamB and ParamC in the manner and link them up using $FLEX$:
ParamA has value set VS1 attached to it. VS1 is of type Independent and has the values ‘ENABLE_B’ and ‘ENABLE_C’.
ParamB has value set VS2 attached to it. VS2 is of type Table and in the Where/Order By clause the condition :$FLEX$.ParamA=’ENABLE_B’ is added.
ParamC has value set VS3 attached to it. VS3 is of tye Table and in the Where/Order By clause the condition :$FLEX$.ParamA=’ENABLE_C’ is added.

When the program is run, both parameters are initially disabled.

But the moment we select a value for the first parameter, ParamA, both ParamB and ParamC get enabled thus defeating our purpose. The only consolation, if it may be so called, is that the list of value for ParamC contains no values.

The correct approach is to use two additional dummy parameters to enable or disable the second and third parameters. We will look into this appoach in more details.
1. ParamA has value set XXSB1_VS1 attached to it. The value set XXSB1_VS1 is of type Independent and contains two values ‘ENABLE_B’ and ‘ENABLE_C’

2. The dummy parameter ParamA1 has a seeded character value set attached to it. Note that the Displayed checkbox is unchecked. Its default value is derived from the SQL statement

1	select decode(:$FLEX$.ParamA,'ENABLE_B','Y', null) from dual

The value for this parameter will be ‘Y’ if ParamA has the value ‘ENABLE_B’ and null otherwise

3. ParamB has value set XXSB1_VS2 attached to it.

4. Value set XXSB1_VS2 is of type Table and in the Where/Order By clause the condition :$FLEX$.ParamA1=’Y’ is added

5. The dummy parameter ParamB1 has a seeded character value set attached to it. Note that the Displayed checkbox is unchecked. Its default value is derived from the SQL statement

1	select decode(:$FLEX$.ParamA,'ENABLE_C','Y', null) from dual

The value for this parameter will be ‘Y’ if ParamA has the value ‘ENABLE_C’ and null otherwise

6. ParamC has value set XXSB1_VS3 attached to it.

7. Value set XXSB1_VS3 is of type Table and in the Where/Order By clause the condition :$FLEX$.ParamB1=’Y’ is added.

That is it, all the parameters have now been set up. When the program is run, the second and third parameters are initially disabled like in the previous approach.

Depending on the value of the first parameter, the second and third parameters are enabled or disabled.

The second approach works while the first does not because the Where/Order By clause for one of the value sets always translates to null=’Y’ which cannot be equated and hence the parameter to which it is attached remains disabled.