This article illustrates the usage of $FLEX$ and $PROFILE$ with an example.
$FLEX$ is a special bind variable that can be used to base a parameter value on the other parameters (dependent parameters)
Syntax — :$FLEX$.Value_ Set_Name
Value_Set_Name is the name of value set for a prior parameter in the same parameter window that you want your parameter to depend on.
Some scenarios where $FLEX$ can be used:
Example1:
Say you have a concurrent program with the below 2 parameters which are valuesets :
Parameter1 is Deparment
Parameter2 is Employee name
Let’s say there are 100 deparments and each deparment has 200 employees. Therefore we have 2000 employees altogether.
If we display all department names in the valueset of parameter1 and all employee names in parameter2 value set then it might kill lot of performance and also it will be hard for a user to select an employee from the list of 2000 entries.
Better Solution is to let user select the department from the Department Valuset first. Based on the department selected, you can display only the employees in parameter2 that belong to the selected department in parameter1 valueset.
Example2:
Say you have a concurrent program with the below 2 parameters:
parameter1: directory path
parameter2: filename
Parameter1 and parameter2 are dependent on each other. If the user doesn’t enter directory path, there is no point in enabling the parameter2 i.e filename. In such a case, parameter should be disabled.This can be achieved using $FLEX$.
Working Example of how to use $FLEX$:
Let’s take the standard concurrent program “AP Withholding Tax Extract” to explain how to use $FLEX$.
This program has 7 parameters like “Date From”, “Date To”, “Supplier From”, “Supplier To” etc
The requirement is to add an additional parameter called “File Name” where the user will give a name to the flat file where the tax extract will be written to, as a parameter. Instead of typing in the name of the file everytime you run the program, the file name should be defaulted with the value that the user provides for the parameter “Date From” plus “.csv” which is the file extension. Let us now see how this can be achieved using $FLEX$.
Navigation: Application Developer responsibility > Concurrent > Program
Query up the Concurrent
Click “Parameters” Button
Add the parameter “File
- Seq: 80 (Something that is not already assigned to other parameters. It’s always better to enter sequences in multiple of 5 or 10. So that you can insert any additional parameters if you want later in middle)
- Parameter: ‘File Name’
- Description: ‘File Name’
- Value set: ‘240 Characters’
- Prompt: File Name
- Default Type: SQL Statement
- Default Value: Select :$FLEX$.FND_STANDARD_DATE||’.csv’ from dual
Here FND_STANDARD_DATE is the value set name of the parameter “Date From” as seen in the above screenshot.
$FLEX$.FND_STANDARD_DATE gets the value that the user enters for the parameter “Date From”
“select :$FLEX$.FND_STANDARD_DATE||’.csv’ from dual” returns “Date From” parameter value appended with ‘.csv’
Save your work.
Now go to the respective responsibility and run the concurrent program.
When you enter the value of “Date From” and hit tab, File Name parameter will automatically be populated as shown in the below screenshot.
$PROFILES$: This will be used to get the Profile value in the Table Value set or from the front end.
To get Profile values from backend we are using Fnd_Profile.Value or Fnd_Profile.get()
Syntax : :$PROFILES$.Profile Name
SELECT SEGMENT1
FROM PO_HEADERS_ALL
22USER : 204
23USER : 887
24USER : 911
SELECT SEGMENT1
FROM PO_HEADER_ALL
WHERE ORG_ID = :$PROFILES$.ORG_ID
Ex: Display the PO’s which are created by the current User
If 22user ope the LOV it has to display the PO’s which are created by 22 user
SELECT SEGMENT1
FROM PO_HEADERS_ALL
WHERE CREATED_BY = :$PROFILES$.USER_ID