$FLEX$ In Oracle Apps 

This article illustrates the usage of $FLEX$ 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.

Value Set ---> Special Value Set in Oracle Apps

Value Set --->  Special Value Set in Oracle Apps

Special value set configuration and uses.
I have observed that many times we need to restrict users to a limited, conditional value entries either in DFF or when submitting concurrent request. Normally we can use dependent value set, but when dynamic or some specific check is required, then special value set is better and only choice. Below I have described all the steps for configuring value set. Assign this value set to DFF or concurrent req. parameter as required.

1. Go to Application Developer --> Application --> Validation --> Set.
2. Create a new value set - Enter value set name, description, List type = List of values.
3. Select Validation Type = Special from left bottom of the screen.
4. Click on Edit information.
5. Select Event = Validate.
6. Now in function, you can write pl/sql code or call any function from database.
7. The logic of validation will be as per requirement.
8. The code syntax will be FND PLSQL " entire function "
9. To read the value which user has entered in DFF or as conc. request paremeter, use lc_in := :!value;
10. To raise error if the enter value is not correct, use fnd_message.raise_error;
11. Use Application message. Display appropiate message.
12. Raising error makes sure that user cannot continue with the invalid value and he will be fored to corret entry.
13. The character size is limited, but by calling database function, one can put complex validation.

-- Below is the code to validate that user can select a date which is 3 months before sysdate. This is a parementer in report in which user should be allowed to see data for 3 months or before, but he cannot see recent quater data.

FND PLSQL "declare

l_value varchar2( 20 ) := :!value ;
l_valid NUMBER := 0 ;
BEGIN 
SELECT (sysdate - to_date(l_value,'dd-mon-yyyy')) 
INTO       l_valid
FROM    dual;
IF (l_valid <= 91) THEN 
fnd_message.set_name( 'FND', 'FND_GENERIC_MESSAGE' ) ;
fnd_message.set_token( 'MESSAGE', 'Date must be atleast 3 calendar months prior to current 
ate' );
fnd_message.raise_error ;
END IF ;
END;
"

Other Example:

Special Value Sets in Oracle Applications

Here I am going to explain Special Value Sets in Oracle Applications.I have a requirement in which I want to execute a BIP report with some parameters.

The first parameter Current Month should allow only MON-YYYY format.Schedule Start Date and Schedule End Dateshould be with in first parameter month.

Approach 1

If the report is through PL/SQL Stored Procedure executable the we can do all the validation in backend.

Approach 2

Second approach is through Special Value Sets.This value set has events like Edit,Load and Validate.We can attach PL/SQL code snippet to each event.Here I am going to attach validation routine to Validate event to validate the user input.Validate event fires when the focus leaves from the item.

Here I am going to create two special value sets ( one for first parameter and another for the second and third parameter).

Value Set 1

Name : XXCUST_CURRENT_MONTH
List Type : List of Values
Format Type : Char
Maximum Size : 8
Validation Type : Special
Event : Validate
Function : XXCUST_CURRENT_MONTH_VALIDATE_ROUTINE

Value Set 2

Name : XXCUST_DATES
List Type : List of Values
Format Type : Standard Date
Validation Type : Special
Event : Validate
Function : XXCUST_DATES_VALIDATE_ROUTINE

Note: Inside the validate routine I am using FND messages.Generate message file also using "FNDMDGEN apps/password 0 Y US XXCUST DB_TO_RUNTIME".

Attach XXCUST_CURRENT_MONTH to first parameter.Also XXCUST_DATES to second and third parameter.

Note: Since the program is using Special Value Sets it can be submit only through Oracle Forms.Submission through OA Framework and PL/SQL APIs are not recommended.

Output

Give Current Date as 01-2012

Give Schedule Start Date out of current month.