Results for “mtl_material_txns_temp_view”

14 results




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

Overview

MTL_MATERIAL_TXNS_TEMP_VIEW is a database view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. As documented in the ETRM repository (status VALID, product INV - Inventory), it is defined as a view of MTL_MATERIAL_TRANSACTIONS_TEMP used in an Inquiry form. Rather than exposing individual pending transaction rows, the view presents aggregated counts of records in the material transactions temporary table, grouped by request, transaction mode, and transaction header. This aggregation makes the view particularly useful for monitoring, diagnosing, and reporting on the contents of the pending transaction interface area maintained by the Inventory module.

The view is referenced in the context of the underlying base table MTL_MATERIAL_TRANSACTIONS_TEMP, a synonym in the APPS schema. It returns one summarized row per distinct combination of TRANSACTION_HEADER_ID, REQUEST_ID, and TRANSACTION_MODE, along with the number of constituent records. This design supports inquiry forms that display how many transaction lines have been staged for a given header and concurrent request, rather than forcing the form to read every staged row.

Underlying Base Objects

The view is defined exclusively over MTL_MATERIAL_TRANSACTIONS_TEMP, documented in ETRM 12.2.2 as a SYNONYM and the sole referenced base object. MTL_MATERIAL_TRANSACTIONS_TEMP is the interface staging table into which transaction rows are written before processing by the Inventory transaction manager. The view text applies a GROUP BY clause to that table, eliminating row-level detail and returning aggregate counts instead.

  • MTL_MATERIAL_TRANSACTIONS_TEMP — the base staging table holding unprocessed transaction rows. The view consumes this object and performs aggregation directly against it.
  • APPS schema — the owner of both the view and the referenced synonym, ensuring consistent privilege and synonym resolution for EBS forms and reports.

Key Columns

The view exposes four columns, each derived from the projection and grouping applied to the base table:

  • TRANSACTION_HEADER_ID — the identifier that links a group of staged transaction rows to a common transactional header. This is the column most frequently used to filter the view, and it drives the grouping of records.
  • REQUEST_ID — the concurrent program request identifier under which the rows were staged. It allows tracking of transactions submitted by a specific concurrent request.
  • TRANSACTION_MODE — the processing mode associated with the staged transactions, indicating how the rows are intended to be handled during transaction processing.
  • NUMBER_OF_RECORDS — the COUNT(*) of rows in MTL_MATERIAL_TRANSACTIONS_TEMP for each distinct combination of header, request, and mode. This is the aggregate result that distinguishes the view from its base table.

Common Use Cases and Queries

The view is principally consumed by Oracle Inventory Inquiry forms to display summary counts of pending transactions. In reporting and integration contexts, it can be used to monitor interface backlogs, confirm whether a request staged the expected volume of rows, and reconcile headers against their staged line counts.

A typical query retrieving all summaries for a specific transaction header appears as follows:

  • SELECT request_id, transaction_mode, number_of_records, transaction_header_id FROM apps.mtl_material_txns_temp_view WHERE transaction_header_id = :header_id;
  • SELECT transaction_header_id, request_id, SUM(number_of_records) FROM apps.mtl_material_txns_temp_view GROUP BY transaction_header_id, request_id;
  • SELECT request_id, transaction_mode, SUM(number_of_records) FROM apps.mtl_material_txns_temp_view GROUP BY request_id, transaction_mode ORDER BY request_id;

Because the view performs aggregation over MTL_MATERIAL_TRANSACTIONS_TEMP, queries against it are inherently lighter than scanning the base table, but they also cannot retrieve individual transaction attributes. Where row-level detail is required, the underlying table must be queried directly. The view reflects its documented ETRM definition across both 12.1.1 and 12.2.2.