Special Value Sets in Oracle Applications
- by Manoj Madhusoodanan
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 1If the report is through PL/SQL Stored Procedure executable the we can do all the validation in backend.
Approach 2Second 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 1Name : XXCUST_CURRENT_MONTHList Type : List of ValuesFormat Type : CharMaximum Size : 8Validation Type : SpecialEvent : ValidateFunction : XXCUST_CURRENT_MONTH_VALIDATE_ROUTINEValue Set 2Name : XXCUST_DATESList Type : List of ValuesFormat Type : Standard DateValidation Type : SpecialEvent : ValidateFunction : 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.
OutputGive Current Date as 01-2012
Give Schedule Start Date out of current month.
