Oracle R11/R12 Special Value Set

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.