Enable/Disable the parameter field Dynamically in Oracle apps Concurrent Program

Enable/Disable the parameter field Dynamically in Oracle apps Concurrent Program

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.

one more Example 

Enable/Disable the parameter field Dynamically

Introduction : This post provides the guidance to the user with the necessary information for creating parameters for a concurrent program and making them conditionally enable/disable.
Steps to be followed :-
1.Create a procedure with two input parameters.
2.Create an executable with execution method as PL/SQL Procedure
  and execution file as the procedure created in step1.
3.Create a concurrent program with executable created in step2.
4.Assign the concurrent program to a request set to run the
  concurrent program from a responsibility.
5.Create two two three values sets.
6.Create three parameters for the concurrent program
  created in step 3 using value sets created in step 5.
7.Go to the responsibility to which the concurrent program assigned in step 4 to run the program..
Installation Steps


Step1 : Create procedure
CREATE OR REPLACE procedure test_proc(
errbuf OUT varchar2,
retcode out NUMBER,
p_One IN VARCHAR2,
p_two_dummy IN VARCHAR2,
p_two IN NUMBER)
is
begin
fnd_file.put_line (fnd_file.LOG,'Log File');
fnd_file.put_line (fnd_file.OUTPUT,'Out put File' || p_one || '   ' || p_two);
end test_proc;
/
Run above procedure in SQL * Plus / Toad.Step2:  Creating Executable:Navigation :- System Administrator Responsibility→ Concurrent →  Program → Executable
Executable : TEST_PROC
Short Name: TEST_PROC
Application: Custom Development
Execution Method: PL/SQL Stored Procedure
Execution File Name : TEST_PROC


Save and close the Concurrent Program executable window.
Step 3: Creation of Values Sets
 3.1 Creating Value set
       Value Set Name  :LAMS_SRS_YES_NO_MAND
       Description     :Yes/No
       List Type       : List Of Values
       Format          : Char
       Security Type   : No Security
       Validation Type : Table
       Edit Information:
            Table Name : FND_LOOKUPS
            Value      : MEANING         TYPE :VARCHAR2
            ID         : LOOKUP_CODE     TYPE :VARCHAR2
            Where/Order By : WHERE Lookup_type = 'YES_NO'  
Click on Test→ Ok → Save and close Validation Table Information window and then value set window.


  3.2 Creating Value set for Dummy Parameter:
      Value Set Name: CST_SRS_MARGIN_ORDER_DUMMY2
      List Type     : List Of Values
      Format        : Char(Check Uppercase Only)
      Security Type : No Security
      Validation Type : None

  3.3 Value set for Sales Order Numbers:      Value Set Name : ONT_ORDER
      List Type   : List Of Values
      Format      : Number(Check Numbers Only)
      Security Type : No Security
      Validation Type : Table
      Edit Information:
        Table Name : OE_ORDER_HEADERS_ALL
        Value   : ORDER_NUMBER           TYPE :NUMBER
        Where/Order By : where :$FLEX$.XXLSS_ORDER_DUMMY = 'Y'  


Click on Test→ Ok → Save and close Validation Table Information window and then value set window.
 Step 4 : Creating a concurrent Program


   Program          : TEST_PROC
   Short Name       : TEST_PROC
   Application      : Custom Development
   Executable  Name : TEST_PROC


Save and click on Parameters.
Step 5: Creation of Parameters
  5.1 Yes/No Parameter
         Seq          : 10
         Parameter    : p_one
         Value Set    : AMS_SRS_YES_NO_MAND
         Enabled      : Yes
         Required     : Yes
         Display      : Yes
  5.2Creating Dummy Parameter
         Seq : 15
         Parameter : p_two_dummy
         Value Set    : CST_SRS_MARGIN_ORDER_DUMMY2
         Default Type : SQL Statement
         Default Value: select decode        (:$FLEX$.XXONT_OSR_YES_NO,'Y','Y','N',NULL) from dual
         Enabled   : Yes
         Required  : No
         Display   : No
  5.3 Creating Conditional Parameter
          Seq : 20
          Parameter :  p_two
          Value Set : ONT_ORDER    
           Enabled   : Yes
           Required  : Yes
            Display   : Yes


Step 6 : Assigning the concurrent program a request group:Navigation:- System Administrator→ Security → Responsibility → Request
Query for the Request group, 'OM Concurrent Programs'
Select Request Type and click on Add New
Type : Program
Name : TEST_PROC
Application : Custom Development
Save and close the Window.
Step7 : Running  the concurrent request:-
Navigation: Order Management Super User→ Reports, Requests → Run Requests  →
  Give Program Name as 'TEST_PROC'
 When you select Yes for the first parameter then second parameter become mandatory.

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.