Search Results product_lot_id




Overview
Explore in AI Chat

Overview

APPS.GMI_LOTS_SOURCE_BOM_V is a read-only database view in the Oracle E-Business Suite Process Manufacturing (OPM) module. It exposes lot-level bill of material (BOM) relationships and, critically, annotates each row with a flag indicating whether the ingredient lot itself has further downstream components. This makes the view a foundational element for lot genealogy, traceability, and recall reporting within Oracle Process Manufacturing.

The view is defined in the APPS schema and documented under ETRM 12.2.2. In Oracle EBS 12.1.1 and 12.2.2, it is used primarily as a data source for backward and forward trace inquiries, quality investigations, and custom genealogy reports. Because it resolves the recursive nature of lot parentage into a single denormalized result set with a HAS_CHILD indicator, it is particularly useful for building hierarchical user interfaces that need to know whether to render an expandable node.

Underlying Base Objects

The view is defined over two documented referenced base objects:

  • GMI_LOTS_BOM_V (VIEW) — Provides the core lot-to-lot BOM relationships, including document, product item, product lot, ingredient item, and ingredient lot identifiers. This is the driving source for the row set.
  • GMI_LOT_TRACE_PKG (PACKAGE) — Supplies the PL/SQL function HAS_INGRED, which is invoked per row to determine whether the ingredient lot participates as a parent in any further BOM relationship. The result is exposed as the HAS_CHILD column.

The SELECT statement groups by DOC_ID, PRODUCT_ITEM_ID, PRODUCT_LOT_ID, INGRED_ITEM_ID, and INGRED_LOT_ID, ensuring a distinct combination of parent and child lots. The HAS_CHILD value is computed at row level by GMI_LOT_TRACE_PKG.HAS_INGRED(INGRED_ITEM_ID, INGRED_LOT_ID). Because a stored PL/SQL function is called within the view definition, the view cannot be used in all read-consistency or distributed query contexts, and performance depends on the efficiency of the package function.

Key Columns

  • DOC_ID — Identifier of the originating document (for example, a batch or production record) that establishes the lot relationship.
  • PRODUCT_ITEM_ID — Inventory item identifier of the parent (produced) item.
  • PRODUCT_LOT_ID — Lot identifier of the parent item, representing the lot produced or assembled.
  • INGRED_ITEM_ID — Inventory item identifier of the ingredient or component consumed by the parent lot.
  • INGRED_LOT_ID — Lot identifier of the ingredient, representing the source lot consumed.
  • HAS_CHILD — Result of GMI_LOT_TRACE_PKG.HAS_INGRED(INGRED_ITEM_ID, INGRED_LOT_ID), indicating whether the ingredient lot has its own component lot records. A non-null/positive value implies the lot can be expanded further in a genealogy tree; a null or zero value identifies a leaf node.

Common Use Cases and Queries

The view is commonly used for lot traceability reporting and for building hierarchical genealogy displays. A typical query retrieves all direct components of a given product lot and flags expandable nodes:

  • Direct component listing: SELECT doc_id, product_lot_id, ingred_item_id, ingred_lot_id, has_child FROM apps.gmi_lots_source_bom_v WHERE product_lot_id = :lot_id;
  • Identifying expandable lots: SELECT ingred_lot_id FROM apps.gmi_lots_source_bom_v WHERE has_child IS NOT NULL AND product_lot_id = :lot_id;
  • Recall/quality trace: Join to item and lot master views to obtain descriptions for a full down-stream or up-stream trace report.

Because HAS_CHILD is evaluated through GMI_LOT_TRACE_PKG, queries against this view should be filtered tightly by lot or document to avoid repeated function invocations across the full table. Organizations typically wrap the view in a custom cursor or report program to render interactive BOM trees in Oracle EBS 12.1.1 and 12.2.2.