Search Results mtl_abc_compiles




Overview
Explore in AI Chat

Overview

The MTL_ABC_COMPILES table is a core data repository within the Oracle E-Business Suite Inventory (INV) module. It stores the detailed, item-level results generated by the ABC (Activity Based Classification) Compilation process. This process analyzes inventory transaction history to classify items into value-based categories—typically A (high value), B (medium value), and C (low value)—based on their cumulative usage value. The table's role is to persist the specific classification assignment, calculated ranking, and associated metrics for each inventory item within a given compile run, enabling businesses to implement targeted inventory control policies and reporting.

Key Information Stored

The table's primary key, consisting of INVENTORY_ITEM_ID and COMPILE_ID, ensures unique identification of an item's classification within a specific compilation. Key columns include COMPILE_ID, which links to the control record in MTL_ABC_COMPILE_HEADERS; INVENTORY_ITEM_ID and ORGANIZATION_ID, identifying the classified item; and SECONDARY_INVENTORY, specifying the subinventory context. Critical calculated data is held in columns such as ABC_CLASS_ID (the assigned category), RANK (the item's positional rank based on descending usage value), QUANTITY, and USAGE_VALUE (the monetary value used for classification). This structure provides a complete historical record of each ABC analysis.

Common Use Cases and Queries

The primary use case is reporting and analysis post-compilation to review classification results. Common queries involve joining to item and classification master tables to produce readable reports. For example, to list all 'A' class items from the latest compile for a specific organization:

  • SELECT msib.segment1 item_code,
          msib.description,
          mac.rank,
          mac.usage_value
    FROM mtl_abc_compiles mac,
         mtl_system_items_b msib,
         mtl_abc_classes mcl
    WHERE mac.inventory_item_id = msib.inventory_item_id
    AND mac.organization_id = msib.organization_id
    AND mac.abc_class_id = mcl.abc_class_id
    AND mac.compile_id = (SELECT MAX(compile_id) FROM mtl_abc_compile_headers WHERE organization_id = 123)
    AND mac.organization_id = 123
    AND mcl.class_code = 'A'
    ORDER BY mac.rank;

This data is also foundational for cycle counting and replenishment processes that use ABC class as a key parameter.

Related Objects

MTL_ABC_COMPILES is centrally connected to several key Inventory master and transaction tables through documented foreign key relationships:

  • MTL_ABC_COMPILE_HEADERS: Linked via COMPILE_ID. The header table stores the compile parameters (e.g., date, name, cutoff values), while MTL_ABC_COMPILES stores the detailed results.
  • MTL_SYSTEM_ITEMS_B: Linked via INVENTORY_ITEM_ID and ORGANIZATION_ID. This provides descriptive item information (item code, description) for the classified items.
  • MTL_SECONDARY_INVENTORIES: Linked via SECONDARY_INVENTORY and ORGANIZATION_ID. This relationship provides subinventory details when the compilation is performed at that level.

These relationships are essential for constructing accurate joins in any reporting or data extraction involving ABC classification history.