How to Set Parameters Dynamically

1.Introduction

Oracle has provided dependent and independent value set to Enable/Disable a concurrent program parameter at run time. However this seeded feature doesn’t have capability to Enable/Disable parameters based on other parameter value selected by the User.

The aim of this document is to enlist steps to make concurrent program parameters Enabled/Disabled at runtime conditionally based on a user entered value

2.Logic:

When a dependent value set of table type refers an independent value set (of table type) using $FLEX$ and finds the value of the independent value set to be null then it automatically becomes disabled.

This behavior of Oracle Application AOL has been exploited to achieve the desired functionality of enabling /disabling a value set based on the “Value” .

3.Case Studies:

Suppose the business requirement is like – There is three Parameters listed below:

1. Payment Mode

2. Bank

3. Branch

Payment Mode parameter’s value set is having values like –

‘CASH’,’DEBIT CARD’,’CREDIT CARD’,’DEMOND DRAFT’

Now the other two parameters Bank and Branch should not be enabled for the value ‘CASH’ selected by user among the list of value from Payment Mode parameter for other values it should be enabled.

4.How to:

Step 1: Table Scripts: - The three tables used by this example are:

1. CREATE TABLE TEST_PAY_MODE (PAY_MODE VARCHAR2 (10));

INSERT INTO TEST_PAY_MODE VALUES (‘&PAY_MODE’);

2. CREATE TABLE TEST_BANK (NAME VARCHAR2 (40))

INSERT INTO TEST_BANK VALUES (‘&NAME’)

3. CREATE TABLE TEST_BRANCH (NAME VARCHAR2 (40))

INSERT INTO TEST_BRANCH VALUES (‘&NAME’)

Step 2: Creating Value Sets - In this Example we will create four Table type of Value Sets

1. TEST_PAY_MODE

2. TEST_BANK

3. TEST_BRANCH

4. FLAG

Note –

As explained in the logic section here we are using 4^th Value Set named – Flag to achive that goal.

This Value Set will be having the value 1 or NULL.

Value will be NULL If Value Set TEST_PAY_MODE’S value =’CASH’

Value will be 1 If Value Set TEST_PAY_MODE’S value <>’CASH’

In the Table Name of this Value set we put –

(select decode ( :$FLEX$.TEST_PAY_MODE,'CASH','',1 ) x from dual )y

Now we add $FLEX$.FLAG=1 in the where clause of both Value sets (TEST_BANK AND TEST BRANCH)

Step 3: Creating Concurrent Program Parameters –

Here we will create three Parameters with display mode on and one other parameter with display mode off and having default value as below.

The default value of Parameter Named -> Flag will be of type SQL having the following statement

SELECT DECODE (:$FLEX$.TEST_PAY_MODE,'CASH','',1) FROM DUAL

5. Supporting Screenshots:

Value set 1 -> TEST_PAY_MODE

Edit Information ->

Value set 2 -> TEST_FLAG

Edit Information ->

Note: Table Name = (select decode( :$FLEX$.TEST_PAY_MODE,'CASH','',1 ) x from dual )y

Value Set 3 -> TEST_BANK

Edit Information ->

Value Set 4 -> TEST_BRANCH

Edit Information ->

Defining A Concurrent Program ->

Defining Parameters

Note: - the default value of Parameter Named -> Flag = select decode (:$FLEX$.TEST_PAY_MODE,'CASH','',1) FROM DUAL

Submitting the Program

First Look ->

2> user has selected the value as ‘CHEQUE’

Both parameters (Bank and Branch) got enabled.

3> user has selected the value as ‘CASH’

Both parameters (Bank and Branch) got disabled

5. Conclusions:

This is a handy feature and it is having limitation to have Value Set of table type only.

Related Articles to Read