Search Results product_item_id




Overview
Explore in AI Chat

Overview

APPS.GMI_LOTS_DEST_BOM_V is a read-only database view in the Oracle E-Business Suite Process Manufacturing (OPM) module, exposed under the APPS schema. Its primary function is to flatten and de-duplicate the parent/child lot-to-lot BOM relationships that are critical to lot genealogy and traceability in regulated manufacturing environments. Specifically, the view takes the raw lot destination-to-BOM relationships from GMI_LOTS_BOM_V and reduces them to a unique combination of document, product item, product lot, ingredient item, and ingredient lot. This supports reporting and integration use cases where consumers need the "who consumed what in which lot" records stripped of the multiplicative duplication that occurs when a formula has multiple component occurrences.

The view is typically consumed by lot traceability tools, disposition/recall reporting, quality investigations, and downstream integrations that push genealogy data to MES or LIMS systems.

Underlying Base Objects

The ETRM documentation for 12.2.2 identifies exactly two referenced base objects:

  • GMI_LOTS_BOM_V (View) — The source of all columns. GMI_LOTS_DEST_BOM_V selects from this view and applies a GROUP BY to produce one row per distinct genealogy tuple.
  • GMI_LOT_TRACE_PKG (Package) — Invoked at runtime in the SELECT list. The function GMI_LOT_TRACE_PKG.HAS_PRODUCT(product_item_id, product_lot_id) is evaluated for each row to return an HAS_CHILD flag indicating whether the given product/lot combination itself has downstream children in the trace hierarchy.

The GROUP BY clause lists DOC_ID, PRODUCT_ITEM_ID, PRODUCT_LOT_ID, INGRED_ITEM_ID, and INGRED_LOT_ID. Note that the function call is not included in the GROUP BY, but because it is determined solely by the two grouping columns PRODUCT_ITEM_ID and PRODUCT_LOT_ID, it is functionally dependent on the grouping key and therefore valid.

Key Columns

  • DOC_ID — Document identifier linking the row to the originating batch, formula, or inventory transaction document.
  • PRODUCT_ITEM_ID — The finished or intermediate product item produced by the document. This is the column users search for when querying "product_item_id".
  • PRODUCT_LOT_ID — The specific lot of the product item generated or associated with the document.
  • INGRED_ITEM_ID — The component (ingredient) item consumed during production.
  • INGRED_LOT_ID — The specific lot of the ingredient item that was consumed.
  • HAS_CHILD — A computed flag (via GMI_LOT_TRACE_PKG.HAS_PRODUCT) indicating whether the product/lot pair has associated child records. This is used for recursive or hierarchical traceability traversal.

Common Use Cases and Queries

Typical scenarios include retrieving all ingredient lots for a given product lot during a recall, confirming that a downstream product lot has children before drilling deeper, and populating genealogy extracts. The PRODUCT_ITEM_ID column is the common entry point for filtering by finished or intermediate item.

SELECT doc_id,
       product_item_id,
       product_lot_id,
       ingred_item_id,
       ingred_lot_id,
       has_child
  FROM apps.gmi_lots_dest_bom_v
 WHERE product_item_id = :p_item_id
   AND product_lot_id  = :p_lot_id;

To find only terminal nodes (lots with no further children):

SELECT doc_id, product_item_id, product_lot_id
  FROM apps.gmi_lots_dest_bom_v
 WHERE has_child = 0;

Because the view performs a GROUP BY over GMI_LOTS_BOM_V, always expect one deduplicated row per (DOC_ID, PRODUCT_ITEM_ID, PRODUCT_LOT_ID, INGRED_ITEM_ID, INGRED_LOT_ID) tuple. Avoid adding columns from the base view that are not present here, as they are not exposed by this view.