Search Results mtl_adjustment_lotserial_v




Overview
Explore in AI Chat

Overview

MTL_ADJUSTMENT_LOTSERIAL_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, defined within the INV (Inventory) product. Its status is VALID in the ETRM dictionary. The view presents lot and serial number information associated with material adjustment transactions, resolving both lot-controlled and serial-controlled inventory activity into a single denormalized result set. In EBS reporting and integration contexts, the view serves as a convenience layer over three high-volume inventory transaction tables, sparing developers from reconstructing the lot-to-serial join logic that Oracle applies internally.

The view exposes four columns — TRANSACTION_ID, LOT, LOT_QUANTITY, and SERIAL — allowing lot and serial data for an adjustment transaction to be retrieved without navigating the underlying transaction model directly. It is commonly used in reconciliation reports, inventory audit extracts, and interfaces that must identify which lot and serial numbers were affected by an adjustment.

Underlying Base Objects

The ETRM metadata documents the view as referencing three base objects, each exposed in APPS through a synonym:

The view text defines a UNION of two SELECT statements, differentiated by the treatment of SERIAL_TRANSACTION_ID. In the first branch, the query joins MTL_MATERIAL_TRANSACTIONS to MTL_TRANSACTION_LOT_NUMBERS and MTL_UNIT_TRANSACTIONS on TRANSACTION_ID using outer joins (+), restricted to rows where MTLN.SERIAL_TRANSACTION_ID IS NULL. In the second branch, the joins between MTL_MATERIAL_TRANSACTIONS and MTL_TRANSACTION_LOT_NUMBERS use an outer join, and MTL_TRANSACTION_LOT_NUMBERS is joined to MTL_UNIT_TRANSACTIONS on SERIAL_TRANSACTION_ID = TRANSACTION_ID (outer), restricted to rows where SERIAL_TRANSACTION_ID IS NOT NULL. This structure accommodates serialized transactions whose serial linkage is carried on the lot record rather than the transaction header.

Key Columns

  • TRANSACTION_ID — Identifier of the inventory transaction. It is the primary correlation key back to MTL_MATERIAL_TRANSACTIONS and is used to join the view to transaction reporting queries.
  • LOT — The lot number (sourced from MTLN.LOT_NUMBER) associated with the transaction. Also historically referred to as LOT_NUMBER in the underlying table.
  • LOT_QUANTITY — The transaction quantity recorded for the lot (sourced from MTLN.TRANSACTION_QUANTITY). It represents the quantity moved or adjusted for that lot on the transaction.
  • SERIAL — The serial number (sourced from MUT.SERIAL_NUMBER) associated with the transaction. Because the view uses outer joins, this column may be null for non-serialized transactions.

Common Use Cases and Queries

The view is typically queried to enumerate lot and serial detail for adjustment transactions, such as cycle count adjustments, miscellaneous issue/receipt corrections, and subinventory transfers posted as adjustments. A representative query retrieving all lot/serial detail for a given transaction is:

  • SELECT transaction_id, lot, lot_quantity, serial FROM apps.mtl_adjustment_lotserial_v WHERE transaction_id = :p_transaction_id;

To list lot and serial activity over a date range, the view is joined back to the transaction header:

  • SELECT v.transaction_id, v.lot, v.lot_quantity, v.serial, t.transaction_date, t.transaction_type_id FROM apps.mtl_adjustment_lotserial_v v, apps.mtl_material_transactions t WHERE v.transaction_id = t.transaction_id AND t.transaction_date BETWEEN :p_from AND :p_to;

Because the view is defined over transaction tables that can grow very large, queries should always filter on TRANSACTION_ID or constrain the driving transaction set before joining. The outer joins guarantee that non-serialized lot adjustments still return rows with a null SERIAL, and that transactions without lot detail remain accessible through the header, making the view suitable for audit extracts where completeness is required.