Step-by-Step: Enabling/disabling concurrent program parameters dyncamically
Find out how to enable/disable concurrent program parameters conditionally/dynamically.
by Arun Sista
This
article is a simple step by step guide on controlling concurrent
program parameters by enabling/disabling them based on values in other
parameters. This article is for use of anyone who wishes to develop a
new concurrent program/modify an existing one and wants to introduce new
behavior to concurrent program parameters.
Background / Overview
I
decided to write this based on some of my experience at Oracle where I
had to research this fairly simple programming technique and given the
lack of documentation ended up spending a fair bit of time on it
initially.
This article contains a detailed explanation in text as well as an audio visual with a demo on the topic.
Scenario:
Let us assume that we are trying to create a concurrent programX_CUSTOM_TEST_CONC_PROGRAM that has 3 parameters.
The
first parameter is called Requisition Type and the values are
controlled by a pre-defined value set by the name
PO_SRS_REQUISITION_TYPE. The values in this parameter can be
INTERNAL/PURCHASE.
The
second parameter is called Inventory Organization and is dependent on
Requisition Type and would be enabled if the value in Requisition Type
is ‘INTERNAL’
The
third parameter is called Supplier and is dependent on Requisition Type
and would be enabled if the value of the Requisition Type is ‘PURCHASE’
Step 1: Preliminary Setup/Knowledge
Understanding
on creation of concurrent programs and concurrent program parameters
and value sets for concurrent program parameters is expected.
Concept:
We
would use dependent value sets to enable / disable parameters. The idea
is simple and straight forward. You can have a value set “A” that drives a parameter. And another value set “B” that drives the second parameter. If I want to introduce a condition such that “B” does not get enabled till I enter a value in “A”I would have to make “B” as a dependent value set on “A”.
There are two ways to do this.
a.For table based value sets you need to put in a condition in the where clause that has a reference to the driving value set.i.e in our case if B is a table based value set it would need to reference A in the where clause in the following manner: and :$FLEX$.A = ‘<some value>’.This
would ensure that the value set of B does not get initialized till the
value set for A returns a value this in turn keeps the parameter for B
disabled.
b.You can create a normal dependent value sets and define dependencies on other value sets.
So how would we approach our problem where we want to enable/disable values based on another parameter.
a.I would define a parameter that provides the driving value(this creates the dependency). This is driven by value set “A”
b.I
would create a dummy parameter based on a value set that returns Y or
Null. I would default the value in this dummy parameter such that it
returns Y for the positive condition and null for the negative
condition.
c.I
would create the third parameter which is the dependent parameter and I
would insert a clause such in it’s value set that it is dependent on
the dummy parameter. What happens because of this is thatthe dummy parameter returns ‘Y’
when the condition matches thus enabling the value set associated with
the dummy parameter and in turn enabling the value set for this
dependent parameter. However if the condition fails the dummy parameter
returns null thus disabling the value set and parameter for the
dependent parameter
Concepts
Step 2: Create value sets
Create
value sets for all the parameters including the dummy parameters. In
our case we would create the following value sets in this given order to
take care of dependencies:
PO_SRS_REQUISITION_TYPE – Value set for the Requisition type
XX_CUSTOM_ENABLE_SUPPLIER - Value set to enable/disable the Supplier parameter.
Enter the possible values for the value set.
XX_CUSTOM_ENABLE_INV_ORG – Value set to control enabling/disabling the Inv org parameter
Enter the possible values for the value set.
XX_CUSTOM_INV_ORGANIZATIONS – Value set for the Inventory Organization parameter
XX_CUSTOM_VENDOR_LIST – Value set for Vendor parameter
Step 3: We would define the concurrent program itself. And add parameters to it with default values.
Create the concurrent program by the name XX_CUSTOM_TEST_CONC_PROGRAM
Define the concurrent program parameters:
Creat the parameter for Requisition Type and enter the Value Set as : PO_SRS_REQUISITION_TYPE
Create a dummy parameter Inv Org enable with value setXX_CUSTOM_ENABLE_INV_ORG. Set the default type to SQL Statement and set the default value toselect decode(:$FLEX$. PO_SRS_REQUISITION_TYPE,'INTERNAL',’Y’,NULL) from dual. Set the displayed value to false by un checking the check box.
Create a dummy parameter Supplier Enable with value set XX_CUSTOM_ENABLE_SUPPLIER. Set the default type to SQLStatement and set the default value toselect decode(:$FLEX$. PO_SRS_REQUISITION_TYPE,'PURCHASE',’Y’,NULL) from dual. Set the displayed value to false by un checking the check box.
Create a parameter for Inventory Organization and assign the value set XX_CUSTOM_INV_ORGANIZATIONS
Create a parameter for Supplier and assign the value set XX_CUSTOM_ENABLE_SUPPLIER.
Add concurrent program to request group and run:
Switch to the sysadmin responsibility and add your concurrent program to the All Reports request group for purchasing.
Navigate to Purchasing Responsibility and run the concurrent program and see the results for yourself.