Results for “mrp_onhand_quantities”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MRP_ONHAND_QUANTITIES is a transactional planning table owned by the MRP schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the on-hand supply picture for items as consumed by a specific plan run, rather than acting as the system of record for inventory balances. During a Master Scheduling/MRP plan compilation, the planning engine extracts current on-hand balances from Oracle Inventory and materializes them into MRP_ONHAND_QUANTITIES, tagged with the plan identifier (COMPILE_DESIGNATOR) that produced them. The table therefore represents a point-in-time snapshot that the MRP, MPS, and DRP engines read when netting supply against demand.

The documented foreign key relationship links MRP_ONHAND_QUANTITIES.INVENTORY_ITEM_ID and ORGANIZATION_ID to MRP_SYSTEM_ITEMS, anchoring each row to a planning item and inventory organization. From a Data Vault modeling perspective, the mined classification is satellite-leaning: the table records descriptive, plan-scoped attributes about an existing business key (the item/organization/plan combination) and does not itself define new hubs or relationships. It is best modeled as a satellite attached to the item-organization-plan hub, with effective dating implied by CREATION_DATE and LAST_UPDATE_DATE.

Key Information Stored

The table contains 16 documented columns. The most significant include:

• INVENTORY_ITEM_ID and ORGANIZATION_ID — the composite business key identifying which item in which inventory organization the row describes; both participate in the foreign key to MRP_SYSTEM_ITEMS.
• COMPILE_DESIGNATOR — the plan identifier that scoped this snapshot, distinguishing rows produced by different plan runs.
• SUB_INVENTORY_CODE — the subinventory from which the quantity was drawn, enabling subinventory-level detail.
• NETTABLE_QUANTITY — on-hand quantity available for planning netting (the supply the engine can consume).
• NONNETTABLE_QUANTITY — on-hand quantity excluded from netting, such as restricted or non-nettable stock.
• PROJECT_ID and TASK_ID — project and task references for project-driven supply.
• PLANNING_GROUP — grouping attribute used to segment planning data.
• TRANSACTION_ID and END_ITEM_UNIT_NUMBER — linkage to the originating inventory transaction and end-item unit.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.

No single surrogate primary key column is documented in the ETRM metadata; the de facto unique key is the combination of INVENTORY_ITEM_ID, ORGANIZATION_ID, COMPILE_DESIGNATOR, and SUB_INVENTORY_CODE (with PROJECT_ID/TASK_ID where applicable).

Common Use Cases and Queries

Typical uses include diagnosing plan results, reconciling planning on-hand to Inventory balances, and building supply reports. A common query pattern retrieves nettable supply for a given plan and item:

• SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID, SUB_INVENTORY_CODE, NETTABLE_QUANTITY, NONNETTABLE_QUANTITY FROM MRP.MRP_ONHAND_QUANTITIES WHERE COMPILE_DESIGNATOR = :plan AND ORGANIZATION_ID = :org;
• Aggregating total nettable supply per item across subinventories for a plan run.
• Comparing NETTABLE_QUANTITY against MRP_SYSTEM_ITEMS attributes to validate planning item setup.
• Filtering by PROJECT_ID/TASK_ID for project-based supply visibility.

Related Objects

The primary documented relationship is to MRP_SYSTEM_ITEMS, joined on INVENTORY_ITEM_ID, ORGANIZATION_ID, and COMPILE_DESIGNATOR. Other significant objects in the same planning schema include MRP_GROSS_REQUIREMENTS, MRP_SCHEDULE_DATES, MRP_RECOMMENDATIONS, and MRP_ITEM_SUPPLIERS, which share COMPILE_DESIGNATOR as the plan scope key. Oracle Inventory tables such as MTL_ONHAND_QUANTITIES_DETAIL serve as the source from which this snapshot is derived, and MTL_SYSTEM_ITEMS_B provides the master item definition.