Introduction

I hope most of you are aware of what is meant by a Lookup in context of Oracle HCM Cloud Application but even if you are not we have got you covered. Hopefully this article (Understanding Lookups In Oracle Fusion Applications ) would give you a basic idea of the same.

One of the major feature of a lookup is that it only allows one unique lookup code and meaning.

What this means is that there can only be one unique value of lookup code can be present in lookup and similarly only one unique lookup meaning can exist.

We can clearly see that if we use either the same lookup code say (‘A-B-C’) or same meaning (‘Y’) for one lookup-type (XX_DEMO_LKP) it throws an error stating:

1. Lookup Type XX_DEMO_LKP already has code A-B-C (FND - 7102)

2. Lookup Type XX_DEMO_LKP already has lookup code meaning Y (FND - 7103)

However, there are many a times when one may wish to have same meaning value. We all know that one may use lookup as a placeholder and in some cases one may wish to hold a specific data combination as eligible (say Y) and others as in-eligible (say N).

Imaging the following data combination

In such cases one may decide to create a independent value set and that would work fine too but lets say for now we would like to have these values stored in a lookup. Now , if we try to create a lookup by using the Data Combination and Eligibility values as Lookup Code and Meaning we would not be able to do so as the meaning cannot be repeated. But there does exists a workaround for the same.

Storing Non-Unique Values in Lookups

The trick here is to make use of “Description” column which does not has any such limitation. The side-effect of using this appraoch could be that one may not be able to use delivered Lookup Functions to get the lookup description value as they have been designed to fetch the lookup meaning when lookup type and code are passed as input, but the advantages are far more rewarding ( for a custom lookup) and one may always use a custom sql (in reports) to fetch the lookup description value ( we will have a look at the custom sql too).

For now lets try to see how can we store non-unique values in a lookup type.

These values when entered into the application would appear as below:

We can clearly see that we can use the same value in description and no error is thrown.

The SQL Code to fetch the description is given below for handy reference.

And when we run the same we can find the results as below:

So this is how we can store non-unique values in a Custom Lookup In Oracle HCM Cloud Application. These concepts would apply for an On-premise enviornment too.

And with this I have come to the end of the article.

I hope this was a good read and would be helpful to you guys.

Thanks all for your time and have a Great Day!

Ashish Harbhajanka

Add comment