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 Date should 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.