Results for “mtl_serial_numbers_temp_erv”

20 results




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

Overview

MTL_SERIAL_NUMBERS_TEMP_ERV is a PL/SQL view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It resides in the Inventory (INV) product and is documented as being "required by electronic records to snapshot serial numbers." In practice the view serves e-records and audit-oriented reporting: it presents a consolidated snapshot of serial number data associated with pending material transactions, joining the temporary serial number staging table to the master serial number table. Because it is a view and not a table, it introduces no storage of its own and reflects the current state of the underlying staging and master data at the moment of query execution. The view is relevant for organizations in regulated industries (pharmaceutical, aerospace, defense, medical device) that must retain serialized transaction evidence for compliance frameworks such as 21 CFR Part 11. The keyword "serial_prefix" that led to this view maps directly to the SERIAL_PREFIX column exposed in its SELECT list.

Underlying Base Objects

The ETRM metadata for 12.2.2 lists four referenced base objects, all consumed through APPS synonyms:

The documented view text shows two branches combined by UNION. The first branch joins MTL_SERIAL_NUMBERS_TEMP (alias MSNT) to MTL_MATERIAL_TRANSACTIONS_TEMP (MMTT) on TRANSACTION_TEMP_ID, filtered to TRANSACTION_ACTION_ID = 1, and to MTL_SERIAL_NUMBERS (MSN) on FM_SERIAL_NUMBER = SERIAL_NUMBER, with organization and item equality constraints. The second branch draws complementary columns from MTL_SERIAL_NUMBERS_TEMP, including VENDOR_SERIAL_NUMBER and SERIAL_PREFIX, ensuring the result set is complete for both received and issued serialized flows.

Key Columns

Common Use Cases and Queries

Typical uses include pre-transaction verification, serial range reconciliation, and compliance snapshots for e-records. A representative query filtering by prefix is:

  • SELECT transaction_temp_id, serial_prefix, fm_serial_number, to_serial_number, vendor_serial_number FROM apps.mtl_serial_numbers_temp_erv WHERE serial_prefix = :prefix;
  • SELECT t.transaction_temp_id, t.serial_prefix, s.serial_number FROM apps.mtl_serial_numbers_temp_erv t, apps.mtl_serial_numbers s WHERE t.fm_serial_number = s.serial_number;
  • SELECT transaction_temp_id, parent_serial_number, origination_date FROM apps.mtl_serial_numbers_temp_erv ORDER BY origination_date DESC;

Because the view reads staging tables, results are transient: rows persist only while the associated pending transaction remains open. Queries intended for audit retention should therefore be materialized into a custom table rather than re-executed later. Joins to MTL_MATERIAL_TRANSACTIONS_TEMP should use TRANSACTION_TEMP_ID and ORGANIZATION_ID to preserve the view's documented filter semantics.