Results for “receipt_issue_type”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

INVBV_SERIAL_NUMBER_TXNS is a read-only Oracle EBS database view owned by the APPS schema and defined in the INV (Inventory) product. The "BV" naming convention identifies it as a business view, and the ETRM metadata records it as VALID and described as "Retrofitted," indicating that the object was reconstructed or re-pointed as part of an upgrade or retrofit effort between release levels such as 12.1.1 and 12.2.2. The view presents serial number transaction activity by combining serial-level unit transaction records with lot-level transaction data, producing a denormalized reporting surface that is otherwise awkward to assemble from the base transaction tables. It functions as a compatibility and reporting bridge for serialized inventory movements.

Underlying Base Objects

The documented view text reveals two distinct branches joined by UNION ALL, each anchored to MTL_UNIT_TRANSACTIONS (MUT), which stores one row per serial number per transaction. The first branch joins MTL_TRANSACTION_LOT_NUMBERS (MLN) on MLN.SERIAL_TRANSACTION_ID = MUT.TRANSACTION_ID, supplying the LOT_NUMBER, TRANSACTION_ID, and the serial group identifier for lot-controlled, serialized items. The second branch is reserved for items not under lot control; per the embedded comment, MTL_UNIT_TRANSACTIONS.TRANSACTION_ID is then equal to MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID, so the view selects from MTL_MATERIAL_TRANSACTIONS (MMT) joined directly to MUT and emits NULL for the lot and lot-transaction-derived columns. The second branch additionally restricts rows to primary owning-organization transactions: MMT.ORGANIZATION_ID = NVL(MMT.OWNING_ORGANIZATION_ID, MMT.ORGANIZATION_ID), NVL(MMT.OWNING_TP_TYPE, 2) = 2, and NVL(MMT.LOGICAL_TRANSACTION, -99) <> 1. All three base tables are referenced through APPS synonyms.

Key Columns

• TRANSACTION_ID — Transaction key, sourced from MLN in the lot-controlled branch and from MMT in the non-lot-controlled branch.
• SERIAL_TRANSACTION_GROUP_ID — The serial transaction identifier (MLN.SERIAL_TRANSACTION_ID); NULL in the non-lot-controlled branch.
• SERIAL_NUMBER / INVENTORY_ITEM_ID / ORGANIZATION_ID — Serial identifier and its item and inventory organization context, always taken from MUT.
• LOT_NUMBER — Lot associated with the serialized transaction; populated only where lot control applies.
• SUBINVENTORY_NAME, INVENTORY_LOCATOR_ID, TRANSACTION_DATE — Physical location and effective date of the serial movement.
• TRANSACTION_SOURCE_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_NAME, RECEIPT_ISSUE_TYPE — Origin and direction attributes of the transaction.
• CREATED_BY/CREATION_DATE, LAST_UPDATED_BY/LAST_UPDATE_DATE — Standard audit columns.

Common Use Cases and Queries

Typical applications include serial number traceability, reconciliation of serialized receipts and issues, and reporting on serial activity by organization, subinventory, or date range without navigating the lot and unit transaction tables separately.

• Trace a specific serial's full movement history:
  SELECT transaction_id, serial_number, organization_id, subinventory_name, transaction_date, receipt_issue_type FROM invbv_serial_number_txns WHERE serial_number = :serial AND organization_id = :org ORDER BY transaction_date;
• Locate serial transactions for one item in a period:
  SELECT serial_number, lot_number, transaction_date, transaction_source_name FROM invbv_serial_number_txns WHERE inventory_item_id = :item AND transaction_date BETWEEN :from_date AND :to_date;
• Aggregate serial activity by subinventory:
  SELECT organization_id, subinventory_name, receipt_issue_type, COUNT(*) FROM invbv_serial_number_txns GROUP BY organization_id, subinventory_name, receipt_issue_type;

Because the view is created WITH READ ONLY, it supports query-only access and cannot be used as a DML target.