Search Results date_received




Overview
Explore in AI Chat

Overview

The MTL_ONHAND_QUANTITIES view is a critical data object within the Oracle E-Business Suite Inventory (INV) module for versions 12.1.1 and 12.2.2. It provides a consolidated and filtered perspective of on-hand material quantities for an inventory organization. As defined in its documentation, it is a "View of items owned, planned and physically belonging to the same inventory organization." Its primary role is to serve as a reliable source for querying the current, non-consigned inventory stock. The view achieves this by selecting data from the underlying detail table, MTL_ONHAND_QUANTITIES_DETAIL, and applying a specific filter (WHERE MOQD.IS_CONSIGNED = 2) to exclude consigned inventory, thereby presenting only the stock that is owned by the organization.

Key Information Stored

The view exposes a comprehensive set of columns that describe the identity, location, quantity, and transactional history of inventory items. Key columns include INVENTORY_ITEM_ID and ORGANIZATION_ID for item and org identification, and TRANSACTION_QUANTITY which holds the primary on-hand quantity. The SUBINVENTORY_CODE, LOCATOR_ID, LOT_NUMBER, and REVISION provide detailed stock locational and traceability data. Notably, the view includes two date-related columns pertinent to the user's search: DATE_RECEIVED and ORIG_DATE_RECEIVED. DATE_RECEIVED typically represents the most recent receipt date for that specific stock segment, while ORIG_DATE_RECEIVED may indicate the original receipt date from the initial transaction. Other significant fields include COST_GROUP_ID for costing, PROJECT_ID and TASK_ID for project inventory, and columns for secondary unit of measure quantities.

Common Use Cases and Queries

This view is fundamental for inventory reporting, reconciliation, and data extraction processes. Common use cases include generating real-time stock level reports, calculating available-to-promise (ATP) quantities, supporting cycle count processes, and feeding data into business intelligence dashboards. A typical query to find on-hand quantities for a specific item, filtered by a recent receipt date, would be:

  • SELECT SUBINVENTORY_CODE, LOT_NUMBER, SUM(TRANSACTION_QUANTITY) FROM APPS.MTL_ONHAND_QUANTITIES WHERE INVENTORY_ITEM_ID = 12345 AND ORGANIZATION_ID = 100 AND DATE_RECEIVED > SYSDATE - 30 GROUP BY SUBINVENTORY_CODE, LOT_NUMBER;

Another frequent pattern is joining this view with MTL_SYSTEM_ITEMS_B to get item descriptions or with MTL_SECONDARY_INVENTORIES for subinventory details. Analysts often use the ORIG_DATE_RECEIVED field in queries aimed at aging analysis or First-In-First-Out (FIFO) valuation assessments.

Related Objects

The view has a direct and singular dependency on the MTL_ONHAND_QUANTITIES_DETAIL table, from which it selects all its data. The key relationship is the view's source query on this base table. For practical application, the view is most commonly joined with other Inventory foundation tables using the following key columns:

  • MTL_SYSTEM_ITEMS_B: Joined on INVENTORY_ITEM_ID and ORGANIZATION_ID to retrieve item segments, descriptions, and primary UOM.
  • MTL_SECONDARY_INVENTORIES: Joined on ORGANIZATION_ID and SUBINVENTORY_CODE to get subinventory details.
  • MTL_ITEM_LOCATIONS: Joined on LOCATOR_ID (and implicitly ORGANIZATION_ID) to get locator information.
  • MTL_MATERIAL_TRANSACTIONS: Can be related via CREATE_TRANSACTION_ID or UPDATE_TRANSACTION_ID to trace back to the originating or last modifying transaction.