Results for “mtl_serial_numbers_interface”

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

Overview

MTL_SERIAL_NUMBERS_INTERFACE is a temporary staging table in the INV (Inventory) schema of Oracle E-Business Suite, documented as VALID at releases 12.1.1 and 12.2.2. As its ETRM description states, it serves as "temporary serial number storage for transactions processor." When transactions involving serial-controlled items are submitted through the Inventory transaction processor — receipts, issues, transfers, deliveries, and returns — the individual serial records travel through this interface table before the processor validates them, derives the corresponding transaction lines, and finally writes the persisted serial rows into MTL_SERIAL_NUMBERS.

Because the table exists solely as a processing staging area, rows are transient by nature: successful transactions are consumed and purged by the transaction processor, while failed rows remain flagged and error-annotated for diagnosis. The heuristic Data Vault classification in the ETRM metadata is hub-leaning, with a foreign reference to CSD_PRODUCT_TRANSACTIONS via PRODUCT_TRANSACTION_ID. In Data Vault modeling terms, this suggests treating TRANSACTION_INTERFACE_ID as a hub-like surrogate while the descriptive serial attributes (status, ownership, and the DESCRIPTIVE/FLEX attribute groups) behave as satellite-style descriptive payload.

Key Information Stored

The table contains 141 documented columns. The primary key is the surrogate key MTL_SERIAL_NUMBERS_INT_PK, defined on TRANSACTION_INTERFACE_ID, which uniquely identifies each staging row and is the join key back to the transaction interface line context.

• TRANSACTION_INTERFACE_ID — surrogate primary key; the row's unique identifier.
• SOURCE_CODE, SOURCE_LINE_ID — origin system and originating document line.
• FM_SERIAL_NUMBER, TO_SERIAL_NUMBER — the beginning and ending serial numbers in a contiguous range.
• VENDOR_SERIAL_NUMBER, VENDOR_LOT_NUMBER — supplier-supplied serial and lot references.
• PROCESS_FLAG, ERROR_CODE — processing state and validation failure indicator returned by the processor.
• PARENT_SERIAL_NUMBER — the parent serial for component or genealogy relationships.
• STATUS_ID, STATUS_NAME — serial status (e.g., defined, in inventory, shipped) at processing time.
• PRODUCT_CODE, PRODUCT_TRANSACTION_ID — product context and the foreign key to CSD_PRODUCT_TRANSACTIONS.
• C_ATTRIBUTE1–C_ATTRIBUTE30, D_ATTRIBUTE1–D_ATTRIBUTE20, N_ATTRIBUTE1–N_ATTRIBUTE30 — the characteristic, descriptive, and numeric attribute groups.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1–ATTRIBUTE15 — the standard EBS descriptive flexfield columns for both interface and serial layers.

Request/audit columns (REQUEST_ID, PROGRAM_ID, CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY) track submission and provenance, while the time/cycle counters (TIME_SINCE_NEW, CYCLES_SINCE_OVERHAUL, NUMBER_OF_REPAIRS, and related columns) support maintenance and repair tracking.

Common Use Cases and Queries

Typical usage is operational troubleshooting and reconciliation. Support and implementation teams query failed rows, monitor processing status, and verify that submitted serials balanced correctly against interface lines.

• Identify rows that failed validation:

  SELECT TRANSACTION_INTERFACE_ID, FM_SERIAL_NUMBER, ERROR_CODE, PROCESS_FLAG
  FROM   INV.MTL_SERIAL_NUMBERS_INTERFACE
  WHERE  ERROR_CODE IS NOT NULL;

• Monitor a batch by request or source line:

  SELECT SOURCE_CODE, SOURCE_LINE_ID, COUNT(*)
  FROM   INV.MTL_SERIAL_NUMBERS_INTERFACE
  GROUP BY SOURCE_CODE, SOURCE_LINE_ID, REQUEST_ID;

• Reconcile staged serials against the lot interface using the shared SERIAL_TRANSACTION_TEMP_ID reference.
• Detect status mismatches by comparing STATUS_ID / STATUS_NAME to the persisted MTL_SERIAL_NUMBERS record after processing.

Reporting use cases include serial receipt audits, vendor serial traceability, and pre-processing validation reports. Queries should be run with awareness that rows are temporary and disappear after successful processing, so this table is best used for diagnostics rather than historical reporting.

Related Objects

• MTL_SERIAL_NUMBERS — the permanent serial master; the interface table feeds this table via the transaction processor.
• MTL_TRANSACTION_LOTS_INTERFACE — references this table through SERIAL_TRANSACTION_TEMP_ID (documented FK).
• CSD_PRODUCT_TRANSACTIONS — referenced by the PRODUCT_TRANSACTION_ID foreign key.
• MTL_TRANSACTIONS_INTERFACE — the parent transaction staging table whose lines carry the serial context.
• MTL_TRANSACTION_LOTS_TEMP — temporary lot storage used alongside serials in the same processor run.
• INV_TXN_MANAGER_PUB / INV_TXN_MANAGER — the transaction processor APIs that consume interface rows.
• MTL_MATERIAL_TRANSACTIONS — material transaction records validated against the interface input.
• MTL_SERIAL_NUMBERS_INTERFACE_PUB — programmatic interface used to populate staging rows.

Together these objects form the serial transaction-processing chain from submission through staging, validation, and permanent storage.