Search Results wms_suggestions_trace_v

Overview

WMS_SUGGESTIONS_TRACE_V is an APPS-owned database view within the Warehouse Management (WMS) product of Oracle E-Business Suite, valid in both release 12.1.1 and 12.2.2. It presents the consolidated material transaction trace records that Oracle WMS generates when the warehouse execution engine evaluates move order lines, pick suggestions, and put-away suggestions. The view flattens the trace data held in the WMS material transaction tables into a single, wide result set that exposes both the "from" and "to" sides of an intended inventory movement along with the strategies and rules that produced the suggestion.

Because the view sources from transient trace tables rather than from the permanent inventory transaction tables, it is diagnostic in nature. It allows a warehouse or technical consultant to reconstruct the step-by-step reasoning the WMS engine applied to a move order line — which pick or put-away strategy was invoked, which rule matched, and how the source cost group differs from the destination cost group. This makes the view a primary reference for troubleshooting suggestions that failed to allocate, resolved to an unexpected subinventory, or returned an incorrect cost group.

The view does not store data itself; it is a runtime projection over the WMS trace synonyms, and its rowset reflects the trace records present at query time. It is not a historical audit table.

Underlying Base Objects

The ETRM 12.2.2 metadata documents three referenced base objects, all exposed as synonyms in the APPS schema:

• WMS_MATERIAL_TXN_TRACE — aliased as MTT in the view text and the dominant source object. It supplies move order line identifiers, transaction template identifiers, organization and item keys, primary and secondary quantities, revision, lot and serial information, locator and subinventory codes, and the strategy and rule identifiers.
• WMS_TRANSACTION_LOTS_TRACE — aliased as MTLT. Joined into the view to supply lot-level quantity, lot number, expiration date, and lot attribute category for transactions that are lot-controlled. The view's anti-join against this table suppresses duplicate material rows when a lot trace record already exists.
• WMS_SERIAL_NUMBERS_TRACE — aliased as MSNT. Joined to supply serial number and serial attribute category data, and used in a second anti-join to suppress duplication when a serial trace record exists.

Two anti-join predicates exclude material trace rows that already have corresponding lot trace or serial number trace records, and a UNION combines the consolidated output. This construction deduplicates the trace results so each logical suggestion appears once, with lot and serial detail attached where applicable.

Key Columns

• ROW_ID / REC_TYPE / TYPE_CODE — the source row identifier, a literal record type marker ('MTT'), and a numeric type code (0). Used internally to distinguish record origins.
• MOVE_ORDER_LINE_ID — the move order line that the suggestion applies to; the principal join key back to move order data.
• TRANSACTION_TEMP_ID — the temporary transaction identifier linking trace records across the material, lot, and serial trace tables.
• ORGANIZATION_ID, INVENTORY_ITEM_ID, REVISION — the inventory organization, item, and revision under evaluation.
• QUANTITY, SECONDARY_QUANTITY — primary and secondary transaction quantities (MTT.PRIMARY_QUANTITY, or MTLT.PRIMARY_QUANTITY when a lot trace governs).
• LOT_NUMBER, LOT_EXPIRATION_DATE, LOT_ATTRIBUTE_CATEGORY, SERIAL_NUMBER, SERIAL_ATTRIBUTE_CATEGORY — lot and serial attributes of the traced material.
• FROM_ORGANIZATION_ID, FROM_SUBINVENTORY_CODE, FROM_LOCATOR_ID, FROM_COST_GROUP_ID — the source side of the movement. FROM_COST_GROUP_ID maps directly to MTT.COST_GROUP_ID and is the column returned when users search for "from_cost_group_id".
• TO_ORGANIZATION_ID, TO_SUBINVENTORY_CODE, TO_LOCATOR_ID, TO_COST_GROUP_ID — the destination side. These are derived using DECODE on TRANSACTION_ACTION_ID: for action 27 (subinventory transfer) and action 12 the destination resolves to the originating organization, subinventory, locator, and cost group; for other actions the transfer-specific columns (TRANSFER_ORGANIZATION, TRANSFER_SUBINVENTORY, TRANSFER_TO_LOCATION, TRANSFER_COST_GROUP_ID) supply the destination.
• PICK_STRATEGY_ID, PICK_RULE_ID, PUTAWAY_STRATEGY_ID, PUTAWAY_RULE_ID — the strategies and rules the engine selected while generating the suggestion.
• LPN_ID — the allocated license plate number, inherited from MTT.ALLOCATED_LPN_ID.

Common Use Cases and Queries

The most frequent use is diagnosing why a move order line resolved to a particular source cost group. A representative query joining the view to move order lines returns the source and destination cost groups side by side:

• Investigate cost group resolution:

  SELECT move_order_line_id,
         from_cost_group_id,
         to_cost_group_id,
         from_subinventory_code,
         to_subinventory_code,
         pick_strategy_id,
         pick_rule_id
  FROM   apps.wms_suggestions_trace_v
  WHERE  move_order_line_id = :p_move_order_line_id;

• Identify suggestions where source and destination cost groups differ, indicating an unintended cost group change during picking or put-away.
• Audit which pick strategy and rule combinations produced suggestions for a given item and organization, to validate rule setup in the warehouse execution engine.
• Trace lot and serial controlled movements by restricting on LOT_NUMBER or SERIAL_NUMBER to confirm the correct lot was selected.
• Reconcile suggested quantities against move order line demand by comparing QUANTITY and SECONDARY_QUANTITY to the move order line's ordered quantity.

Because the view is defined over transient trace tables, results should be gathered while the trace data remains in the engine's working set. Older suggestions may no longer be represented, so for persistent audit purposes the trace output should be captured into a custom table at the time of execution.