Introduction

For all the Technical Consultants working with E-Business Suite creating dependent parameters for a Concurrent Program must be a very commonly used feature ( remember using $FLEX$ in the value set definition of one parameter which would fetch value from another parameter on which this parameter would depend) but the same had been a little different till now in Oracle Fusion Cloud Application ( you might still achieve this in an On-Premise deployment using JDeveloper) as neither we have the flexibility of using $FLEX$ in Fusion BIP Data Model nor do we have flexibility of attaching value sets to Fusion ESS jobs but having said so it doesn’t means we have hit a dead end. There is a workaround available where-in we can achieve the same functionality using bind variable feature.

If we use the bind variable (the independent parameter) in the SQL query which is attached to the other parameter (say dependent parameter) we can easily achieve the same.

Let us try to demonstrate the same with a worked example.

Worked Example

For this example, we would create a very simple data model which would fetch data from an Application Base table.

We would not be going through a step-by-step detail on creating a BIP Data Model but concentrate on creation of a dependent parameter here ( for a step-by-step detailed document on how to create a BIP Data Model feel free to refer the link here)

For this example, we will create a data model which would have two parameters:

Parameter 1: Period Type (Independent Parameter)

This may be regarded as the parent parameter (in case you like to say so) which is the independent parameter. We would try to fetch the different ‘Work Relationship’ an individual holds with the organization in this variable. It could hold the following data values:

1. E: Denotes Employee

2. C: Denotes Contingent Worker

3. N: Denotes Non-Worker

4. P: Denotes Pending Worker

This is the second parameter which would list all Person Number depending on the Period Type value selected in the first parameter. Say if Period Type value selected is:

1. E then it will list all Person Numbers which have an Employee Work Relationship

2. C then it will list all Person Numbers which have a Contingent Worker Work Relationship

3. N then it will list all Person Numbers which have a Non-Worker Work Relationship

4. P then it will list all Person Numbers which have a Pending Worker Work Relationship

Parameter Detail

Notice the use of :periodtype bind variable in the SQL query ( this ensures that the PersonNumber parameter is dependent on the value of PersonType parameter)

We would now create a data set based on below details

The screen once populated should appear as:

Running / Executing the Data Model

Once we save the data model, we may run the same by clicking on the ‘Data’ tab

For first run lets select the PeriodType as ‘N’ and we could see that the PersonNumber will only list those PersonNumber who have a Non Worker Work Relationship ( It shows 3364 only)

Before proceeding lets verify the same from the application

As a last step lets us try to change the PeriodType to E and search for the same PersonNumber and we should not be able to search this PersonNumber

Great so we could see that the PersonNumber parameter is dependent on the PersonType parameter and the data which is displayed on the List of Value attached with PersonNumber changes as and when the value of PersonType is changed.

And with this we have reached the end of the article and I hope we were able to learn  how to create dependent parameters in Oracle Fusion Cloud Application. Do try at your end with a different example and let-us know how it goes.

Ashish Harbhajanka

Add comment