Results for “vendor_lot_number”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MTL_VENDOR_LOT_VIEW is a public view owned by the APPS schema within the Oracle Inventory (INV) module. Its documented description is "Supplier information," reflecting its purpose: to expose the association between internal inventory lot numbers and the corresponding supplier (vendor) lot numbers recorded against material transactions. The view is defined as a UNION ALL of two SELECT statements, each returning a distinct combination of organization, inventory item, lot number, and vendor lot number. Because the view is maintained by Oracle and marked VALID in ETRM 12.2.2, it is safe to consume in custom reports, BI Publisher data models, concurrent programs, and integration extracts without needing to reconstruct the underlying join logic. It is available in both Oracle EBS 12.1.1 and 12.2.2, where the INV data model for lot and transaction tracking is consistent for this object.

Underlying Base Objects

The documented base objects referenced by the view are:

• MTL_MATERIAL_TRANSACTIONS (SYNONYM) — the transaction header/fact table that stores inventory movements, including the VENDOR_LOT_NUMBER attribute captured at receipt or issue time.
• MTL_TRANSACTION_LOT_NUMBERS (SYNONYM) — the transaction-to-lot detail table that holds LOT_NUMBER and SUPPLIER_LOT_NUMBER for each lot-controlled transaction line.
• MTL_LOT_NUMBERS (SYNONYM) — the lot master table, listed as a referenced base object and providing the lot definition context for the organization/item/lot key.

The join predicate links MTL_TRANSACTION_LOT_NUMBERS to MTL_MATERIAL_TRANSACTIONS on TRANSACTION_ID, ORGANIZATION_ID, and INVENTORY_ITEM_ID. The first branch of the UNION ALL projects MMT.VENDOR_LOT_NUMBER as VENDOR_LOT_NUMBER, while the second branch projects MTLN.SUPPLIER_LOT_NUMBER as VENDOR_LOT_NUMBER. This UNION ALL design consolidates two distinct sources of vendor lot data — the transaction header and the transaction lot detail — into a single normalized column, ensuring supplier lot information is surfaced regardless of which table captured it.

Key Columns

• ORGANIZATION_ID — the inventory organization in which the transaction and lot reside. Primary key component and the standard partitioning dimension for multi-org queries.
• INVENTORY_ITEM_ID — the item identifier for the lot-controlled item. Joins to MTL_SYSTEM_ITEMS_B.
• LOT_NUMBER — the internal lot number from the transaction lot detail (MTLN.LOT_NUMBER), representing the organization's own lot identifier.
• VENDOR_LOT_NUMBER — the supplier's lot number, sourced from either MMT.VENDOR_LOT_NUMBER or MTLN.SUPPLIER_LOT_NUMBER depending on which UNION ALL branch produced the row.

Common Use Cases and Queries

Typical uses include supplier lot traceability for quality and recall investigations, receipt history reporting where both internal and vendor lot numbers must be shown side by side, and extracts feeding downstream systems that reconcile vendor certificates of analysis against internal lots.

Example query retrieving vendor lots for a specific item and organization:

• SELECT organization_id, inventory_item_id, lot_number, vendor_lot_number FROM mtl_vendor_lot_view WHERE organization_id = :org_id AND inventory_item_id = :item_id ORDER BY lot_number;

Example query mapping an internal lot to its supplier lot:

• SELECT lot_number, vendor_lot_number FROM mtl_vendor_lot_view WHERE organization_id = :org_id AND inventory_item_id = :item_id AND lot_number = :lot_number;

Because the view applies SELECT DISTINCT within each UNION ALL branch, duplicate transaction rows are collapsed per organization/item/lot/vendor-lot combination. Queries should therefore be treated as distinct reference lookups rather than transaction-level detail; where transaction quantities or dates are required, join back to MTL_MATERIAL_TRANSACTIONS directly.