EBS的lookup在很多地方都有使用。比如说,应用开发者,采购管理,等等。一般的不同的模块使用的都是两张表fnd_lookup_types和fnd_lookup_values,生成不同的视图。例如,po_lookup_types和po_lookup_values。
在lookup中有两张主要的表fnd_lookup_types和fnd_lookup_values。由于EBS的lookup采用了多语言方式,所以与之相关的还有fnd_lookup_types_tl。
Fnd_lookup_types |
Fnd_lookup_values |
外键:fnd_lookup_types.lookup_type |
下面我们来看看应用开发者中的lookup的定义:
Application Developer(应用开发者)-> Application(应用)->Lookups(查询)-> XHU Lookup
如图所示:
然后进入到XHU Lookups职责,进行sql查询:
SELECT LOOKUP_TYPE,
MEANING,
DESCRIPTION,
CUSTOMIZATION_LEVEL,
VIEW_APPLICATION_ID,
SECURITY_GROUP_ID,
APPLICATION_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ROW_ID
FROM FND_LOOKUP_TYPES_VL
WHERE VIEW_APPLICATION_ID = 201
AND APPLICATION_ID = NVL('', APPLICATION_ID)
AND LOOKUP_TYPE = NVL('', LOOKUP_TYPE)
AND (SECURITY_GROUP_ID = 0)
ORDER BY LOOKUP_TYPE
SELECT LOOKUP_CODE,
MEANING,
DESCRIPTION,
TAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ENABLED_FLAG,
LOOKUP_TYPE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
VIEW_APPLICATION_ID,
SECURITY_GROUP_ID,
TERRITORY_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ROW_ID
FROM FND_LOOKUP_VALUES_VL
WHERE (NVL('', TERRITORY_CODE) = TERRITORY_CODE OR TERRITORY_CODE IS NULL)
AND LOOKUP_TYPE = 'ACCRUAL TYPE'
AND (LOOKUP_TYPE = 'ACCRUAL TYPE')
AND (VIEW_APPLICATION_ID = 201)
AND (SECURITY_GROUP_ID = 0)
ORDER BY LOOKUP_CODE
在上面的界面中有两个Block:Lookups Block和Lookups Values Block。
Lookups Block
The Lookups block contains the following fields:
Query the type of your Lookup. You can define a maximum of 250 Lookups for a single type.
The user name is used by loader programs.
Query the application associated with your Lookup type.
If you use windows specialized for a particular Lookup type, the window uses this description in the window title.
The access level restricts changes that are possible to a lookup type. The possible levels are:
System - No changes to the lookup codes are allowed.
Extensible - New lookup codes can be added. However, you cannot modify seeded lookup codes.
User - You can change any lookup code.
This field is for HRMS security only. Refer to the HRMS implementation documentation for more information.
Only the security group of the current form session can be maintained using this form. Standard is the standard default security group. Custom indicates this lookup type is for a custom security group.
All global and cross-security group maintenance is done using the Generic Loader with the Lookups configuration file.
Lookups Values Block
If you would like to query records by a code attribute, such as the Enabled check box, then do the following:
1. Query the lookup type.
2. Move the cursor to any field in the lower region on the window.
3. From the View menu, select Query By Example, Enter.
4. Enter your query criteria.
5. From the View Menu, select Query By Example, Run.
Code
Enter the code value for your Lookup. You can define a maximum of 250 Lookups for a single Lookup type. When you enter a valid Lookup meaning into a displayed window field, Lookups stores this code into a corresponding hidden field. For example, the Lookup "Y" displays the meaning "Yes" but stores the code value "Y" in a hidden field.
You cannot change the values in this field after committing them. To remove an obsolete Lookup you can either disable the code, enter an end date, or change the meaning and description to match a replacement code.
Meaning
When you enter a valid Lookup meaning into a displayed window field, Lookups stores the corresponding code into a hidden field. Lookups automatically displays the meaning in your Lookups field whenever you query your window. For example, the Lookup "Y" displays the meaning "Yes" but stores the code value "Y" in a hidden field.
Description
You can display the description along with the meaning to give more information about your Lookup.
Tag
Optionally enter in a tag to describe your lookup. The tag can be used to categorize lookup values.
Effective Dates
Enter the dates between which this Lookup becomes active. If you do not enter a start date, your Lookup is valid immediately.
Once a Lookup expires, users cannot insert additional records using the Lookup, but can query records that already use the Lookup. If you do not enter an end date, your Lookup is valid indefinitely.
Enabled
Indicate whether applications can use your Lookup. If you enter No, users cannot insert additional records using your Lookup, but can query records that already use this Lookup.
[ ]
The double brackets ([ ]) identify a descriptive flexfield that you can use to add data fields to this window without programming.
接着,我们来看看FND_LOOKUP_TYPES_VL的定义:
CREATE OR REPLACE VIEW FND_LOOKUP_TYPES_VL AS
SELECT B.ROWID ROW_ID,
B.APPLICATION_ID,
B.LOOKUP_TYPE,
B.CUSTOMIZATION_LEVEL,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
B.SECURITY_GROUP_ID,
B.VIEW_APPLICATION_ID,
T.MEANING,
T.DESCRIPTION
FROM FND_LOOKUP_TYPES_TL T, FND_LOOKUP_TYPES B
WHERE B.LOOKUP_TYPE = T.LOOKUP_TYPE
AND B.SECURITY_GROUP_ID = T.SECURITY_GROUP_ID
AND B.VIEW_APPLICATION_ID = T.VIEW_APPLICATION_ID
AND T.LANGUAGE = USERENV('LANG');
FND_LOOKUP_VALUES_VL的定义:
CREATE OR REPLACE VIEW FND_LOOKUP_VALUES_VL AS
SELECT B.ROWID ROW_ID,
B.ATTRIBUTE_CATEGORY,
B.ATTRIBUTE1,
B.ATTRIBUTE2,
B.ATTRIBUTE3,
B.ATTRIBUTE4,
B.ATTRIBUTE5,
B.TERRITORY_CODE,
B.ATTRIBUTE6,
B.ATTRIBUTE7,
B.ATTRIBUTE8,
B.ATTRIBUTE9,
B.ATTRIBUTE10,
B.ATTRIBUTE11,
B.ATTRIBUTE12,
B.ATTRIBUTE13,
B.ATTRIBUTE14,
B.ATTRIBUTE15,
B.LOOKUP_TYPE,
B.LOOKUP_CODE,
B.ENABLED_FLAG,
B.START_DATE_ACTIVE,
B.END_DATE_ACTIVE,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.LAST_UPDATE_DATE,
B.SECURITY_GROUP_ID,
B.VIEW_APPLICATION_ID,
B.TAG,
B.MEANING,
B.DESCRIPTION
FROM FND_LOOKUP_VALUES B
WHERE B.LANGUAGE = USERENV('LANG');