Skip to content

Green

color

Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color
Home arrow Technical Articles arrow Miscellaneous Technology arrow Lookup Types and Lookup codes in Oracle Apps
This website has now moved to http://apps2fusion.com





Lookup Types and Lookup codes in Oracle Apps | Print |  E-mail
Written by Anil Passi   
Thursday, 26 October 2006
An article on Lookups, for beginners that follow http://getappstraining.blogspot.com
Question : What is a lookup in Oracle Apps
Answer: It is a set of codes and their meanings.

Question: Any examples?
Answer: The simplest example is say a lookup type of Gender.
This will have definitions as below
Code    Meaning
------        -------------
M            Male
F             Female
U            Unknown

Question: But where is it used, any examples of its usages?
Answer: Let us say that there is a table for employees, and this table named PER_PEOPLE_F & has following columns
----
FIRST_NAME
LAST_NAME
DATE_OF_BIRTH
GENDER

Question: Will the gender column in above table hold the value of M or F or U?
Answer: Correct, and the screen that displays people details will in reality display the meaning of those respective codes (i.e. Male, Female, Unknown etc) instead of displaying the code of M or F or U

Question: hmmm...so are lookups used to save the bytes space on database machine?
Answer: Noooo. Imagine a situation as below
a. There are 30,000 records in people table of which 2000 records have gender value = U. In the screen, their Gender is being displayed as "Unknown". Now let’s say you want this to be changed to "Undisclosed". To implement this change, all you have to do is to change the meaning of the lookup codes for lookup type GENDER. Hence it will look like
Code    Meaning
------    -------------
M         Male
F          Female
U         Undisclosed

Here lies the beauty of lookups, you do not need to modify 2000 odd records in this case.

Question : Any other usage of lookups?
Answer : Sure, lets take another example. In HRMS, there is a field named Ethnicity. By default Oracle ERO delivers the below values
Lookup code        lookup meaning
----------------        ---------------------
AS            Asian
EU            European

Now, if your client wants to track Ethnicity at a granular level, they can amend the Oracle delivered lookup definition as below

Lookup code        lookup meaning
----------------        ---------------------
ASI            Asian-Indian
ASP            Asian-Pakistani
EU            European

Hence these values will then be available in the list of values for Ethnicity field.

Question: Are we saying that all the lookups delivered by oracle can be modified?
Answer: Depends. If oracle has a lookup called termination status, and if based on the termination status code Oracle has some rules defined within Payroll Engine....!! Surely Oracle  Payroll Engine will not like it if you end date an existing status code or add a new status code to termination. For this very reason, Oracle flags some lookups as System lookups, and the lookup entry screen will not let you modify those lookup codes.

Question: OK, what if I do not wish to modify existing Lookup codes, but only wish to add new Lookup codes to an existing Oracle delivered Lookup Type?
Answer: You can do so, provided the Oracle delivered Lookup Type is flagged as Extensible. Please see the screenshot

Question: Can we add our own new lookup types?
Answer: Yes you can, for this you will first define a lookup type and will then define a set of lookup codes against the Lookup Type. In our example above, GENDER is the LOOKUP_TYPE

Question: Does a LOOKUP_TYPE get attached to a Descriptive Flexfield…just like Value Sets?
Answer: Not really. There is no direct relation between lookup and Descriptive Flexfield.


Now, the screenshots. Click on the menu as below to invoke Lookup Screen.
Image

Once in the screen, you can define your lookup type and lookup codes as below.
Image
Comments (42)add
...
written by Hisham , November 22, 2006
Hi anil,
Can you also show (screen shots) how to attach a look up type to a screen?
...
written by Anil Passi , November 22, 2006
You can create a value set of type table, and then attach that value set to DFF Segment or a KFF segment.
Alternately, you need to write a select on Lookup table in the LOV query[when developing a custom screen ]
...
written by Hisham , November 22, 2006
Hi anil,
Can you also show (screen shots) how to attach a look up type to a screen?
...
written by Anil Passi , November 22, 2006
You can create a value set of type table, and then attach that value set to DFF Segment or a KFF segment.
Alternately, you need to write a select on Lookup table in the LOV query[when developing a custom screen ]
...
written by srinu , December 14, 2006
Its nice & clear examples are given & also suggested to give more depth explanation like when it uses in the organisation setups.

Thanks...a lot...
...
written by srinu , December 14, 2006
Its nice & clear examples are given & also suggested to give more depth explanation like when it uses in the organisation setups.

Thanks...a lot...
...
written by Sreenivasa Rao Ramuni , December 22, 2006
Hi Anil,
Thanks for giving the solutions. I need some more clarifications for difference between lookup and value set.

Thanks
Sreenivas
...
written by Anil Passi , December 22, 2006
Hi Sreenivas

Please let me know what you are confused about?

I will try to explain.

Thanks,
Anil Passi
...
written by Sreenivasa Rao Ramuni , December 22, 2006
Hi Anil,
Thanks for giving the solutions. I need some more clarifications for difference between lookup and value set.

Thanks
Sreenivas
...
written by Anil Passi , December 22, 2006
Hi Sreenivas

Please let me know what you are confused about?

I will try to explain.

Thanks,
Anil Passi
...
written by kumar , January 05, 2007
hi anil

thanks providing useful stuff on lookups concept

regards
kumar
...
written by kumar , January 05, 2007
hi anil

thanks providing useful stuff on lookups concept

regards
kumar
...
written by anuj , January 19, 2007
Hi,

I am new to apps but whenever i see something on your Site that things becomes easy to understand. Keep it up man.

Regards,

Anuj
...
written by anuj , January 19, 2007
Hi,

I am new to apps but whenever i see something on your Site that things becomes easy to understand. Keep it up man.

Regards,

Anuj
...
written by Anuj , January 24, 2007
Hi,
I am working on a 6 i Report which I have created and now I want to use the lookup codes which will be maintained by the user.
The look up code will have values which are there in the report also.
We want that these lookup codes act as parameter for the report.
Can we do that and how?

Thanks
Regards,
anuj
...
written by Anuj , January 24, 2007
Hi,
I am working on a 6 i Report which I have created and now I want to use the lookup codes which will be maintained by the user.
The look up code will have values which are there in the report also.
We want that these lookup codes act as parameter for the report.
Can we do that and how?

Thanks
Regards,
anuj
...
written by Anil Passi , January 25, 2007
Hi Anuj

you need to creaet a Value Set of Type Table, that uses fnd_lookup_values/your lookup table. The value set will get attached to the parameter of report.

thanks
anil passi
...
written by sudarsan reddy , April 17, 2007
hi anil ji
very interested and easy to learn.
continue ur support 4 us
thanks
sudarsan
...
written by surya , April 24, 2007
what is the difference between lookup codes and quick codes
...
written by Anil Passi , April 25, 2007
Hiya

Lookups are mainly stored in fnd_lookup_values. Some people call a quickcode to be something which helps in quick data entry, via a list of values.

If a lookup is being used to facilitate entries in LOV, then both mean the same.

You can use lookup codes for validation purposes too[not just LOV]. But quickcodes mainly means LOV.

Thanks,
Anil Passi
...
written by Raj , June 26, 2007
How can i check whether a table column is based on a lookup? i mean is there a way to know which lookup type a table column is linked to...
...
written by Anil Passi , June 26, 2007
Run trace for that screen while you do data entry.
If a hit is made to fnd_lookup_values, then it means YES, your table is using lookup.

Another way is to reference eTRM

Thanks
Anil
...
written by Billu , July 06, 2007
Hi Anil,

I just need a confirmation.We can define lookups at 2 level fnd and application like hr.
Is that fnd lookups are availables to all applications and if you want lookup specific to your application you go for application lookup like hr lookups. So that other application users can not modify any values for that.

Just need a confimation Is this correct?

Thanks,
Saurabh
...
written by Anil Passi , July 06, 2007
Hi Saurabh

Even the application specific lookup codes are stored in table fnd_lookup_values itself.

The application specific lookups views[like hr_lookups] are database views created on top of fnd_lookup_values itself.

If the Title of the Lookup screen is "Application Utilities Lookup" then it will let you query lookups accross all aplications.

In other cases, for application specific lookup screens, a WHERE clause similar to "WHERE view_application_id = 800" will be applied, and hence the data will be secured

This too is configurable.
All Application Specific Lookup screens use the same form i.e. "Define Lookups".
However there form function is passed a parameter similar to "VIEW_APPLICATION=PER"
This form function parameter is translated into "WHERE view_application_id = 800" within the Lookup Screen.

Thanks,
Anil Passi
...
written by saurabh agarwal , August 09, 2007
HI anil can u plz attach a doc related to service request concept
...
written by Jobin , August 21, 2007
Hi Anil,
How we implement or use lookup types in apps?
...
written by Krishnab , August 28, 2007
In Oracle Apps Some tables ends with TL,VL,what is the significance of the TL and VL?
...
written by Krishnab , August 28, 2007
What is the Use of User Exit in Oracle apps and when we are developeing the new report you need to pass one parameter called P_CONC_REQUEST_ID,what is the use of this parameter and is it mandatory.Can't we develop report without this User exit's and P_CONC_REQUEST_ID parameter?
...
written by Krishnab , August 28, 2007
What is the Use of User Exit in Oracle apps and when we are developeing the new report you need to pass one parameter called P_CONC_REQUEST_ID,what is the use of this parameter and is it mandatory?can't we develop report without this User exit's and P_CONC_REQUEST_ID parameter?
...
written by Krishnab , August 28, 2007
How you will Diaplay the Company LOGO In report.Can anyone knows please give solution?
...
written by soumya , August 28, 2007
hi anil,
where can i find the documentation about lookup types &codes?
plz give the names of the books
...
written by CM , September 25, 2007
create and modify lookup by the application is just the same of insert update the APPLSYS.fnd_lookup_values tables?
...
written by sandyhs , September 25, 2007
Hi Anil,
What is the difference between AOL lookups and Common lookups? I am still at a point when I am learning Oracle Apps on dummy applications. So, under what circumstances do I need to create what type?
...
written by Anil Passi , September 25, 2007
Being a learner, for all practical purposes, think of both to be the same.

Thanks,
Anil Passi
...
written by Pavan , September 27, 2007
Hi Anil,

I am amazed by your suggestions and solutions.These articles really helps lot of people to make them fast in developing solutions in their day to day work. Appreciate your help, keep it up. Kudos to you.

- Pavan. smilies/wink.gif
FND Lookups
written by Girish Tawry , October 04, 2007
Hi Anil,

There are many lookup types defined in the application and all these are stored in fnd_lookup_values table and using view_application_id we can differentiate what type of lookup is that. Is there any way to know which view_application_id is mapped to which type of Lookup. For example as you said view_application_id 800 is for Application Utilities Lookup
...
written by CoolMagma , November 19, 2007
My SelfService apps works fine in dev. env. but now I got to go on line and i need to deploy an incredible number of lookups
...someone told me I should not just insert into fnd_lookup_values, fnd_lookup_types and fnd_lookup_types_tl, but I have to use the "API"

so which are the right api for lookup values?
Look up Values
written by SSB , November 29, 2007
Hi Anil,
How to get the name of the already defined Lookup type so that it can be queried in the lookup window and look up values can be added to it.

...
written by Anil Passi , November 29, 2007
Hi SSB

Simply run SQL*Trace with bind variable option.
In the trace file, you will find the lookup type

Thanks,
Anil Passi
Lookup code
written by Vaibhaw K. Bagde , December 19, 2007
Hi Anil,

Can we modify the Lookup code?
lookup
written by AmritaK , January 08, 2008
Hi Anil,
What do you mean by lookup type = table.

Regards,
Amrita

Lookup option not seen
written by Sudesh Pawar , January 16, 2008
We are working on 11.0.3 version of oracle apps. To add a value in lookups we did not find the option under Application in Application developer responsibility. Can you help us out where can we enter the value in FND_COMMON_LOOKUPS through frontend in 11.0.3.
Sudesh
You must be logged in to a comment. Please register if you do not have an account yet.

busy