Search Results mtl_item_sub_defaults_pk

Overview

MTL_ITEM_SUB_DEFAULTS is an Inventory (INV) module table that stores the item-level default subinventory assignments used during receiving and shipping transactions in Oracle EBS 12.1.1 and 12.2.2. Each row associates an inventory item within a specific organization with a subinventory code and a default type, allowing the system to pre-populate subinventory fields on material movement, receipt, and shipment forms. The table is owned by the INV schema and is classified as VALID in the ETRM repository for release 12.2.2.

From a dimensional modeling perspective, the mined relationship structure suggests a link classification within a Data Vault heuristic. The table resolves many-to-many style relationships between items and subinventories, with transactional columns such as LAST_UPDATE_DATE and REQUEST_ID providing satellite-like audit context. This classification is offered as a modeling suggestion rather than a documented Oracle design intent.

Key Information Stored

The physical schema documents thirteen columns. The most significant are the identifying and reference attributes that define each default assignment.

• INVENTORY_ITEM_ID — surrogate reference to the item in MTL_SYSTEM_ITEMS_B; part of the composite primary key.
• ORGANIZATION_ID — the inventory organization context; part of the composite primary key.
• SUBINVENTORY_CODE — the target subinventory; part of the primary key and a foreign key to MTL_SECONDARY_INVENTORIES.
• DEFAULT_TYPE — distinguishes the purpose of the default (for example, receiving versus shipping); part of the primary key.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program and request tracking columns.

The surrogate primary key is MTL_ITEM_SUB_DEFAULTS_PK, defined across INVENTORY_ITEM_ID, ORGANIZATION_ID, SUBINVENTORY_CODE, and DEFAULT_TYPE. A separate unique index, MTL_ITEM_SUB_DEFAULTS_U1 on INVENTORY_ITEM_ID, ORGANIZATION_ID, and DEFAULT_TYPE, represents a business-key candidate that enforces a single default subinventory per item, organization, and default type.

Common Use Cases and Queries

Typical scenarios include identifying the default receiving or shipping subinventory for an item, auditing item setup consistency across organizations, and validating subinventory assignments prior to transaction interfaces.

A representative query retrieves the default subinventory for an item in a given organization:

SELECT subinventory_code, default_type FROM mtl_item_sub_defaults WHERE inventory_item_id = :item_id AND organization_id = :org_id;

A reporting pattern joins to MTL_SYSTEM_ITEMS_B and MTL_SECONDARY_INVENTORIES to expose item descriptions and subinventory names alongside the default assignments. Exception reporting can identify items lacking a receiving default or referencing inactive subinventories.

Related Objects

• MTL_SYSTEM_ITEMS_B — referenced on INVENTORY_ITEM_ID and ORGANIZATION_ID to resolve item identity.
• MTL_SECONDARY_INVENTORIES — referenced on SUBINVENTORY_CODE and ORGANIZATION_ID to validate subinventory definitions.
• MTL_ITEM_LOCATIONS and related receiving tables that consume the configured defaults during transactions.
• Receiving and shipping open interfaces (for example, RCV_* interface tables) that read these defaults when processing inbound and outbound documents.

Together these relationships place MTL_ITEM_SUB_DEFAULTS at the intersection of item master setup and subinventory configuration, making it a key reference table for both transactional processing and inventory setup reporting.