Search Results mtl_cycle_count_entries




Overview
Explore in AI Chat

Overview

The MTL_CYCLE_COUNT_ENTRIES table is a core transactional entity within the Oracle E-Business Suite Inventory (INV) module, specifically for cycle counting operations in releases 12.1.1 and 12.2.2. It defines and stores the detailed, line-level records for each individual item count performed as part of a scheduled cycle count. Each entry represents a single counting instance for a specific item, revision, lot, and location combination. The table's primary role is to serve as the persistent data store for all user-entered count quantities, adjustments, and related data during the cycle count process, linking the high-level schedule (MTL_CYCLE_COUNT_HEADERS) to the physical inventory transactions that result from count adjustments.

Key Information Stored

The table's structure captures the full context and result of a count. Its primary key is CYCLE_COUNT_ENTRY_ID. Essential columns include CYCLE_COUNT_HEADER_ID, linking to the parent schedule; INVENTORY_ITEM_ID and ORGANIZATION_ID, identifying the counted item; and SUBINVENTORY and LOCATOR_ID, defining the storage location. Critical transactional columns are COUNT_QUANTITY (the quantity entered by the counter), SYSTEM_QUANTITY (the on-hand quantity per the system at count time), and ADJUSTMENT_QUANTITY (the calculated difference). The table also tracks status (e.g., 'OPEN', 'COMPLETED'), approval details, and holds foreign keys for lot (LOT_NUMBER), revision (REVISION), cost group (COST_GROUP_ID), license plates (PARENT_LPN_ID, OUTERMOST_LPN_ID), adjustment accounts (INVENTORY_ADJUSTMENT_ACCOUNT), and adjustment reasons (TRANSACTION_REASON_ID).

Common Use Cases and Queries

This table is central to cycle count reporting, reconciliation, and audit processes. Common queries involve identifying discrepancies, tracking count progress, and analyzing count history. A fundamental query retrieves all pending adjustments for approval by selecting entries where ADJUSTMENT_QUANTITY is not zero and the entry status is 'COMPLETED' but not yet posted. Analysts frequently join this table with MTL_SYSTEM_ITEMS_B and MTL_ITEM_LOCATIONS to generate detailed variance reports showing item number, description, location, system quantity, counted quantity, and variance. Another critical use case is supporting the cycle count interface (MTL_CC_ENTRIES_INTERFACE), where external data is validated and used to create or update records in this base table.

  • Sample SQL: To find significant variances for a specific cycle count header:
    SELECT msi.segment1 item_code, mcc.count_quantity, mcc.system_quantity, (mcc.count_quantity - mcc.system_quantity) variance FROM mtl_cycle_count_entries mcc, mtl_system_items_b msi WHERE mcc.cycle_count_header_id = :p_header_id AND mcc.inventory_item_id = msi.inventory_item_id AND mcc.organization_id = msi.organization_id AND ABS(mcc.count_quantity - mcc.system_quantity) > 0;

Related Objects

As indicated by its extensive foreign key relationships, MTL_CYCLE_COUNT_ENTRIES is a central hub connected to numerous master and transactional tables. Its primary parent is MTL_CYCLE_COUNT_HEADERS. It references master data tables such as MTL_SYSTEM_ITEMS_B (items), MTL_SECONDARY_INVENTORIES (subinventories), MTL_ITEM_LOCATIONS (locators), MTL_LOT_NUMBERS, MTL_ITEM_REVISIONS_B, and MTL_PARAMETERS (organizations). For financial and logistical context, it links to GL_CODE_COMBINATIONS (adjustment accounts), CST_COST_GROUPS, WMS_LICENSE_PLATE_NUMBERS, and MTL_TRANSACTION_REASONS. Crucially, it is referenced by the interface table MTL_CC_ENTRIES_INTERFACE, which feeds it data, and by MTL_CC_SCHED_COUNT_XREFS, which helps manage scheduled counts. The data in this table ultimately drives the creation of inventory adjustment transactions in MTL_MATERIAL_TRANSACTIONS.