Goal
How to use Special Value Sets in Oracle Applications
Applies To
Oracle eBusiness Suite (EBS) R11/R12
Solution
Use case:
For a given Concurrent Program, user enters an Employee Name followed by some Process Date. Restrict user to enter invalid process date. The process date should be between employees’ joining date and Current Date.
Solution Steps:
1. Create Value Set#1
a. Value Set
i. Value Set Name: Employee_list
ii. Maximum Size: 100
iii. Validation type: Table
iv. Keep all other default values as it is.
b. Click Edit Information
i. Table Name: PER_PEOPLE_X
ii. Value: FULL_NAME
iii. Type: Varchar2 Size: 100
iv. ID: PERSON_ID
v. Type: Number Size: 100
2. Create value Set#2:
a. Value Set
i. Value Set Name: Validate Process Date
ii. Format Type Standard Date
iii. Maximum Size: 11
iv. Validation type: Special
v. Keep all other default values
b. Click Edit Information
i. Event : Validate
ii. Function:
FND PLSQL
"
Declare
l_temp number;
begin
select
distinct '1' into l_temp
from per_people_x
where person_id=:$FLEX$.Employee_List
and :!VALUE between start_date and trunc(sysdate);
exception
when no_data_found then
fnd_message.set_name('XXADFD_GEN_MSG','Invalid Process Date');
fnd_message.raise_error;
end;
"
3. Register Concurrent Program
a. Register a dummy Concurrent Program. And add the following two parameters.
i. Parameter#1
1. Seq: 10
2. Parameter: Employee Name
3. Value Set: Employee_List
4. Check the Required field
5. Display Size: 30
ii. Parameter#2
1. Seq: 20
2. Parameter: Process Date
3. Value Set: Validate Process Date
4. Check the Required field
iii. Save and ignore any warnings
Finally run your Concurrent Program and Test different values for the Process Date.
