Search Results mtl_short_chk_temp_pk

Overview

MTL_SHORT_CHK_TEMP is an Inventory (INV) module table owned by the INV schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented description is the "Shortage Check Temporary Table." As the name implies, the table functions as a transient, session-scoped workspace used by Oracle Inventory's shortage-check logic, most notably the material shortage detection performed during pick release, sales order reservation, move order processing, and other demand satisfaction routines. Rather than persisting a durable business fact, MTL_SHORT_CHK_TEMP aggregates candidate demand lines for which available supply is being evaluated, allowing the shortage check program to resolve quantities, units of measure, and object references before reporting shortages to the calling process.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure is a link. The table does not represent an independent business entity (a hub); instead it interconnects an organization (via ORGANIZATION_ID) and an inventory item (via INVENTORY_ITEM_ID) with the business object that is being checked for shortage. It behaves as an association/transaction-style table capturing the relationship between demand, supply context, and item inventory.

Key Information Stored

The table contains 13 documented columns. The primary key MTL_SHORT_CHK_TEMP_PK is composed of five columns and acts as the surrogate/resolution key:

• SEQ_NUM – Sequence number distinguishing individual rows within a check run.
• OBJECT_TYPE – Identifies the type of business object under evaluation (for example, a demand or reservation object).
• OBJECT_ID – Identifier of the referenced business object.
• OBJECT_DETAIL_ID – Further qualifying identifier for the object detail line.

The unique index MTL_SHORT_CHK_TEMP_U1 (ORGANIZATION_ID, INVENTORY_ITEM_ID, OBJECT_TYPE, OBJECT_ID, OBJECT_DETAIL_ID, SEQ_NUM) is the documented business-key candidate, adding the organization and item context to the PK columns. The remaining documented attributes carry the measurement and audit information:

• ORGANIZATION_ID – Inventory organization in which the shortage check is performed; foreign key to MTL_PARAMETERS.
• INVENTORY_ITEM_ID – The item being checked; foreign key to MTL_SYSTEM_ITEMS_B.
• QUANTITY_OPEN – The open/required quantity pending against the demand or object line.
• UOM_CODE – Unit of measure for the open quantity.
• Standard audit columns: LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE.

Common Use Cases and Queries

Because the table is temporary by design, it is most frequently inspected for troubleshooting rather than reporting. Typical scenarios include diagnosing why pick release flagged a shortage, verifying which demand lines were resolved, and reconciling QUANTITY_OPEN against on-hand availability. A representative query joining the item and organization masters is:

SELECT t.ORGANIZATION_ID, t.INVENTORY_ITEM_ID, t.OBJECT_TYPE,
       t.OBJECT_ID, t.OBJECT_DETAIL_ID, t.QUANTITY_OPEN, t.UOM_CODE
  FROM   INV.MTL_SHORT_CHK_TEMP t
 WHERE  t.ORGANIZATION_ID = :org_id
   AND  t.INVENTORY_ITEM_ID = :item_id;

Reporting use cases include measuring shortage incidence by item or organization, analyzing recurring shortage objects, and validating that the shortage engine correctly resolved demand quantities. Since rows are temporary, any extract must be captured while the check run is active or immediately after, before the session cleans up the workspace.

Related Objects

Significant related objects are anchored in the documented foreign keys and the broader shortage-check process:

• MTL_PARAMETERS – joined on ORGANIZATION_ID; supplies organization-level inventory parameters.
• MTL_SYSTEM_ITEMS_B – joined on INVENTORY_ITEM_ID (and ORGANIZATION_ID); the item master definition.
• MTL_ONHAND_QUANTITIES / MTL_ONHAND_QUANTITIES_DETAIL – supply reference used to determine shortage.
• MTL_RESERVATIONS – reservation demand lines evaluated by the shortage check.
• MTL_DEMAND – pending demand driving the shortage calculation.
• MTL_MATERIAL_TRANSACTIONS – transaction context surrounding release and issue.
• WIP_REQUIREMENT_OPERATIONS / WIP_MOVE_ORDERS – manufacturing demand sources that trigger shortage checks.

These relationships confirm the table's role as a link between organization, item, and the object under shortage evaluation.