Search Results gmi_lots_source_bom_v




Overview
Explore in AI Chat

Overview

GMI_LOTS_SOURCE_BOM_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, registered as a VALID object within the Process Manufacturing Inventory (GMI) product family. The view exists to support the Lot Genealogy Tree Navigator rendered on the Lot Genealogy Inquiry form when the navigation mode is set to "Lot Source." In this mode, the tree expands upward from a subject lot toward the ingredients and lots that were consumed to produce it, rather than downward toward the lots that a lot was subsequently consumed into.

The view therefore acts as the presentation-layer data source for a hierarchical UI control rather than as a transactional or integration-facing object. It is not part of the documented public API surface and should be treated as an internal supporting object for the Lot Genealogy Inquiry, subject to change across point releases. In EBS 12.1.1 and 12.2.2 the object remains present in the APPS schema under the Process Manufacturing (GMI) product, consistent with the ETRM 12.2.2 documentation.

Underlying Base Objects

The documented base objects referenced by GMI_LOTS_SOURCE_BOM_V are:

  • GMI_LOTS_BOM_V (VIEW) — supplies the row set that GMI_LOTS_SOURCE_BOM_V aggregates and decorates.
  • GMI_LOT_TRACE_PKG (PACKAGE) — specifically the function GMI_LOT_TRACE_PKG.HAS_INGRED, invoked in the SELECT list to compute the HAS_CHILD indicator.

The view text is defined as a grouped projection over GMI_LOTS_BOM_V, with the GROUP BY applied across DOC_ID, PRODUCT_ITEM_ID, PRODUCT_LOT_ID, INGRED_ITEM_ID, and INGRED_LOT_ID. This de-duplicates the underlying genealogy rows so that each distinct product-to-ingredient lot relationship appears exactly once in the navigator. The HAS_INGRED function is evaluated against INGRED_ITEM_ID and INGRED_LOT_ID for each grouped row, returning a flag that indicates whether the ingredient lot itself has recorded ingredient consumption — that is, whether the tree node is expandable.

Key Columns

The view exposes the following columns:

  • DOC_ID — the document identifier (batch or production document) that links the product and ingredient lot relationship.
  • PRODUCT_ITEM_ID — the inventory item identifier of the produced (parent) material.
  • PRODUCT_LOT_ID — the lot identifier of the produced material at the node being expanded.
  • INGRED_ITEM_ID — the inventory item identifier of the consumed ingredient.
  • INGRED_LOT_ID — the lot identifier of the consumed ingredient.
  • HAS_CHILD — computed via GMI_LOT_TRACE_PKG.HAS_INGRED; indicates whether the ingredient lot has its own upstream ingredient records and is therefore expandable in the tree navigator.

Common Use Cases and Queries

Primary usage is population of the Lot Source tree on the Lot Genealogy Inquiry. A typical supporting query for a given product lot returns all source ingredients and their expandability status:

  • SELECT doc_id, product_item_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;
  • Restricting by DOC_ID isolates the ingredients attributable to a specific production document.
  • Filtering WHERE has_child = 1 identifies source lots that themselves have upstream ingredients, useful for recursion or for estimating tree depth before expansion.

Because the view is a UI-oriented projection with a packaged function call in its SELECT list, it is not recommended as a high-volume integration source; batch extraction of genealogy is better served from the underlying GMI lot and BOM base tables directly. The view remains a reliable reference for reconstructing the exact row set the Lot Source navigator presents to the user.