Search Results mtl_short_chk_temp_u1

Overview

INV.MTL_SHORT_CHK_TEMP is a transient staging table in the Oracle E-Business Suite Inventory (INV) schema. It stores the temporary results of material shortage queries executed by the material shortage check process. Each execution of a shortage query is identified by a unique SEQ_NUM value, allowing concurrent query sessions to coexist within the same table without collision. Once the result set for a given sequence number has been consumed or is no longer required, the corresponding rows are purged, keeping the table small and self-maintaining.

The table resides in the APPS_TS_INTERFACE tablespace with a PCT Free of 10, which is consistent with its role as an interface and temporary working area rather than a permanent transactional store. In ETRM 12.1.1 and 12.2.2, its physical schema documents 13 columns and two indexes. From a Data Vault modeling perspective, the heuristic classification is a link: the table records associations between an organization/item combination and a demand source (object header and detail), qualified by a sequence number, rather than describing a persistent business entity.

Key Information Stored

The table captures the open demand for inventory items at the moment a shortage query is run. The most significant columns are:

• SEQ_NUM — The sequence number that identifies a single query result set. It is also the leading column of the nonunique index MTL_SHORT_CHK_TEMP_N1 and the primary purge key.
• ORGANIZATION_ID — The inventory organization in which the shortage was evaluated; foreign key to MTL_PARAMETERS.
• INVENTORY_ITEM_ID — The item under shortage analysis; foreign key to MTL_SYSTEM_ITEMS_B.
• QUANTITY_OPEN — The open demand quantity for the item.
• UOM_CODE — The unit of measure associated with QUANTITY_OPEN.
• OBJECT_TYPE — The classification of the demand source.
• OBJECT_ID — The header identifier of the demand source.
• OBJECT_DETAIL_ID — The line identifier of the demand source, where applicable.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Who audit columns applied by the concurrent program that populates the table.

The documented primary key is MTL_SHORT_CHK_TEMP_PK (SEQ_NUM, INVENTORY_ITEM_ID, OBJECT_TYPE, OBJECT_ID, OBJECT_DETAIL_ID). The unique index MTL_SHORT_CHK_TEMP_U1 covers the same set of columns in a different leading order: (ORGANIZATION_ID, INVENTORY_ITEM_ID, OBJECT_TYPE, OBJECT_ID, OBJECT_DETAIL_ID, SEQ_NUM). Together these define the business-key candidate that guarantees a demand line is recorded once per query result set.

Common Use Cases and Queries

The table is consumed by the shortage check concurrent process and by any diagnostic or reconciliation query that needs to inspect the intermediate demand picture. A typical pattern first isolates the latest sequence number, then joins back to the item master and organization parameters:

• List the items short in a given organization: SELECT inventory_item_id, quantity_open FROM inv.mtl_short_chk_temp WHERE seq_num = :seq AND organization_id = :org;
• Correlate a shortage to its demand source by joining OBJECT_ID and OBJECT_DETAIL_ID to the appropriate demand table for the given OBJECT_TYPE.
• Reconcile total open demand per sequence: SELECT organization_id, inventory_item_id, SUM(quantity_open) FROM inv.mtl_short_chk_temp WHERE seq_num = :seq GROUP BY organization_id, inventory_item_id;
• Identify stale result sets prior to cleanup by selecting distinct SEQ_NUM values with MAX(creation_date).

Because rows are purged after use, the table is unsuitable for historical shortage reporting; it should be treated as an in-flight working set only.

Related Objects

The following objects are the most significant dependencies and references:

• MTL_PARAMETERS — joined on ORGANIZATION_ID to supply organization context.
• MTL_SYSTEM_ITEMS_B — joined on INVENTORY_ITEM_ID to resolve item attributes such as description, UOM, and planner.
• MTL_SHORT_CHK_TEMP_U1 — the unique business-key index, useful when defining a surrogate key strategy.
• MTL_SHORT_CHK_TEMP_N1 — the nonunique index on SEQ_NUM that supports the dominant purge and selection access path.
• MTL_SHORT_CHK_TEMP_PK — the primary key constraint enforcing row uniqueness within a sequence.
• APPS.MTL_SHORT_CHK_TEMP — the APPS-layer synonym or view through which application code accesses the base table.

Applications should not write directly to this table; insertion and purging are performed by the shortage check program that owns the SEQ_NUM lifecycle.