Search Results lot_quantity




Overview
Explore in AI Chat

Overview

APPS.MTL_ADJUSTMENT_LOTSERIAL_V is a reporting view in Oracle E-Business Suite (validated against 12.1.1 and 12.2.2) that presents lot and serial information associated with inventory material transactions. Its principal purpose is to resolve the relationship between a material transaction, the lot number(s) it consumed or produced, and the serial number(s) associated with those movements. Because Oracle stores serial-controlled and lot-controlled transaction detail in separate transactional tables, applications and reports frequently need a single denormalized result set that joins the two. This view supplies exactly that: one row per transaction/lot/serial combination, including a LOT_QUANTITY column that exposes the per-lot quantity moved.

The view is especially relevant to inventory adjustment reporting, where users need to see quantity movements at lot granularity. The alias used in the view definition — mtln.transaction_quantity lot_quantity — is the origin of the common lot_quantity reference, and it is the column most often requested when analysts search for "lot_quantity" in this context.

Underlying Base Objects

The view is defined as a UNION of two nearly identical SELECT statements against three user-facing synonyms owned by APPS:

The joins are outer joins ((+) syntax), so transactions without lot or serial rows are still returned. The two branches of the UNION partition the data by whether MTLN.SERIAL_TRANSACTION_ID IS NULL. The first branch handles rows where the lot line is not linked to a separate serial transaction, joining serial detail on MMT.TRANSACTION_ID. The second branch handles rows where SERIAL_TRANSACTION_ID IS NOT NULL, joining serial detail on that key instead. This structure ensures serial numbers are correctly matched to their originating lot line.

Key Columns

  • TRANSACTION_ID — Identifier from MTL_MATERIAL_TRANSACTIONS; links the row to the parent transaction.
  • LOT_NUMBER — Lot number from MTL_TRANSACTION_LOT_NUMBERS; null for non-lot-controlled items.
  • LOT_QUANTITY — The TRANSACTION_QUANTITY of the lot line, i.e., the quantity moved for that lot within the transaction.
  • SERIAL_NUMBER — Serial number from MTL_UNIT_TRANSACTIONS; null for non-serial-controlled items or unmatched rows.

Common Use Cases and Queries

Typical uses include lot genealogy reporting, serial-to-lot reconciliation, adjustment analysis, and feeding custom interfaces where line-level lot quantity and serial detail are required. A representative query lists all lots and serials for a transaction:

  • SELECT transaction_id, lot_number, lot_quantity, serial_number FROM apps.mtl_adjustment_lotserial_v WHERE transaction_id = :p_transaction_id;
  • SELECT lot_number, SUM(lot_quantity) FROM apps.mtl_adjustment_lotserial_v WHERE transaction_id IN (SELECT transaction_id FROM mtl_material_transactions WHERE transaction_type_id = :p_type) GROUP BY lot_number;

Because the view is a UNION, callers should expect potential duplicate-looking rows when both lot and serial detail exist and should filter or aggregate accordingly.