Search Results inv_lot_serial_columns_v




Overview
Explore in AI Chat

Overview

INV_LOT_SERIAL_COLUMNS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. Its documented purpose is to expose the descriptive flexfield (DFF) column definitions that apply to lot and serial number records in the Oracle Inventory (INV) module. In practical terms, the view answers the question of which attribute columns exist for lots and serials, what each column is called in the underlying table, and what label the end user sees for it in the application forms. The view is a metadata discovery object rather than a transactional one: it does not store lot or serial attribute values. Instead, it describes the shape of the flexfield so that reports, interfaces, and extensions can discover and label the available attributes dynamically. Because Oracle supplies only a finite set of seeded DFF columns (D_ATTRIBUTE1 through D_ATTRIBUTE10 plus older descriptive columns), the view is particularly useful when a developer or report author searches for a specific attribute such as d_attribute10 and needs to know its end-user name, the flexfield it belongs to, and whether it is enabled.

Underlying Base Objects

The view is defined over three documented objects. FND_DESCR_FLEX_COL_USAGE_VL is the descriptive flexfield column usage view in the Application Object Library (FND) schema. It supplies the application column name, the end-user (prompt) column name, the descriptive flexfield name, the context code, and the enabled flag. The view joins this to FND_TABLES and FND_COLUMNS, which are synonyms referencing the flexfield table and column registries. The definition uses two branches combined with UNION. The first branch selects from FND_DESCR_FLEX_COL_USAGE_VL restricted to APPLICATION_ID 401, the flexfield names 'LOT ATTRIBUTES' and 'SERIAL ATTRIBUTES', ENABLED_FLAG = 'Y', and an explicit list of application column names running from D_ATTRIBUTE1 through D_ATTRIBUTE10. The second branch reads FND_TABLES and FND_COLUMNS to collect any additional columns of column type 'D' on MTL_LOT_NUMBERS and MTL_SERIAL_NUMBERS that are not among the standard audit columns or the ten D_ATTRIBUTE columns. In this way, the view presents both the flexfield-mapped attributes and the remaining descriptive columns present on the lot and serial tables.

Key Columns

  • APPLICATION_COLUMN_NAME — the physical column name in the underlying lot or serial table, for example D_ATTRIBUTE10. This is the value searched for during development.
  • END_USER_COLUMN_NAME — the label or prompt displayed to the end user for that column, as defined in the flexfield setup. For the second UNION branch this reflects the physical column name itself.
  • QUERY_FOR — a derived indicator produced by a DECODE. Rows sourced from 'LOT ATTRIBUTES' or MTL_LOT_NUMBERS resolve to 'LOT', while rows from 'SERIAL ATTRIBUTES' or MTL_SERIAL_NUMBERS resolve to 'SERIAL'. This lets consumers filter the view to a single entity type.
  • DESCRIPTIVE_FLEX_CONTEXT_CODE — the flexfield context under which the attribute is defined. Rows from FND_COLUMNS carry a single space because context is not applicable to the raw column listing.

Common Use Cases and Queries

Typical uses include building attribute-aware lot and serial reports, validating that a requested column such as d_attribute10 is enabled before coding a query against MTL_LOT_NUMBERS or MTL_SERIAL_NUMBERS, and generating dynamic labels for user-facing output. The following examples illustrate these patterns.

  • List every lot attribute available: SELECT application_column_name, end_user_column_name FROM apps.inv_lot_serial_columns_v WHERE query_for = 'LOT' ORDER BY application_column_name;
  • Confirm the definition of a specific column: SELECT application_column_name, end_user_column_name, query_for, descriptive_flex_context_code FROM apps.inv_lot_serial_columns_v WHERE application_column_name = 'D_ATTRIBUTE10';
  • Retrieve serial attributes for a given context: SELECT end_user_column_name FROM apps.inv_lot_serial_columns_v WHERE query_for = 'SERIAL' AND descriptive_flex_context_code = :context_code;

Because the view is a definition-time metadata source, queries against it are inexpensive and can safely be embedded in concurrent programs that must adapt to a customer's flexfield configuration without hardcoding prompts or column availability.