Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

Contact Us

Miscellaneous
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

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


Anil Passi

Comments   

0 #1 Hisham 2006-11-22 00:00
Hi anil,
Can you also show (screen shots) how to attach a look up type to a screen?
Quote
0 #2 Anil Passi 2006-11-22 00:00
You can create a value set of type table, and then attach that value set to DFF Segment or a KFF segment.
Alter nately, you need to write a select on Lookup table in the LOV query[when developing a custom screen ]
Quote
0 #3 Hisham 2006-11-22 00:00
Hi anil,
Can you also show (screen shots) how to attach a look up type to a screen?
Quote
0 #4 Anil Passi 2006-11-22 00:00
You can create a value set of type table, and then attach that value set to DFF Segment or a KFF segment.
Alter nately, you need to write a select on Lookup table in the LOV query[when developing a custom screen ]
Quote
0 #5 srinu 2006-12-14 00:00
Its nice & clear examples are given & also suggested to give more depth explanation like when it uses in the organisation setups.

Than ks...a lot...
Quote
0 #6 srinu 2006-12-14 00:00
Its nice & clear examples are given & also suggested to give more depth explanation like when it uses in the organisation setups.

Than ks...a lot...
Quote
0 #7 Sreenivasa Rao Ramuni 2006-12-22 00:00
Hi Anil,
Thanks for giving the solutions. I need some more clarifications for difference between lookup and value set.

Thanks
Sreenivas
Quote
0 #8 Anil Passi 2006-12-22 00:00
Hi Sreenivas

Pl ease let me know what you are confused about?

I will try to explain.

Tha nks,
Anil Passi
Quote
0 #9 Sreenivasa Rao Ramuni 2006-12-22 00:00
Hi Anil,
Thanks for giving the solutions. I need some more clarifications for difference between lookup and value set.

Thanks
Sreenivas
Quote
0 #10 Anil Passi 2006-12-22 00:00
Hi Sreenivas

Pl ease let me know what you are confused about?

I will try to explain.

Tha nks,
Anil Passi
Quote
0 #11 kumar 2007-01-05 00:00
hi anil

thanks providing useful stuff on lookups concept

rega rds
kumar
Quote
0 #12 kumar 2007-01-05 00:00
hi anil

thanks providing useful stuff on lookups concept

rega rds
kumar
Quote
0 #13 anuj 2007-01-19 00:00
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
Quote
0 #14 anuj 2007-01-19 00:00
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
Quote
0 #15 Anuj 2007-01-24 00:00
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
Quote
0 #16 Anuj 2007-01-24 00:00
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
Quote
0 #17 Anil Passi 2007-01-25 00:00
Hi Anuj

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

than ks
anil passi
Quote
0 #18 sudarsan reddy 2007-04-17 00:00
hi anil ji
very interested and easy to learn.
continu e ur support 4 us
thanks
sud arsan
Quote
0 #19 surya 2007-04-24 00:00
what is the difference between lookup codes and quick codes
Quote
0 #20 Anil Passi 2007-04-25 00:00
Hiya

Lookups are mainly stored in fnd_lookup_valu es. 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
Quote
0 #21 Raj 2007-06-26 00:00
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...
Quote
0 #22 Anil Passi 2007-06-26 00:00
Run trace for that screen while you do data entry.
If a hit is made to fnd_lookup_valu es, then it means YES, your table is using lookup.

Anot her way is to reference eTRM

Thanks
Anil
Quote
0 #23 Billu 2007-07-06 03:29
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?

Thank s,
Saurabh
Quote
0 #24 Anil Passi 2007-07-06 06:03
Hi Saurabh

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

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

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

I n other cases, for application specific lookup screens, a WHERE clause similar to "WHERE view_applicatio n_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".
Howev er there form function is passed a parameter similar to "VIEW_APPLICATI ON=PER"
This form function parameter is translated into "WHERE view_applicatio n_id = 800" within the Lookup Screen.

Thanks ,
Anil Passi
Quote
0 #25 saurabh agarwal 2007-08-09 05:00
HI anil can u plz attach a doc related to service request concept
Quote
0 #26 Jobin 2007-08-21 12:59
Hi Anil,
How we implement or use lookup types in apps?
Quote
0 #27 Krishnab 2007-08-28 05:19
In Oracle Apps Some tables ends with TL,VL,what is the significance of the TL and VL?
Quote
0 #28 Krishnab 2007-08-28 05:23
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?
Quote
0 #29 Krishnab 2007-08-28 05:59
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?
Quote
0 #30 Krishnab 2007-08-28 06:02
How you will Diaplay the Company LOGO In report.Can anyone knows please give solution?
Quote
0 #31 soumya 2007-08-28 19:59
hi anil,
where can i find the documentation about lookup types &codes?
plz give the names of the books
Quote
0 #32 CM 2007-09-25 09:38
create and modify lookup by the application is just the same of insert update the APPLSYS.fnd_loo kup_values tables?
Quote
0 #33 sandyhs 2007-09-25 09:40
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?
Quote
0 #34 Anil Passi 2007-09-25 09:44
Being a learner, for all practical purposes, think of both to be the same.

Thanks,
Anil Passi
Quote
0 #35 Sree 2007-09-27 00:35
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. ;)
Quote
0 #36 Girish Tawry 2007-10-04 07:02
Hi Anil,

There are many lookup types defined in the application and all these are stored in fnd_lookup_valu es table and using view_applicatio n_id we can differentiate what type of lookup is that. Is there any way to know which view_applicatio n_id is mapped to which type of Lookup. For example as you said view_applicatio n_id 800 is for Application Utilities Lookup
Quote
0 #37 CoolMagma 2007-11-19 13:42
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
...some one told me I should not just insert into fnd_lookup_valu es, fnd_lookup_type s and fnd_lookup_type s_tl, but I have to use the "API"

so which are the right api for lookup values?
Quote
0 #38 SSB 2007-11-29 05:29
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.
Quote
0 #39 Anil Passi 2007-11-29 05:52
Hi SSB

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

Thanks,
A nil Passi
Quote
0 #40 Vaibhaw K. Bagde 2007-12-19 12:43
Hi Anil,

Can we modify the Lookup code?
Quote
0 #41 AmritaK 2008-01-08 15:28
Hi Anil,
What do you mean by lookup type = table.

Regards ,
Amrita
Quote
0 #42 Sudesh Pawar 2008-01-16 11:06
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_LOOK UPS through frontend in 11.0.3.
Sudesh
Quote
0 #43 shobana.ram 2008-05-27 01:31
How to enter values in attribute1 column in fnd_lookup_valu es using the front end?
Quote
0 #44 Anil Passi- 2008-05-27 02:07
you need to enable the descriptive flexfield against that table.
Quote
0 #45 Anu1 2009-07-14 07:07
Hi Anil,

How do we enable the DFF for the Lookup?

Thanks ,
Anu
Quote
0 #46 tariq 2009-09-02 12:04
I need to create DFF @ the level of lookup screen but i dont wont the DFF appears with all structures !
only i neeed it with my own created DFF;


if u can help i'll be appreciate
Quote
0 #47 Deepak Kumar 2011-09-18 11:05
Anil,

I want to activate attribute1 and attribute2 of a lookup. I will provide LOV for capturing the values in the attributes. The LOVs will populate from existing lookups. How can I do it?

~ Deepak
Quote
0 #48 PradeepGowda 2012-09-26 05:52
Hi Anil,

I have lookup values data in data files , i want to upload this data to fnd_lookup_valu es table programatically ..... could you please let me know is there any API or interface available to import data into fnd_lookup_valu es table

--
Regards
Pr adeep
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Aug 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
        1
  2  3  4  5  6  7  8
  9101112131415
16171819202122
23242526272829
3031     

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner