Search Results rcv_lot_transactions




Overview
Explore in AI Chat

Overview

RCV_LOT_TRANSACTIONS is a receiving lot transaction table owned by the PO (Purchasing) schema in Oracle E-Business Suite. It records the lot-level detail associated with receiving transactions, capturing the movement, quantity, and disposition of lot-controlled and sublot-controlled inventory items at the time a receipt, delivery, return, correction, or adjustment is processed. In Oracle EBS 12.1.1 and 12.2.2 it functions as a supporting detail table to RCV_TRANSACTIONS, extending the header-level receipt transaction with the specific lot and grade information required for lot-tracked items.

Under the heuristic Data Vault classification mined from its foreign key structure, RCV_LOT_TRANSACTIONS is a link table. It resolves and connects multiple parent entities — RCV_TRANSACTIONS (referenced three times) and RCV_SHIPMENT_LINES — rather than acting as a standalone hub of business entities. This modeling suggestion reflects the table's role as an associative join carrying the transactional relationship between a receiving transaction, its shipment line, and the lot being transacted.

Key Information Stored

The table contains 24 documented columns. The most significant are:

  • TRANSACTION_ID — Foreign key to RCV_TRANSACTIONS; identifies the parent receiving transaction. This is the primary relationship to the header transaction, not a standalone surrogate key.
  • SHIPMENT_LINE_ID — Foreign key to RCV_SHIPMENT_LINES; ties the lot movement to a specific shipment line.
  • SOURCE_TRANSACTION_ID and CORRECTION_TRANSACTION_ID — Self-referencing-style foreign keys back to RCV_TRANSACTIONS, used to trace the original or corrected transaction lineage.
  • LOT_TRANSACTION_TYPE — Classifies the nature of the lot transaction (for example receipt, return, correction).
  • LOT_NUM and SUBLOT_NUM — The lot and sublot identifiers of the affected material.
  • ITEM_ID — The inventory item being transacted.
  • QUANTITY and PRIMARY_QUANTITY — Transaction quantity in transaction and primary units respectively.
  • SECONDARY_QUANTITY — Quantity in a secondary unit of measure.
  • TRANSACTION_DATE and EXPIRATION_DATE — Date of the transaction and the lot expiration date.
  • QC_GRADE — Quality grade assigned to the lot.
  • REASON_CODE — Reason associated with the lot transaction.
  • Audit columns (LAST_UPDATE_DATE, CREATED_BY, PROGRAM_ID, REQUEST_ID, etc.) — Standard EBS WHO columns supporting concurrency, auditing, and concurrent program traceability.

The documented metadata does not identify a single-column surrogate primary key or a named unique index; the foreign key columns TRANSACTION_ID and SHIPMENT_LINE_ID serve as the principal business-key candidates for joining and de-duplication.

Common Use Cases and Queries

Typical scenarios include lot traceability for recalled or quality-affected material, reconciliation of received lot quantities against shipment lines, and reporting of expiration-dated stock by QC grade. A representative join pattern retrieves lot detail against its parent receipt transaction:

  • SELECT lt.TRANSACTION_ID, lt.LOT_NUM, lt.SUBLOT_NUM, lt.QUANTITY, lt.QC_GRADE FROM PO.RCV_LOT_TRANSACTIONS lt WHERE lt.TRANSACTION_ID = :transaction_id;
  • Join to RCV_TRANSACTIONS on lt.TRANSACTION_ID = rt.TRANSACTION_ID to obtain receipt header context such as receipt number and vendor.
  • Join to RCV_SHIPMENT_LINES on lt.SHIPMENT_LINE_ID = sl.SHIPMENT_LINE_ID to reconcile lot quantities against the expected shipment line.
  • Join to the item master (MTL_SYSTEM_ITEMS_B) on lt.ITEM_ID to enrich lot reports with item descriptions.
  • Use SOURCE_TRANSACTION_ID and CORRECTION_TRANSACTION_ID to trace correction and reversal chains for audit reporting.

Reporting use cases include expiration monitoring (EXPIRATION_DATE filters), grade-based on-hand analysis (QC_GRADE), and quantity variance analysis across QUANTITY, PRIMARY_QUANTITY, and SECONDARY_QUANTITY.

Related Objects

The most significant related objects, based on the documented foreign key relationships, are:

  • RCV_TRANSACTIONS — Referenced via TRANSACTION_ID, SOURCE_TRANSACTION_ID, and CORRECTION_TRANSACTION_ID; the central receiving transaction header.
  • RCV_SHIPMENT_LINES — Referenced via SHIPMENT_LINE_ID; the shipment line against which the lot is received.
  • MTL_SYSTEM_ITEMS_B — Joined on ITEM_ID for item attributes (lot control, shelf life).
  • RCV_SHIPMENT_HEADERS — Provides receipt header context when traversing through shipment lines.
  • MTL_LOT_NUMBERS / lot master tables — Related through LOT_NUM and ITEM_ID for lot attribute and expiration data.
  • RCV_TRANSACTIONS_INTERFACE — Related to receiving transaction processing that populates transaction data.