Search Results mfg_lookups




Overview
Explore in AI Chat

Overview

MFG_LOOKUPS is an Oracle E-Business Suite 12.1.1 / 12.2.2 view owned by the APPS schema and classified under the Inventory (INV) product family. It is a filtered projection of the FND_LOOKUPS_VALUES table, the core lookup repository used throughout EBS by the Oracle Application Object Library (FND). The view exposes a constrained subset of lookup rows that pertain specifically to manufacturing and inventory lookup types, identified by VIEW_APPLICATION_ID = 700. This filtering isolates the manufacturing application's lookup codes from the thousands of lookup types shared across the entire E-Business Suite, providing a narrower and more performance-friendly data set for inventory and manufacturing reports, concurrent programs, and interface extracts.

Because it resides in APPS and is defined as a view rather than a synonym, developers can query it directly without needing to know the underlying FND physical model. Its columns mirror those of FND_LOOKUP_VALUES, so existing SQL written against the base table can generally be redirected to MFG_LOOKUPS with minimal modification.

Underlying Base Objects

The view is defined over FND_LOOKUP_VALUES, accessed through a synonym in the APPS schema. The defining SQL joins no other tables and applies three filter predicates: LANGUAGE = USERENV('LANG'), ensuring the session language drives the returned MEANING and DESCRIPTION text; VIEW_APPLICATION_ID = 700, restricting rows to manufacturing lookups; and SECURITY_GROUP_ID = 0, excluding rows owned by non-zero security groups so that only globally shared lookups are exposed.

The LOOKUP_CODE column is deliberately converted with TO_NUMBER, which means the view expects numeric lookup codes. This is an important constraint: any lookup type in application 700 that contains alphanumeric codes will raise an ORA-01722 invalid number error when the view is queried. Developers must therefore confirm that the target lookup types use purely numeric codes before relying on this view, or query FND_LOOKUP_VALUES directly when alphanumeric codes are present.

Key Columns

  • LOOKUP_TYPE — The lookup category, identifying which manufacturing list (for example a transaction reason or status list) the row belongs to.
  • LOOKUP_CODE — The stored code value, cast to a numeric type. This is the value typically held in transactional tables.
  • MEANING — The user-facing display text translated into the session language.
  • DESCRIPTION — A longer explanation of the lookup code, also language-dependent.
  • ENABLED_FLAG — Indicates whether the lookup is currently active (Y) or disabled (N). Reports should filter on Y for current-validity lookups.
  • START_DATE_ACTIVE / END_DATE_ACTIVE — The effective date window; a row is valid within this range. END_DATE_ACTIVE may be null for open-ended entries.
  • CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns used for change tracking and auditing.

Common Use Cases and Queries

The view is most often used to decode numeric codes stored on inventory transactions into readable meanings, to populate LOVs in custom forms or OAF pages, and to validate values in interface staging tables before import. A typical query joining a transaction table to obtain the display meaning is:

SELECT t.transaction_id, m.meaning
FROM mtl_material_transactions t, mfg_lookups m
WHERE m.lookup_type = 'MTL_TRANSACTION_TYPE'
AND m.lookup_code = t.transaction_type_id
AND m.enabled_flag = 'Y';

A second frequent pattern retrieves all active values for a given lookup type, commonly used to drive a selection list:

SELECT lookup_code, meaning
FROM mfg_lookups
WHERE lookup_type = 'MTL_DISPOSITION_TYPE'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
AND NVL(end_date_active, SYSDATE)
ORDER BY meaning;

When performance is a concern on large lookup types, filtering on ENABLED_FLAG and the active date range reduces the row set returned. If a code proves to be alphanumeric, the TO_NUMBER conversion will fail and the equivalent query must be issued against FND_LOOKUP_VALUES instead, applying the same VIEW_APPLICATION_ID and SECURITY_GROUP_ID predicates to preserve the manufacturing scope.