Search Results pick_rule_id




Overview
Explore in AI Chat

Overview

MTL_TRANSACTION_LOTS_TEMP_V is an APPS-owned, VALID database view within the INV (Inventory) product module of Oracle E-Business Suite 12.1.1 and 12.2.2. Its purpose is to consolidate lot and serial-level transaction staging data during inventory transaction processing. In standard EBS flow, lot-controlled material transactions are first written into temporary tables while validation, rule resolution, and error-checking occur before the records are committed to the permanent transaction tables. This view presents that staging data in a denormalized, reporting-friendly form, joining lot-level transaction lines with their parent transaction headers. Because it exposes lot attributes, warehouse rule identifiers, and error codes, it serves as an interface point for diagnostics, custom validation screens, and integration programs that need to inspect transactions prior to completion. Users searching for pick_rule_id reach this view because it is a documented carrier of the PICK_RULE_ID column inherited from the lot staging table.

Underlying Base Objects

Per the ETRM 12.2.2 metadata, the view is defined over three referenced base objects, all accessed through APPS synonyms:

  • MTL_MATERIAL_TRANSACTIONS_TEMP — the transaction header staging table; supplies organization and item context.
  • MTL_TRANSACTION_LOTS_TEMP — the lot/serial transaction line staging table; supplies quantity, lot number, attributes, and rule identifiers.
  • MTL_TXN_REQUEST_LINES — the move order line table, referenced in the second half of the UNION for transactions originating from move order requests.

The view text shows two select branches combined by UNION. The first joins MTL_TRANSACTION_LOTS_TEMP (alias L) to MTL_MATERIAL_TRANSACTIONS_TEMP (alias MMTT) on TRANSACTION_TEMP_ID, with the ORGANIZATION_ID derived via DECODE on TRANSACTION_ACTION_ID so that transfer transactions report the destination organization. The second branch uses SELECT DISTINCT against MTL_TXN_REQUEST_LINES (alias MTRL) to cover move-order-driven replenishment and pick scenarios, which is precisely why PICK_RULE_ID and PUT_AWAY_RULE_ID appear in the projection: these rule columns are populated when put-away and picking rules resolve against the staged lot line.

Key Columns

Common Use Cases and Queries

Typical scenarios include pre-post validation reporting, reconciliation of pending lots against expected receipts, auditing which put-away or pick rule was applied, and custom interfaces that must read staged data before the concurrent transaction worker commits it.

  • Identify pending transactions using a specific pick rule: 
    SELECT transaction_temp_id, organization_id, inventory_item_id,
       lot_number, transaction_quantity, pick_rule_id, error_code
FROM   apps.mtl_transaction_lots_temp_v
WHERE  pick_rule_id IS NOT NULL;
  • Locate staged lines that failed validation: 
    SELECT transaction_temp_id, inventory_item_id, lot_number, error_code
FROM   apps.mtl_transaction_lots_temp_v
WHERE  error_code IS NOT NULL;
  • Trace staged lots for a given concurrent request: 
    SELECT transaction_temp_id, lot_number, lot_expiration_date, status_id
FROM   apps.mtl_transaction_lots_temp_v
WHERE  request_id = :p_request_id;

Because these are temporary staging tables, queries should be timely; data is transient and is cleared after the transaction manager processes the records. For point-in-time auditing, join the view to MTL_MATERIAL_TRANSACTIONS_TEMP on TRANSACTION_TEMP_ID to recover full header context.