Search Results item_desc1




Overview
Explore in AI Chat

Overview

APPS.GMI_LOTS_DEST_LIST_V is a reporting view in the Oracle E-Business Suite Process Manufacturing (OPM/GMI) schema. It exposes a de-duplicated, grouped listing of lot and sublot inventory records together with the descriptive text of the associated item. The "DEST" element of the name refers to a destination or distribution-oriented listing used by lot destination and lot traceability screens; the view is designed to present each combination of item, lot, sublot, organization, and quality grade as a single, distinct row.

The view is defined over the base view GMI_LOTS_LIST_V and applies a GROUP BY across all projected columns, which collapses duplicate rows that arise when the source listing returns the same lot from more than one join path. It also invokes the packaged function GMI_LOT_TRACE_PKG.HAS_PRODUCT to compute a derived indicator column, HAS_CHILD, that signals whether a given lot has downstream product or child records. This makes the view a convenient single source for lot selection windows, LOVs, traceability inquiries, and ad-hoc reporting where both descriptive and genealogical context are required. The user search term "item_desc1" corresponds directly to one of the view's exposed columns, which is the primary reason this object is surfaced for that query.

Underlying Base Objects

The view is documented with two referenced base objects:

  • GMI_LOTS_LIST_V (VIEW) — The immediate source of all data columns. Every attribute in GMI_LOTS_DEST_LIST_V (item number, description, identifiers, lot numbers, creation date, grade, organization, and vendor) is projected from this listing view, and it supplies the FROM clause of the definition.
  • GMI_LOT_TRACE_PKG (PACKAGE) — Provides the stored function HAS_PRODUCT(item_id, lot_id), invoked in the SELECT list to derive the HAS_CHILD column. This package encapsulates the lot genealogy logic used throughout OPM traceability.

Because the definition is a projection-and-group over GMI_LOTS_LIST_V rather than a direct join of base tables, the view sits one abstraction layer above the underlying lot and item tables. This design isolates consumers from the join complexity of the lower-level listing view while adding only the traceability flag and the row-collapsing GROUP BY.

Key Columns

  • ITEM_NO — The item number (name) of the lot's parent item, used as the human-readable item identifier.
  • ITEM_DESC1 — The primary description of the item. This is the column referenced by the "item_desc1" search and is typically the field displayed in lot selection lists.
  • ITEM_ID — The internal surrogate key for the item; the join key for other OPM item-related entities.
  • LOT_ID — The internal identifier of the lot; used together with ITEM_ID when calling traceability functions.
  • LOT_NO / SUBLOT_NO — The user-visible lot number and its sublot qualifier, forming the actual inventory lot identifier.
  • CREATION_DATE — The date the lot record was created, commonly used for aging and cutoff analysis.
  • QC_GRADE — The assigned quality control grade of the lot.
  • ORGN_CODE — The inventory organization code to which the lot belongs.
  • VENDOR_NO — The vendor number associated with the lot, relevant for supplier and receipt traceability.
  • HAS_CHILD — A derived flag returned by GMI_LOT_TRACE_PKG.HAS_PRODUCT, indicating whether the lot has product or child records in the traceability hierarchy.

Common Use Cases and Queries

Typical uses include populating lot LOVs that require item descriptions, driving lot traceability inquiries that highlight lots with downstream product, and supporting quality reporting by grade and organization.

To locate lots by item description:

  • SELECT item_no, item_desc1, lot_no, sublot_no, orgn_code, qc_grade FROM apps.gmi_lots_dest_list_v WHERE item_desc1 LIKE 'Aspirin%';

To retrieve lots flagged as having downstream product in a specific organization:

  • SELECT item_no, lot_no, sublot_no, has_child FROM apps.gmi_lots_dest_list_v WHERE orgn_code = 'M1' AND has_child = 1;

To review lots by creation date and grade for quality assessment:

  • SELECT item_no, item_desc1, lot_no, qc_grade, creation_date FROM apps.gmi_lots_dest_list_v WHERE creation_date >= SYSDATE - 90 ORDER BY creation_date DESC;

Because HAS_CHILD is computed through a packaged function, queries returning large lot populations incur per-row PL/SQL execution cost; filtering first on indexed columns such as ITEM_ID or LOT_ID before projecting HAS_CHILD is advisable in high-volume reporting.