Search Results mtl_item_loc_defaults_u1

Overview

INV.MTL_ITEM_LOC_DEFAULTS is an Oracle EBS Inventory (INV) transactional table that stores optional default locators and subinventories used for shipping and receiving purposes. The table associates a specific inventory item within a specific organization with a preferred subinventory code, and optionally a preferred locator, distinguished by a default type code that indicates whether the record controls the default shipping location or the default receiving location. Because the same item may require separate shipping and receiving preferences, the DEFAULT_TYPE column differentiates these two logical roles within one physical table.

Within the Oracle EBS 12.1.1 and 12.2.2 data model, MTL_ITEM_LOC_DEFAULTS functions as a link (junction) object. The heuristic Data Vault classification derived from its foreign key structure is link, since it resolves many-to-many associations between inventory items, organizations, subinventories, and locators rather than holding descriptive attributes as an independent hub or satellite. It is populated and maintained by the Inventory application and its associated setup and transaction flows, and it works in conjunction with MTL_ITEM_SUB_DEFAULTS, which holds related subinventory-level defaults. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its unique index is stored in APPS_TS_TX_IDX, reflecting a typical high-volume transactional disposition.

Key Information Stored

MTL_ITEM_LOC_DEFAULTS contains fourteen documented columns. The most significant are:

• INVENTORY_ITEM_ID — Numeric identifier of the inventory item; part of the primary key and of the unique business key.
• ORGANIZATION_ID — Numeric identifier of the inventory organization in which the default applies.
• SUBINVENTORY_CODE — VARCHAR2(10) code of the subinventory that serves as the default target or source.
• LOCATOR_ID — Numeric identifier of the specific locator within the subinventory; optional in practice because not all subinventories are locator-controlled.
• DEFAULT_TYPE — Numeric code indicating whether the record represents the default shipping or the default receiving subinventory/locator.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard Who columns providing audit and concurrency information.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Standard concurrent program columns identifying the process that last changed the row.

The documented surrogate primary key is MTL_ITEM_LOC_DEFAULTS_PK on (INVENTORY_ITEM_ID, ORGANIZATION_ID, LOCATOR_ID, DEFAULT_TYPE, SUBINVENTORY_CODE). A separate unique index, MTL_ITEM_LOC_DEFAULTS_U1, is defined on the business-key candidate set (INVENTORY_ITEM_ID, ORGANIZATION_ID, SUBINVENTORY_CODE, DEFAULT_TYPE). Note that the unique index omits LOCATOR_ID, which reinforces the interpretation that LOCATOR_ID is the optional attribute and that one default subinventory/type combination is permitted per item and organization.

Common Use Cases and Queries

The primary reporting scenario retrieves the default shipping or receiving locator for an item so that picking, put-away, or receiving logic can pre-populate a destination. Because DEFAULT_TYPE is a coded value, joins to the appropriate lookup are usually required to render a meaningful label.

A representative query that lists all defaults for a given item and organization, resolving the subinventory and locator:

• SELECT d.inventory_item_id, d.organization_id, d.subinventory_code, d.locator_id, d.default_type FROM inv.mtl_item_loc_defaults d WHERE d.inventory_item_id = :item_id AND d.organization_id = :org_id ORDER BY d.default_type;

To isolate only the shipping default, filter on DEFAULT_TYPE with the value that the business assigns to shipping; to isolate receiving, use the complementary value. Where the inventory item is locator-controlled, join LOCATOR_ID to INV.MTL_ITEM_LOCATIONS to obtain the physical locator fields; where it is not, LOCATOR_ID may be null and the SUBINVENTORY_CODE alone governs. A common reconciliation report compares MTL_ITEM_LOC_DEFAULTS against MTL_ITEM_SUB_DEFAULTS to detect items configured with conflicting or missing setup across the two default tables. Auditors frequently extract the Who columns to determine when and by which concurrent program a default was last modified.

Related Objects

The foreign key relationships documented for this table identify the principal parent objects joined in practice:

• INV.MTL_SYSTEM_ITEMS_B — referenced through INVENTORY_ITEM_ID; supplies item attributes such as description, unit of measure, and locator control flag.
• INV.MTL_ITEM_LOCATIONS — referenced through LOCATOR_ID; provides the physical locator definition within the subinventory.
• INV.MTL_SECONDARY_INVENTORIES — referenced through SUBINVENTORY_CODE; supplies subinventory attributes including locator control and default material status.
• INV.MTL_ITEM_SUB_DEFAULTS — companion object holding subinventory-level defaults; used together with this table to validate complete item/warehouse setup.
• INV.MTL_ITEM_LOC_DEFAULTS# — the underlying editioned base object referenced by this table in an upgraded 12.2.2 environment.

No other database objects are documented as being referenced by MTL_ITEM_LOC_DEFAULTS beyond these dependencies, so joins to item, locator, and subinventory master tables, together with the companion MTL_ITEM_SUB_DEFAULTS object, constitute the complete practical integration surface for reporting and validation.