Search Results fnd_common_lookups

Overview

OE_FND_COMMON_LOOKUPS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the ONT (Order Management) product family and, per the ETRM documentation, is defined as a view based on FND_COMMON_LOOKUPS. Functionally, the view presents the standard Oracle Application Object Library lookup repository — the codes and translated meanings that drive List of Values (LOVs), flexfield segments, and value validation throughout EBS — in a form that Order Management components and custom integrations can consume against the OE naming convention.

The view carries a STATUS of VALID and is exposed through the APPS schema, which is the standard read-only access point for custom SQL, Discoverer workbooks, BI Publisher data templates, and interface programs. Because it simply projects the FND_COMMON_LOOKUPS contents without transformation, it exists primarily for compatibility and naming consistency with other OE_* views rather than to provide denormalized or Order Management-specific logic. In every practical sense it is a pass-through wrapper over the Foundation lookups table.

Underlying Base Objects

The ETRM metadata documents two referenced objects for this view:

• FND_COMMON_LOOKUPS (VIEW) — the immediate source of all columns in the SELECT list. In Oracle EBS, FND_COMMON_LOOKUPS is itself the multilingual (MLS) view over FND_LOOKUP_TYPES_B, FND_LOOKUP_TYPES_TL, FND_LOOKUPS, and the associated _TL translation tables, resolving language-specific MEANING and DESCRIPTION values based on the session language.
• FND_GLOBAL (PACKAGE) — the FND_GLOBAL.USER_ID and related session-context functions indirectly referenced by the lookups' WHO columns and by the language resolution performed inside FND_COMMON_LOOKUPS.

Because the view does not join any ONT table, it provides no Order Management-specific filtering. Any Order Management context must be applied by the caller, typically using LOOKUP_TYPE values that begin with an OE prefix or that are specific to Order Management functional areas.

Key Columns

The view exposes the set of columns documented in the ETRM view text:

• APPLICATION_ID — the application owning the lookup type; joined to FND_APPLICATION for the application short name.
• LOOKUP_TYPE — the name of the lookup category (for example, an OE_* type used by Order Management).
• LOOKUP_CODE — the internal stored value used in transactional tables.
• MEANING — the translated, user-facing display value.
• DESCRIPTION — the optional longer description.
• ENABLED_FLAG — Y/N indicator of whether the code is active; inactive codes must be excluded from validation logic.
• START_DATE_ACTIVE / END_DATE_ACTIVE — the effective date window for the lookup code.
• CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.

Common Use Cases and Queries

The most frequent use is LOV and validation lookup inside Order Management extensions, where a custom form or OAF page needs the valid values for a given lookup type. The second common use is reporting, where a transactional OE table stores a LOOKUP_CODE and the report must translate it to a MEANING. A third use is data migration, where the view is queried to validate source values before they are loaded into Order Management tables.

Retrieve all enabled codes for a lookup type:

SELECT lookup_code, meaning, description
FROM apps.oe_fnd_common_lookups_v
WHERE lookup_type = :p_lookup_type
AND enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, SYSDATE)
AND NVL(end_date_active, SYSDATE);

Decode a stored code back to its display meaning:

SELECT meaning
FROM apps.oe_fnd_common_lookups_v
WHERE lookup_type = 'OE_ORDER_SOURCE'
AND lookup_code = :p_code;

Join the view to an Order Management transactional table to translate a coded column:

SELECT h.order_number, l.meaning
FROM apps.oe_order_headers_all h,
apps.oe_fnd_common_lookups_v l
WHERE l.lookup_type = 'OE_ORDER_SOURCE'
AND l.lookup_code = h.order_source_id;

Because the view performs no filtering, applications should always constrain LOOKUP_TYPE and apply the enabled flag and active-date predicates to avoid returning obsolete codes.