How to use custom Profile Options Lockups on Oracle Apps R12 Technical
Many a times a PL/SQL program in Oracle apps need to map certain values or retrieve a value based on certain condition. For example, an employee id which has a certain privilege for a responsibility, or to find out whether the responsibility in which the program is running allows for a certain operation, etc.
I have seen many developers disregard the usage of a Lookup or a custom Profile option. A Lookup in Oracle apps can hold a set of values mapped to each other, against which the PL/SQL code can get a corresponding value if it is mapping with a set value.
A custom Profile option gives developers the option of setting different values at various levels of Oracle, like site, application, responsibility, user.
I have seen developers use independent Value sets more often and that might tend to make the program lengthy in certain cases. The advantage of using a Lookup is that the Code column is always unique and it has an index on it. If there is a very big set of values then the PL/SQL will turn out to be faster for a Lookup against and Independent Value Set.
I always suggest developers to use either a Lookup, Value Set or a Profile Option to store or look up values instead of hard coding even though the value required may be just 1 to enhance flexibility of the code.
Create a custom Profile Option
Login to Oracle and go to
Responsibility: Application Developer
Navigation: Profile
Enter the values and save the form. The profile option is created. Now the values need to be entered at various levels, i.e. Site, Application, Responsibility,User.
Switch responsibility to
Responsibility: System Administrator
Navigation: Profile > System
Now you can enter the values at all the required levels.
Creating a Lookup
Responsibility: Application Developer
Navigation: Application > Lookups > Common
Enter the values as required. Keep in mind that Code column has to be unique as the table FND_LOOKUP_VALUES table, that stores the lookup values, has a unique index on this column. Meaning column cannot be null as it is supposed to be mapped with Code column. Description and Tag column can be null.
There are 2 kinds of Lookups, Application Utilities and Common. For custom lookups it is always better to use Common Lookups.
0 comments