Search Results mtl_system_items_fvl




DeepSeek eTRM - INV Tables and Views in Oracle EBS 12.1.1/12.2.2

Overview

The DeepSeek Enterprise Technical Reference Manual (eTRM) for Oracle E-Business Suite (EBS) provides a comprehensive documentation of Inventory (INV) module tables and views in releases 12.1.1 and 12.2.2. These database objects form the foundation for inventory management, item master, material transactions, and warehouse operations.

Key Inventory Tables

1. Core Item Master Tables

  • MTL_SYSTEM_ITEMS_B: Primary table storing item definitions with attributes like description, UOM, and item status.
  • MTL_ITEM_REVISIONS_B: Tracks revision-controlled items with effectivity dates.
  • MTL_ITEM_CATEGORIES: Stores category assignments for items.

2. Inventory Organization Structure

  • MTL_PARAMETERS: Organization-level inventory parameters.
  • MTL_SECONDARY_INVENTORIES: Sub-inventory definitions within organizations.
  • MTL_ITEM_LOCATIONS: Locator master data for warehouse management.

3. Transaction Processing

  • MTL_MATERIAL_TRANSACTIONS: Core transaction table recording all inventory movements.
  • MTL_TRANSACTION_TYPES: Definition of transaction types used in the system.
  • MTL_TRANSACTION_LOTS: Lot number tracking for lot-controlled items.

4. On-Hand Quantities

  • MTL_ONHAND_QUANTITIES: Current inventory balances by subinventory/locator.
  • MTL_ONHAND_QUANTITIES_DETAIL: Detailed breakdown of on-hand quantities.

Key Inventory Views

1. Reporting Views

  • MTL_ITEMS_V: Comprehensive item master view combining multiple base tables.
  • MTL_ITEM_REVISIONS_V: Enhanced revision information view.
  • MTL_STOCK_LOCATORS_V: Locator information with organization context.

2. Transaction Views

  • MTL_MATERIAL_TRANSACTIONS_V: Transaction data with descriptive information.
  • MTL_TRANSACTION_LOTS_V: Lot information view for transactions.

3. On-Hand Views

  • MTL_ONHAND_QUANTITIES_V: Current inventory view with item details.
  • MTL_ONHAND_QUANTITIES_DETAIL_V: Detailed on-hand quantities view.

Technical Considerations

  • The eTRM provides complete data dictionary information including column definitions, constraints, and indexes.
  • Views typically join base tables with descriptive flexfields and reference data.
  • Key relationships are maintained through foreign key constraints like ORGANIZATION_ID and INVENTORY_ITEM_ID.
  • Partitioning strategies differ between 12.1.1 and 12.2.2, with 12.2.2 offering more partitioning options.

Integration Points

  • Strong integration with WMS tables in advanced warehouse implementations.
  • Interfaces with Cost Management (CST) for inventory valuation.
  • Connections to Order Management (OE) for shipping transactions.
  • Integration with Purchasing (PO) for receipt processing.

Customization Guidance

  • Direct DML on base tables is generally discouraged.
  • Public APIs should be used for data modifications.
  • Custom extensions should leverage descriptive flexfields where possible.
  • New indexes should be carefully evaluated for performance impact.

Version Differences

  • 12.2.2 introduces additional columns for cloud-specific features.
  • New views were added in 12.2.2 to support REST services.
  • Partitioning enhancements in 12.2.2 improve performance for large implementations.
The DeepSeek eTRM serves as an essential technical resource for developers, DBAs, and implementers working with Oracle Inventory, providing the detailed metadata needed for custom reports, interfaces, and extensions.