Search Results mtl_item_locations




Overview
Explore in AI Chat

Overview

The MTL_ITEM_LOCATIONS table is a core data object within the Oracle E-Business Suite (EBS) Inventory (INV) module. It serves as the master repository for all stock locator definitions within an organization. A locator is a physical or logical subdivision within a subinventory used to track the precise storage location of items. This table is fundamental to granular inventory control, enabling precise tracking of material quantities by location, supporting cycle counting, and facilitating accurate material transactions such as receipts, issues, and transfers. Its integrity is critical for the entire inventory management process in both EBS 12.1.1 and 12.2.2.

Key Information Stored

The table's primary key is a composite of INVENTORY_LOCATION_ID and ORGANIZATION_ID, ensuring uniqueness of locators within each inventory organization. Key columns include INVENTORY_LOCATION_ID (the unique system identifier), SEGMENT1-20 (which store the flexfield structure of the locator code), and DESCRIPTION. It links to other critical master data via foreign keys: ORGANIZATION_ID to MTL_PARAMETERS, SUBINVENTORY_CODE to MTL_SECONDARY_INVENTORIES, STATUS_ID to MTL_MATERIAL_STATUSES_B for locator holds, and INVENTORY_ACCOUNT_ID to GL_CODE_COMBINATIONS for financial posting. Other important columns control locator behavior, such as DISABLE_DATE, PICKING_ORDER, and DROPPING_ORDER.

Common Use Cases and Queries

This table is central to queries for inventory on-hand reporting, transaction history analysis, and locator maintenance. Common scenarios include identifying all active locators within a specific subinventory, finding the default locator for an item, and reporting empty locators. A typical query to list locators for a subinventory would be:

  • SELECT mil.segment1, mil.segment2, mil.description
  • FROM mtl_item_locations mil
  • WHERE mil.organization_id = :org_id
  • AND mil.subinventory_code = :subinv_code
  • AND NVL(mil.disable_date, SYSDATE+1) > SYSDATE
  • ORDER BY mil.picking_order;

It is also heavily referenced in cycle count entry listings (MTL_CYCLE_COUNT_ENTRIES) and material transaction inquiries (MTL_MATERIAL_TRANSACTIONS) to trace item movement by precise location.

Related Objects

As indicated by the foreign key relationships, MTL_ITEM_LOCATIONS is a hub table with extensive dependencies. Key parent tables include MTL_PARAMETERS (organization), MTL_SECONDARY_INVENTORIES (subinventory), and GL_CODE_COMBINATIONS (account). Crucially, it is referenced as a parent by numerous transactional and setup tables. These include MTL_MATERIAL_TRANSACTIONS (every material move), MTL_CYCLE_COUNT_ENTRIES, MTL_DEMAND, and BOM_OPERATIONAL_ROUTINGS (for completion locators). For programmatic access and maintenance, developers typically use the INV_LOCATOR_PUB package rather than direct DML on this table to maintain business rule integrity.