Search Results exp_lot1

Overview

The APPS.GMI_LOT_TRACE_PKG package provides the core lot genealogy and traceability engine for Oracle Process Manufacturing (OPM) within Oracle E-Business Suite 12.1.1 and 12.2.2. Its principal business function is to resolve the chain of parent-child relationships that connects ingredient lots to finished product lots through a given production transaction. This "where-used" and "where-from" resolution is required for regulatory compliance (such as FDA 21 CFR Part 11, food safety, and pharmaceutical lot recall), quality investigations, and customer-facing product traceability reporting.

The package is owned by the APPS schema and classified as an OTHER API. Its source header identifies it as a generation (.pls) script, last shipped under 11.5.x, indicating that the package is stable and inherited into the 12.1.1 and 12.2.2 code lines without functional change. It is referenced by five other application packages, confirming that it is a shared, foundational object rather than a standalone utility.

Key Procedures and Functions

The package exposes four documented program units. No parameter lists beyond those shown in the source excerpt are reproduced here; the descriptions reflect documented behavior only.

• EXP_LOT — Expands the lot trace tree outward from a starting item and lot. Given an item/lot pair, a level, a component counter passed IN OUT, a view flag, and a trace identifier, it walks the genealogy in the chosen direction. It is the primary recursive driver used to explode a lot into its upstream ingredients or downstream products.
• EXP_LOT1 — The extended variant of EXP_LOT. It accepts the same core parameters plus a node_index and a lot_tab collection of type LOT_TAB_TYP (an associative array of numbers indexed by BINARY_INTEGER) passed IN OUT NOCOPY. The collection parameter carries accumulated lot results across recursion levels, which improves performance on deep or highly branched bills of material. LOT_TAB_TYP is declared at package level and is therefore reusable by calling code.
• HAS_INGRED — A Boolean-style predicate, returning VARCHAR2, that determines whether a specified item and lot combination has a traceable ingredient ancestry. It is used to short-circuit tree expansion when no upstream genealogy exists.
• HAS_PRODUCT — The counterpart predicate to HAS_INGRED. For a given item/lot, it determines whether there is a downstream product relationship in the trace. This is the function most commonly associated with the "has_product" search term and is typically the entry point when validating whether a lot was consumed into any finished output.

Tables Accessed

The package resolves data through APPS synonyms over the base OPM tables:

• GMI_LOT_TRACE — The central genealogy table storing the recorded parent-child lot relationships. It is the primary source for all expansion and predicate logic.
• IC_ITEM_MST — Validates item identifiers and supplies item attributes used in traversal.
• IC_LOTS_MST — Supplies lot master details and validates lot identifiers against the item.
• IC_TRAN_CMP — Completed inventory transactions, contributing actual consumption and production events to the trace.
• IC_TRAN_PND — Pending inventory transactions, allowing in-flight movements to be included in the genealogy.
• DBMS_SQL — The Oracle dynamic SQL package, used for dynamic statement construction where traversal shape is determined at runtime. Note that DBMS_SQL is not a business table; it appears in the metadata as a referenced object.

Usage Notes

GMI_LOT_TRACE_PKG is normally invoked indirectly. OPM lot traceability forms and concurrent programs (such as lot genealogy inquiries, recall reports, and quality results) call into it rather than executing the procedures directly. Its five package-level dependents confirm that other OPM APIs also rely on it for embedded trace checks.

Because the functions return VARCHAR2 rather than BOOLEAN, they are safe to call from SQL and from clients that cannot bind PL/SQL Booleans. For deep or multi-level trees, callers should prefer EXP_LOT1 and reuse a LOT_tab_TYP collection to avoid repeated allocation. Custom code performing recall or compliance analysis should call HAS_PRODUCT or HAS_INGRED first as a cheap existence test, then invoke the expansion procedures only when the predicate confirms a relationship exists. Always invoke the package as APPS with the calling user's AUTHID CURRENT_USER privileges respected, and observe standard EBS patching and versioning discipline, since the source is a seeded script.