Search Results mtl_mfg_part_numbers




Overview
Explore in AI Chat

Overview

The MTL_MFG_PART_NUMBERS table is a core data object within the Oracle E-Business Suite (EBS) Inventory (INV) module. Its primary function is to define and maintain associations between an organization's internal inventory items and the specific part numbers assigned to those items by their manufacturers. This table is essential for managing procurement, supplier management, and item cross-referencing, enabling businesses to link their internal catalog to the external part identifiers used by vendors and supply chains.

Key Information Stored

The table stores the relationship between an item within a specific inventory organization and a manufacturer's part identifier. Its structure is defined by a primary key and several foreign key relationships. The primary key is a composite of MANUFACTURER_ID and MFG_PART_NUM, ensuring uniqueness for a given manufacturer's part number. Key columns include INVENTORY_ITEM_ID and ORGANIZATION_ID, which together identify the internal item, and MANUFACTURER_ID, which references the manufacturer entity. The MFG_PART_NUM column stores the actual alphanumeric part identifier provided by the manufacturer. The table's integrity is enforced through foreign keys to MTL_SYSTEM_ITEMS_B (for the item), MTL_MANUFACTURERS (for the manufacturer), and MTL_PARAMETERS (for the organization).

Common Use Cases and Queries

This table is central to processes requiring a translation between internal and external part numbers. Common use cases include generating supplier-specific purchase orders, performing supplier item comparisons, and supporting engineering or maintenance teams in identifying replacement parts. A typical query retrieves all manufacturer part numbers for a specific internal item to understand sourcing options:

  • SELECT mmpn.mfg_part_num, mm.manufacturer_name FROM mtl_mfg_part_numbers mmpn, mtl_manufacturers mm WHERE mmpn.inventory_item_id = :item_id AND mmpn.organization_id = :org_id AND mmpn.manufacturer_id = mm.manufacturer_id;

Conversely, to find the internal item associated with a known manufacturer part number for a given supplier:

  • SELECT msib.segment1 item_code FROM mtl_mfg_part_numbers mmpn, mtl_system_items_b msib WHERE mmpn.mfg_part_num = :supplier_part_num AND mmpn.manufacturer_id = :supplier_id AND mmpn.inventory_item_id = msib.inventory_item_id AND mmpn.organization_id = msib.organization_id;

Related Objects

As indicated by its foreign key constraints, MTL_MFG_PART_NUMBERS has direct dependencies on several fundamental Inventory tables. MTL_SYSTEM_ITEMS_B is the master table for all inventory items. MTL_MANUFACTURERS stores information about supplier manufacturers. MTL_PARAMETERS defines inventory organizations. This table is also closely related to the purchasing and supplier management functionalities, where the manufacturer part number data is utilized in sourcing and procurement documents. APIs and user interfaces for managing item supplier associations, such as those found in the Item Master and Supplier Catalog, will read from and write to this table.