How To Use Lookups To The Other Tables In Oracle Apps

Navigation:Responsibility:Application Developer -> Application -> lookups
Lookup screen is available in almost evert superuser responsibility,you can access common lookup screen. Basically records get inserted into FND_LOOKUP_TYPES and FND_LOOKUP_VALUES

Lookup types are list of values.The Value in the list are hard code,but the mechanism is different from the value set of type independent.
The Values are stored in FND_LOOKUP_VALUES,while the values of the independent value set are stored in FND_FLEX_VALUES

===Key Tables===

FND_LOOKUP_TYPES
FND_LOOKUP_TYPES stores Oracle Application Object Library QuickCode types. Each row includes the QuickCode lookup type and the application the lookup type belongs to.
Each row also includes the customization level for the lookup type, the security group the lookup type belongs to, and the application view through with the lookup type will be exposed. You need one row for each QuickCode lookup type.
Oracle Application Object Library uses this information to displayLOVs for Oracle Application Object Library forms and other forms.

FND_LOOKUP_VALUES
FND_LOOKUP_VALUES stores Oracle Application Object Library QuickCode values. Each row includes the QuickCode lookup type, the QuickCode itself, its meaning, and additional description, as well as values that indicate whether this QuickCode is currently valid.

Each row also includes a language code that indicates what language the information is in. You need one row for each QuickCode in each of the languages installed at your site. Oracle Application Object Library uses this information to display LOVs for Oracle Application Object Library forms and other forms.

===Related Views===

FND_LOOKUPS

FND_LOOKUPS is a view of selected columns from the table FND_LOOKUP_VALUES.
This view contains information about the available QuickCodes in the language under which Oracle Applications is currently running at your site.
Oracle Application Object Library uses this view to display information for LOVs.

MFG_LOOKUPS
MFG_LOOKUPS is a view of selected columns from the table FND_LOOKUP_VALUES.
This view contains information about the available QuickCodes for Orace Manufacturing in the language under which Oracle Applications is currently running at your site.
Oracle Applications uses this view to display information for LOVs.

Similarly some lookups views for other module

OE_LOOKUPS,HR_LOOKUPS,WSH_LOOKUPS,AR_LOOKUPS,GL_LOOKUPS......

===SQL Queries===

SELECT *

FROM FND_LOOKUP_TYPES

WHERE LOOKUP_TYPE = \'MTL_TRANSACTION_ACTION\';

SELECT LOOKUP_CODE,MEANING
FROM FND_LOOKUP_VALUES
WHERE LANGUAGE = \'US\'
AND LOOKUP_TYPE = \'MTL_TRANSACTION_ACTION\'
ORDER BY LOOKUP_CODE;

===Lookup FAQ===

source:http://krishnareddyoracleapps.blogspot.jp/2012/07/lookup-types-and-lookup-codes-in-oracle.html

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.

How To Use Lookups To The Other Tables In Oracle Apps

Source: http://blog.itpub.net/26687597/viewspace-1207414/

Posted by: moranaboughtters.blogspot.com

Share this post