Search Results gmo_material_undispenses




Overview
Explore in AI Chat

Overview

The GMO_MATERIAL_UNDISPENSES table is a core transactional object within the Oracle Manufacturing Execution System (MES) for Process Manufacturing, delivered under the GMO schema. It records the reversal or return of previously dispensed materials during production execution. When an operator dispenses ingredients to a batch or work order, a record is written to GMO_MATERIAL_DISPENSES. When that dispense must be undone—whether due to incorrect quantities, mis-scanned lots, container reconciliation, or returned material—a corresponding UNDispense transaction is stored here. This provides a full audit trail of material movement reversals on the shop floor.

From a Data Vault modeling perspective, the metadata was classified heuristically as standalone. That classification should be treated as a suggestion rather than a prescriptive design rule; a practitioner might alternatively model this table as a link connecting dispense events, reservations, and material detail, given its multiple foreign key relationships. The object is marked VALID in ETRM 12.2.2 and carries a documented physical schema of 40 columns.

Key Information Stored

The primary key is the surrogate GMO_MATERIAL_UNDISPENSES_PK, built on the single column UNDISPENSE_ID. A separate unique index, GMO_MATERIAL_UNDISPENSES_U1 (UNDISPENSE_ID, DISPENSE_ID), serves as a business-key candidate, tying each reversal uniquely back to its originating dispense.

Common Use Cases and Queries

The most common reporting need is reconciling dispensed versus undispensed quantities for a batch to confirm net consumption. A typical query joins reversals back to the parent dispense:

  • Reconciling net material consumption per batch by summing DISPENSED_QTY and UNDISPENSED_QTY grouped by BATCH_ID.
  • Auditing operator reversals using UNDISPENSE_TYPE, UNDISPENSING_MODE, and UNDISPENSED_DATE.
  • Tracking container returns via SOURCE_CONTAINER_DEVICE_ID and MATERIAL_LOSS for weight discrepancy analysis.
  • Verifying reservation releases by joining RESERVATION_ID to MTL_RESERVATIONS.

A representative pattern:

SELECT u.UNDISPENSE_NUMBER, u.UNDISPENSE_TYPE, u.UNDISPENSED_QTY, u.LOT_NUMBER, u.UNDISPENSED_DATE FROM GMO.GMO_MATERIAL_UNDISPENSES u WHERE u.BATCH_ID = :p_batch_id ORDER BY u.UNDISPENSED_DATE;

Related Objects

  • GMO_MATERIAL_DISPENSES — joined via GMO_MATERIAL_UNDISPENSES.DISPENSE_ID; the parent dispense being reversed.
  • MTL_RESERVATIONS — joined via RESERVATION_ID; the inventory reservation affected.
  • GMO_DISPENSE_AREA_B — joined via DISPENSE_AREA_ID; defines the physical dispense area.
  • GMO_MATERIAL_DETAILS — conceptually linked through MATERIAL_DETAIL_ID for batch material requirements.
  • GMO_BATCHES / GMO_BATCH_STEPS — provide batch and step context via BATCH_ID and BATCH_STEP_ID.
  • MTL_SYSTEM_ITEMS_B and MTL_ITEM_LOCATIONS — resolve item and subinventory attributes referenced by the denormalized columns.

Together these relationships position GMO_MATERIAL_UNDISPENSES as the reversal ledger within MES process manufacturing, essential for accurate yield, loss, and inventory reconciliation reporting.