Main Menu
| Home |
| Technical Articles |
| Training Articles |
| Receive Email for New Articles |
| Contributors |
| Hands-On-Training |
| Consultancy Services |
Related Items
- Restart or Bounce Apache in Oracle Apps 11i
- XMLImporter in Oracle Applications Framework
- Read Only Schema in Oracle APPS 11i
- Technical Interview Questions in Oracle Apps
- A New Custom Form in Oracle Apps
- Install tools for Oracle Apps Development
- Concurrent Programs in Oracle Apps
- Concurrent manager in Oracle Apps
- Customization of Reports in Oracle Apps
- Audit Trail in Oracle Apps - EBS
- List of Business Events in 11.5.10
- Oracle Technical and Apps Functional Demo
- Training Seminar in Denmark
- Debug Forms in Oracle eBusiness Suite
- Factors effecting SQL Results in Oracle Apps
Home 
Technical Articles Miscellaneous Technology Lookup Types and Lookup codes in Oracle Apps
Technical Articles Miscellaneous Technology Lookup Types and Lookup codes in Oracle Apps Also see here fully verifiable feedbacks/testimonials
| Lookup Types and Lookup codes in Oracle Apps | | Print | |
| 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.  Once in the screen, you can define your lookup type and lookup codes as below.  Comments
(42)
 written by Hisham , November 22, 2006 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
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
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
HI anil can u plz attach a doc related to service request concept
Hi Anil,
How we implement or use lookup types in apps?
In Oracle Apps Some tables ends with TL,VL,what is the significance of the TL and VL?
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?
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?
How you will Diaplay the Company LOGO In report.Can anyone knows please give solution?
hi anil,
where can i find the documentation about lookup types &codes? plz give the names of the books
create and modify lookup by the application is just the same of insert update the APPLSYS.fnd_lookup_values tables?
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?
Being a learner, for all practical purposes, think of both to be the same.
Thanks, Anil Passi
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. 
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
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?
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.
Hi SSB
Simply run SQL*Trace with bind variable option. In the trace file, you will find the lookup type Thanks, Anil Passi
Hi Anil,
Can we modify the Lookup code?
Hi Anil,
What do you mean by lookup type = table. Regards, Amrita
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.
|
Subscribe to this comment's feed
Can you also show (screen shots) how to attach a look up type to a screen?