Search Results mtl_lot_issues_val_v




Overview
Explore in AI Chat

Overview

The view APPS.MTL_LOT_ISSUES_VAL_V is an Oracle E-Business Suite Inventory (INV) database object documented in ETRM for release 12.1.1 and 12.2.2. Its status is VALID and it is owned by the APPS schema. The ETRM description carries the notation "10SC ONLY," indicating that the object was created for a specific, restricted implementation context rather than as a general-purpose, globally shipped Inventory view. This distinction is important: the presence of a view in the APPS schema does not by itself imply that it is available in every EBS instance, and DBAs should confirm existence before referencing it in custom code.

Functionally, the view presents lot-level on-hand quantity information enriched with lot attributes. It joins on-hand quantity records to lot number definitions, exposing both the primary transaction quantity and the secondary transaction quantity alongside expiration, grade, parent lot, and locator details. In reporting and integration scenarios, it serves as a convenience layer that spares developers from writing the join between on-hand quantities and lot master data repeatedly, and it is particularly relevant where dual-unit-of-measure (primary and secondary UOM) inventory tracking is enabled.

Underlying Base Objects

The ETRM metadata documents two referenced base objects, both exposed to APPS as synonyms:

  • MTL_ONHAND_QUANTITIES_DETAIL (aliased MOQ in the view text) — the detail on-hand quantity table holding quantity by organization, item, subinventory, locator, lot, and revision.
  • MTL_LOT_NUMBERS (aliased MLN) — the lot master table holding lot-level attributes such as expiration date, parent lot number, and grade code.

The view text joins these two tables on three keys: ORGANIZATION_ID, INVENTORY_ITEM_ID, and LOT_NUMBER. A predicate restricts results to rows where MOQ.LOT_NUMBER IS NOT NULL, ensuring that only lot-controlled on-hand records are returned. Because the join is an inner join, on-hand rows without a corresponding lot master record are excluded. The view is read-only; it contains no DML logic and simply projects and renames columns from its base objects.

Key Columns

  • LOT_NUMBER — the lot identifier from the on-hand record, used as a join key and displayed for both tables.
  • EXPIRATION_DATE — lot expiration date sourced from MTL_LOT_NUMBERS.
  • TRANSACTION_QUANTITY — the view's alias for MOQ.PRIMARY_TRANSACTION_QUANTITY, the primary UOM on-hand quantity. Note the column renaming, which differs from the base table name.
  • SECONDARY_TRANSACTION_QUANTITY — the secondary UOM on-hand quantity, populated when dual UOM control is enabled for the item; the column most relevant to searches on this term.
  • ORGANIZATION_ID, INVENTORY_ITEM_ID, SUBINVENTORY_CODE, LOCATOR_ID, REVISION — the inventory dimension keys identifying where the quantity resides.
  • PARENT_LOT_NUMBER and GRADE_CODE — lot hierarchy and quality grade attributes from the lot master.

Common Use Cases and Queries

Typical uses include lot-level on-hand reporting for dual-UOM items, integration extracts feeding warehouse or quality systems, and reconciliation between lot master attributes and physical on-hand balances. The following sample returns lot balances for a given organization and item, including the secondary quantity:

  • SELECT lot_number, expiration_date, transaction_quantity, secondary_transaction_quantity, subinventory_code, locator_id, revision, grade_code, parent_lot_number FROM apps.mtl_lot_issues_val_v WHERE organization_id = :p_org AND inventory_item_id = :p_item AND secondary_transaction_quantity > 0 ORDER BY lot_number;

Because the view is documented as "10SC ONLY," any dependency should be validated against the target instance and release before deployment.