Search Results invbv_material_transactions

Overview

INVBV_MATERIAL_TRANSACTIONS is a backward-compatibility view owned by the APPS schema in Oracle E-Business Suite (EBS) Release 12.1.1 and 12.2.2. It is catalogued under the INV – Inventory product family and carries the status VALID. The ETRM description records it as "- Retrofitted", indicating that the view was introduced or re-pointed as part of the Oracle Applications retrofit program to preserve the semantics of the former Oracle Manufacturing/Inventory schema objects after the consolidation of base tables into the APPS schema.

The view exposes transactional inventory movement data drawn from MTL_MATERIAL_TRANSACTIONS, presenting it in a flattened, report-friendly form. In addition to the core transaction attributes, it decodes the polymorphic TRANSACTION_SOURCE_TYPE_ID and TRANSACTION_SOURCE_ID columns into a named set of surrogate key columns such as PO_HEADER_ID, SALES_ORDER_ID, WIP_ENTITY_ID, and PHYSICAL_INVENTORY_ID. It also provides descriptive lookups for transaction action and costed flag values. This makes the view suitable for operational reporting, reconciliation extracts, and integration feeds where consumers need source-document context without applying the DECODE logic themselves. Because it is a view rather than a table, no data is duplicated; queries always resolve against current base-table content.

Underlying Base Objects

The documented referenced base object is MTL_MATERIAL_TRANSACTIONS, accessed in the APPS schema through the SYNONYM. All columns projected by the view are qualified with the alias MMT, and every derived column is computed from MTL_MATERIAL_TRANSACTIONS alone, meaning this is a single-table view with no joins to other inventory tables. This is significant for performance: the view introduces negligible overhead beyond the DECODE expressions, and it inherits the partitioning, indexing, and security characteristics of the base table. Because the synonym resolves to the actual transaction table, the view remains valid across upgrades provided the base table structure is unchanged, which the VALID status confirms.

Key Columns

• TRANSACTION_ID – Primary identifier for the material transaction row.
• TRANSACTION_TYPE_ID / TRANSACTION_ACTION_ID – Numeric type identifier and the decoded action meaning resolved via the MFG_LOOKUPS lookup type MTL_TRANSACTION_ACTION.
• TRANSACTION_SOURCE_TYPE_ID / TRANSACTION_SOURCE_ID – The polymorphic source discriminator and its value; the view decodes these into PO_HEADER_ID, SALES_ORDER_ID, ACCOUNT_ID, WIP_ENTITY_ID, ACCOUNT_ALIAS_ID, REQUISITION_HEADER_ID, CYCLE_COUNT_HEADER_ID, PHYSICAL_INVENTORY_ID, and COST_UPDATE_ID.
• INVENTORY_ITEM_ID, ORGANIZATION_ID, REVISION – Item, inventory organization, and revision context.
• SUBINVENTORY_CODE, LOCATOR_ID – From-subinventory and from-locator for the movement.
• TRANSFER_SUBINVENTORY, TRANSFER_LOCATOR_ID, TRANSFER_ORGANIZATION_ID, TRANSFER_TRANSACTION_ID – Destination subinventory, destination locator, destination organization, and the paired transfer transaction identifier. TRANSFER_LOCATOR_ID is the column most often targeted by users searching this view, since it identifies the "to" locator on transfer transactions.
• TRANSACTION_DATE, TRANSACTION_QUANTITY, TRANSACTION_UOM, PRIMARY_QUANTITY – Timing and quantity measures, in both transaction and primary units of measure.
• SHIPMENT_NUMBER, WAYBILL_AIRBILL, FREIGHT_CODE, NUMBER_OF_CONTAINERS, REASON_ID, TRANSACTION_REFERENCE – Shipping, receipt, reason, and free-text reference attributes.
• COSTED_FLAG meaning – Decoded through MFG_LOOKUPS lookup type INV_YES_NO_ERROR to yield Yes/No/Error semantics.

Common Use Cases and Queries

The view is typically used to report inventory movements with human-readable source and action descriptors, and to reconcile transfer pairs by comparing LOCATOR_ID with TRANSFER_LOCATOR_ID.

Example — transfer movements between locators for a given organization:

SELECT transaction_id, inventory_item_id, organization_id,
       subinventory_code, locator_id,
       transfer_subinventory, transfer_locator_id,
       transaction_quantity, transaction_uom, transaction_date
  FROM apps.invbv_material_transactions
 WHERE organization_id = :org_id
   AND transfer_locator_id IS NOT NULL
   AND transaction_date >= :start_date;

Example — isolating receipt-side transactions sourced from purchase orders:

SELECT transaction_id, po_header_id, inventory_item_id,
       transaction_quantity, transaction_date
  FROM apps.invbv_material_transactions
 WHERE po_header_id IS NOT NULL;

Because the view is a thin projection over MTL_MATERIAL_TRANSACTIONS, consumers requiring the decoded transfer locator, source-document identifiers, or transaction action meanings can query it directly while leaving the DECODE logic to the database. In integration scenarios the view serves as a stable interface should the base table's decoding requirements evolve, shielding downstream extracts from repeated rework.