Search Results gmi_lot_trace_pkg




Overview
Explore in AI Chat

Overview

GMI_LOT_TRACE_PKG is an Oracle EBS Application Object Library package body owned by the APPS schema. It belongs to the Oracle Process Manufacturing (OPM) product family, specifically the Lot Genealogy and Traceability module. The package supports lot traceability and ingredient/product recall functionality by enabling backward and forward tracing of lot relationships across the manufacturing supply chain.

In the context of Oracle EBS 12.1.1 and 12.2.2, GMI_LOT_TRACE_PKG is classified as OTHER in the ETRM documentation. It is a self-contained procedural package with four documented procedures/functions, and it is referenced by five other database objects, indicating it serves as a utility layer for lot traceability operations invoked by higher-level components.

Key Procedures and Functions

The package exposes four documented procedures and functions:

  • EXP_LOT — An export routine that extracts lot traceability information. Given its name, it likely materializes or returns lot genealogy data for a specified lot, producing the trace output used in recall investigation or regulatory reporting.
  • EXP_LOT1 — A companion or overload variant of EXP_LOT, typically used to handle an alternate tracing direction (for example, upward versus downward genealogy) or a different output format.
  • HAS_INGRED — A boolean-style function that determines whether a given lot has ingredient-level relationships. This is used to short-circuit trace logic when no upstream ingredients exist for a lot.
  • HAS_PRODUCT — A boolean-style function that determines whether a given lot has downstream product relationships. This is used to determine whether forward tracing is required for a given lot.

Parameter lists are not documented; the procedures should be treated as internal APIs whose signatures are not published for customer customization.

Tables Accessed

The package reads and writes the following tables via APPS synonyms:

  • GMI_LOT_TRACE — The core OPM lot traceability table in which genealogy relationships between lots are recorded and queried.
  • IC_ITEM_MST — The item master, used to resolve item attributes and descriptions during trace output.
  • IC_LOTS_MST — The lot master, used to resolve lot numbers, status, and lot attributes.
  • IC_TRAN_CMP — Completed inventory transactions, consulted to reconstruct historical lot movements.
  • IC_TRAN_PND — Pending inventory transactions, consulted for in-flight movements that have not yet completed.

The package also uses DBMS_SQL for dynamic SQL, allowing it to construct flexible trace queries at runtime.

Usage Notes

GMI_LOT_TRACE_PKG is an internal Oracle EBS package rather than a public API. It is not referenced directly by customer extensions in normal circumstances but is invoked by five other database objects within the APPS schema. These callers are typically OPM lot traceability forms (such as the Lot Genealogy or Recall Query windows) and concurrent programs that produce trace and recall reports.

When investigating a recall or quality incident, the user typically triggers an OPM form or concurrent request; that layer calls EXP_LOT or EXP_LOT1, which in turn use HAS_INGRED and HAS_PRODUCT to decide whether upward and downward traversal is required before querying GMI_LOT_TRACE and the IC_ tables. Because the package relies on DBMS_SQL and accesses GMI_LOT_TRACE directly, customizations should avoid modifying the underlying trace records outside supported OPM processes. Use the standard OPM traceability forms and reports to invoke this logic rather than calling the package directly.