Search Results mtl_item_loc_defaults_pk

Overview

MTL_ITEM_LOC_DEFAULTS is an Inventory (INV) module table that stores item receiving and shipping locator default values within Oracle E-Business Suite 12.1.1 and 12.2.2. The table acts as a defaulting mechanism that allows warehouse and receiving personnel to predefine which subinventory and locator should be proposed by the system during receiving, shipping, and related material transactions. By binding an inventory item to an organization, a subinventory, and a locator for a specific transaction direction, the table removes the need for manual locator selection at transaction time and enforces consistent material placement across the enterprise.

From a data modeling perspective, the object is best classified heuristically as a link table. It resolves a many-to-many relationship between inventory items (MTL_SYSTEM_ITEMS_B), locators (MTL_ITEM_LOCATIONS), and subinventories (MTL_SECONDARY_INVENTORIES), and it carries no independent descriptive attributes beyond the associative keys and standard audit columns. This makes it a natural bridge in a Data Vault model rather than a hub or satellite.

Key Information Stored

The table comprises 14 documented columns. The most significant are the business keys and the discriminant value that defines the purpose of each row:

• INVENTORY_ITEM_ID — Identifies the item for which a locator default is defined; joins to MTL_SYSTEM_ITEMS_B.
• ORGANIZATION_ID — Scopes the default to a specific inventory organization; participates in every foreign key.
• LOCATOR_ID — The physical locator (row-rack-bin) that will be defaulted; foreign key to MTL_ITEM_LOCATIONS.
• SUBINVENTORY_CODE — The subinventory containing the locator; foreign key to MTL_SECONDARY_INVENTORIES.
• DEFAULT_TYPE — Discriminates the transaction context of the default, distinguishing receiving defaults from shipping defaults.
• Standard audit columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, supporting concurrency, auditing, and concurrent program traceability.

The surrogate primary key is MTL_ITEM_LOC_DEFAULTS_PK, defined across (INVENTORY_ITEM_ID, ORGANIZATION_ID, LOCATOR_ID, DEFAULT_TYPE, SUBINVENTORY_CODE). A separate unique index, MTL_ITEM_LOC_DEFAULTS_U1, spans (INVENTORY_ITEM_ID, ORGANIZATION_ID, SUBINVENTORY_CODE, DEFAULT_TYPE), which is the effective business-key candidate. Because DEFAULT_TYPE appears in both the PK and the unique index, it is the pivot that permits a single item-organization-subinventory combination to hold parallel receiving and shipping defaults pointing to distinct locators.

Common Use Cases and Queries

Typical scenarios include resolving why a particular locator was proposed during a receipt, validating that every stocked item has a shipping default, and auditing defaults after an item organization assignment change.

• Retrieving all locator defaults for an item in an organization:
  SELECT locator_id, subinventory_code, default_type FROM mtl_item_loc_defaults WHERE inventory_item_id = :item_id AND organization_id = :org_id;
• Finding items that lack a receiving default, driving setup remediation reports.
• Joining to MTL_ITEM_LOCATIONS to translate LOCATOR_ID into human-readable segment values for receiving documentation.
• Reconciling defaults against MTL_SECONDARY_INVENTORIES when a subinventory is being disabled.

Because the table is small relative to transaction tables, reporting queries are inexpensive, but joins should always include ORGANIZATION_ID to avoid cross-organization leakage.

Related Objects

• MTL_SYSTEM_ITEMS_B — referenced on INVENTORY_ITEM_ID and ORGANIZATION_ID; the master item definition.
• MTL_ITEM_LOCATIONS — referenced on LOCATOR_ID and ORGANIZATION_ID; supplies locator segments.
• MTL_SECONDARY_INVENTORIES — referenced on SUBINVENTORY_CODE and ORGANIZATION_ID; supplies subinventory attributes.
• MTL_MATERIAL_TRANSACTIONS — the transaction history whose locator values commonly originate from these defaults.
• MTL_TRANSACTION_TYPES — governs which DEFAULT_TYPE values apply to receiving versus shipping flows.

Together these objects form the item-locator defaulting framework used throughout Oracle Inventory receiving and shipping execution.