Results for “inv_diag_msn_temp”

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

Overview

INV_DIAG_MSN_TEMP is a transient staging table owned by the INV (Inventory) schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It supports the serial number diagnostics and "missing serial number" (MSN) reconciliation utilities used by inventory administrators to identify and repair inconsistencies between on-hand serialized inventory records and their corresponding transaction history. The table holds a working snapshot of serial number attributes—current status, subinventory, locator, lot, and last transaction context—that the diagnostic program uses to compare the physical on-hand state against the recorded lifecycle events of each serialized unit.

Consistent with its transient, utility-oriented purpose, the heuristic Data Vault classification mined from the foreign key structure is standalone. This should be treated as a modeling suggestion rather than a formal architecture: the table is not a persistent hub, link, or satellite, and it is not intended as a source for dimensional data warehousing. Rows are populated, evaluated, and purged within the execution window of the diagnostic routine.

Key Information Stored

The documented physical schema at 12.2.2 contains 20 columns. The most significant include:

• SERIAL_NUMBER — the serialized unit identifier under diagnosis; the primary business identifier for the row.
• ORG_ID — the inventory organization in which the serial number resides; required for multi-org isolation.
• INVENTORY_ITEM_ID — the item associated with the serial number, keyed to MTL_SYSTEM_ITEMS_B.
• CURRENT_STATUS — the serial number's present status (for example, defined, in transit, or resolved).
• CURRENT_SUBINVENTORY_CODE and CURRENT_LOCATOR_ID — the physical storage position, enabling comparison of on-hand location versus recorded movement history.
• LOT_NUMBER — the lot association for lot-serial controlled items.
• LAST_TRANSACTION_ID, LAST_TXN_SOURCE_TYPE_ID, and LAST_TXN_SOURCE_ID — the last recorded transaction and its source document, which anchor the audit trail.
• LAST_RECEIPT_ISSUE_TYPE — indicates whether the most recent movement was a receipt or an issue.
• COST_GROUP_ID — the only documented foreign key, joining to CST_COST_GROUPS.
• LPN_ID — the license plate number of the containing handling unit.
• PARENT_SERIAL_NUMBER and PARENT_ITEM_ID — support parent-child serial structures for component serials.

The metadata documents no explicit surrogate primary key column; SERIAL_NUMBER combined with ORG_ID and INVENTORY_ITEM_ID typically serves as the practical business key. The mark columns (GROUP_MARK_ID, LINE_MARK_ID, LOT_LINE_MARK_ID) are processing control identifiers used internally by the diagnostic algorithm rather than business attributes.

Common Use Cases and Queries

This table is queried almost exclusively during or after execution of serial number diagnostic programs. Typical patterns include locating orphaned or duplicate serial records within an organization, verifying that the last transaction reflects the expected movement, and reconciling subinventory or locator mismatches.

A representative query to surface suspect serials follows:

• SELECT serial_number, org_id, inventory_item_id, current_status, current_subinventory_code, last_transaction_id FROM inv_diag_msn_temp WHERE org_id = :org_id ORDER BY serial_number;

Reporting use cases include producing exception lists for serial numbers with no valid last transaction, and joining to CST_COST_GROUPS via COST_GROUP_ID to review costing context for affected units. Because rows are temporary, any extract intended for reconciliation evidence should be captured before the diagnostic process completes.

Related Objects

The table's dependencies are limited, reflecting its utility nature:

• CST_COST_GROUPS — joined on COST_GROUP_ID (the sole documented foreign key).
• MTL_SERIAL_NUMBERS — the authoritative serial number master, matched on SERIAL_NUMBER, INVENTORY_ITEM_ID, and ORG_ID.
• MTL_MATERIAL_TRANSACTIONS — matched on LAST_TRANSACTION_ID to retrieve movement detail.
• MTL_ONHAND_QUANTITIES_DETAIL — compared against CURRENT_SUBINVENTORY_CODE and CURRENT_LOCATOR_ID.
• MTL_SYSTEM_ITEMS_B — resolved on INVENTORY_ITEM_ID for item descriptions.
• WIP_ENTITIES — referenced through ORIGINAL_WIP_ENTITY_ID for work-in-process origin tracking.

No public APIs operate directly on this table; the supported interface is the Inventory serial number diagnostic concurrent program that populates and consumes it.