Search Results pmi_lot_source_leaf_v




Overview
Explore in AI Chat

Overview

The PMI_LOT_SOURCE_LEAF_V view is an APPS-owned database object in Oracle E-Business Suite, delivered under the Process Manufacturing Intelligence (PMI) product family. According to the ETRM metadata for release 12.1.1 and 12.2.2, the view lists raw material lots that are used in the manufacture of a product lot. In this context, a "raw material lot" is defined as any lot that cannot be further decomposed into a lower level of constituent ingredients — meaning it represents a terminal (leaf) node in the lot genealogy hierarchy. The view is documented as belonging to an obsolete product module, and its status in the data dictionary is recorded as VALID.

The view plays a reporting and integration role rather than a transactional one. It exposes a flattened, query-ready representation of the deepest sourcing relationships captured within the Process Manufacturing lot genealogy, allowing downstream reports, analytics, and integrations to identify the originating ingredient lots for a finished or intermediate product lot without navigating intermediate decomposition steps themselves.

Underlying Base Objects

The view is defined exclusively over a single referenced base object: the synonym PMI_LOT_GENEALOGY, which resolves to the underlying lot genealogy table in the APPS schema. The defining query uses a correlated NOT EXISTS subquery against the same genealogy source (aliased as B) to filter out any ingredient lot that itself appears as a product lot with further decomposition. The predicate B.INGRED_LOT_ID <> B.PRODUCT_LOT_ID excludes self-referencing rows from that determination, ensuring circular or identity relationships do not incorrectly suppress a legitimate leaf record.

The view is created WITH READ ONLY, confirming that it is intended strictly for query and reporting access and cannot be used as the target of DML operations.

Key Columns

The view projects four columns, all sourced directly from the genealogy record:

  • PRODUCT_ITEM_ID — The inventory item identifier of the manufactured product lot whose sourcing is being described.
  • PRODUCT_LOT_ID — The lot identifier of that manufactured product. This is the parent context for the reported raw material usage.
  • INGRED_ITEM_ID — The inventory item identifier of the raw material ingredient lot that terminates the decomposition chain for the associated product lot.
  • INGRED_LOT_ID — The lot identifier of that raw material ingredient. This is the column most commonly referenced in ad hoc searches, including queries issued against the term "ingred_lot_id", because it uniquely ties a leaf material lot back to the product lot that consumed it.

Common Use Cases and Queries

Typical usage centers on traceability and recall analysis, where a manufacturer must determine which raw material lots were ultimately consumed in a given product lot, or conversely, which product lots were affected by a suspect raw material lot. A representative query joining item and lot descriptions follows:

SELECT PRODUCT_ITEM_ID, PRODUCT_LOT_ID, INGRED_ITEM_ID, INGRED_LOT_ID
FROM APPS.PMI_LOT_SOURCE_LEAF_V
WHERE PRODUCT_LOT_ID = :p_product_lot_id;

To trace backward from a specific leaf material, the same view supports filtering on the ingredient lot, for example WHERE INGRED_LOT_ID = :p_ingred_lot_id, returning every product lot whose manufacture depended on that raw material. Because the view only returns leaf-level sources, consumers are relieved of the recursive logic required to prune intermediate decomposition nodes. Given the obsolete status of the PMI module, organizations running 12.1.1 or 12.2.2 should validate continued availability of the underlying PMI_LOT_GENEALOGY object before embedding this view in production reports.