Search Results mtl_transaction_action




Overview
Explore in AI Chat

Overview

APPS.WSM_INV_GENEALOGY_V is a reporting view in Oracle E-Business Suite (documented in ETRM for 12.2.2 and applicable to 12.1.1) that presents a denormalized, human-readable projection of inventory material transactions. The view resolves the numeric foreign keys stored on MTL_MATERIAL_TRANSACTIONS—organization, transaction type, transaction action, and transaction source type—into descriptive text such as organization code, operating unit name, transaction type description, action meaning, and source type description.

Its role is to support genealogy and transaction-history reporting, where a user or downstream process needs to trace material movements for an item or transaction without performing the multiple lookup joins manually. Because it carries both the internal identifiers (TRANSACTION_ID, ORGANIZATION_ID, LOCATOR_ID) and their decoded descriptions, the view serves reporting, integration extracts, and diagnostic queries. The name "WSM" ties it to the Warehouse/Shop Floor Management domain, while the underlying tables are standard Oracle Inventory objects. The documented base objects include several views exposed through synonyms (MTL_MATERIAL_TRANSACTIONS, MTL_PARAMETERS, MTL_TRANSACTION_TYPES, MTL_TXN_SOURCE_TYPES) plus MFG_LOOKUPS and HR_ORGANIZATION_UNITS, with the security packages HR_GENERAL and HR_SECURITY referenced for organization access.

Underlying Base Objects

The view is defined over six sources joined on their respective keys:

  • MTL_MATERIAL_TRANSACTIONS (TRAN) — the driving transaction table, supplying transaction_id, subinventory_code, transaction_date, created_by, locator_id, and the foreign keys organization_id, transaction_type_id, transaction_action_id, and transaction_source_type_id.
  • MTL_PARAMETERS (ORG) — joined on organization_id to provide organization_code.
  • HR_ORGANIZATION_UNITS (HOU) — joined on organization_id to provide the operating unit / organization name (HOU.NAME).
  • MTL_TRANSACTION_TYPES (TRAN_TYPE) — joined on transaction_type_id to provide the transaction type description.
  • MFG_LOOKUPS (ACTION) — joined on transaction_action_id = lookup_code, restricted to lookup_type = 'MTL_TRANSACTION_ACTION', to provide the action meaning.
  • MTL_TXN_SOURCE_TYPES (SOURCE_TYPE) — joined on transaction_source_type_id to provide the source type description.

Because HR_ORGANIZATION_UNITS and the HR_GENERAL/HR_SECURITY packages participate, the view is subject to organization-level security; visible rows are effectively filtered by the operating units and organizations accessible to the session's responsibility.

Key Columns

  • TRANSACTION_ID — primary identifier of the material transaction; the anchor for genealogy and drill-down.
  • SUBINVENTORY_CODE — subinventory in which the transaction occurred.
  • TRANSACTION_DATE — date the transaction was posted.
  • CREATED_BY — user ID that created the transaction record.
  • ORGANIZATION_CODE — inventory organization code from MTL_PARAMETERS.
  • NAME — organization / operating unit name from HR_ORGANIZATION_UNITS.
  • DESCRIPTION (transaction type) — descriptive name of the transaction type.
  • MEANING (action) — decoded meaning of the transaction action lookup, i.e., the MTL_TRANSACTION_ACTION value associated with the user's search term "mtl_transaction_action".
  • DESCRIPTION (source type) — decoded transaction source type.
  • LOCATOR_ID — inventory locator (stocking position) identifier.
  • ORGANIZATION_ID — numeric organization identifier for joins back to inventory tables.

Common Use Cases and Queries

Typical scenarios include transaction-history reports for a subinventory or item, reconciliation of transaction types and actions, and feeds into genealogy or traceability processes where the decoded action (for example, issue, receipt, transfer) must be shown. The view is also convenient for diagnosing how transaction_action_id values map to lookup meanings without referencing MFG_LOOKUPS directly.

Sample query retrieving recent transactions with decoded action and type:

  • SELECT transaction_id, organization_code, subinventory_code, transaction_date, description, meaning, created_by FROM apps.wsm_inv_genealogy_v WHERE transaction_date >= SYSDATE - 7 ORDER BY transaction_date DESC;
  • SELECT organization_id, organization_code, COUNT(*) FROM apps.wsm_inv_genealogy_v WHERE meaning = 'Issue' GROUP BY organization_id, organization_code;

Note that the view does not expose the item or quantity columns present on MTL_MATERIAL_TRANSACTIONS; for item-level genealogy, join back to MTL_MATERIAL_TRANSACTIONS on TRANSACTION_ID.