Search Results detailing_date




Overview
Explore in AI Chat

Overview

APPS.WSH_PICK_SLIP_V is a reporting and integration view in Oracle E-Business Suite (documented for 12.1.1 and 12.2.2) that consolidates material movement lines associated with pick slips across three distinct transactional sources. The view presents a unified result set of pick-slip detail lines, tagging each row with a LINE_STATUS of either 'UNPICKED' or 'PICKED' depending on the source object from which the row originates. This design allows downstream consumers—custom reports, interfaces, and Oracle Warehouse Management (WMS) or shipping workflows—to query a single object without separately unioning staging, historical, and move-order tables.

The view is not a base table; it is a read-only UNION ALL construct. Consequently, it is typically used for inquiry and reporting rather than transactional DML. Each source branch applies its own filter on PICK_SLIP_NUMBER and quantity, normalizing signs so that picked quantities appear as positive values.

Underlying Base Objects

Per the ETRM metadata, the view is defined over three referenced base objects, all exposed as SYNONYMs in the APPS schema:

  • MTL_MATERIAL_TRANSACTIONS_TEMP — staging rows not yet posted. These contribute lines flagged 'UNPICKED' and are filtered by PICK_SLIP_NUMBER IS NOT NULL AND ABS(NVL(TRANSACTION_QUANTITY,0)) > 0.
  • MTL_MATERIAL_TRANSACTIONS — posted inventory transactions. These contribute lines flagged 'PICKED', filtered by PICK_SLIP_NUMBER IS NOT NULL AND NVL(TRANSACTION_QUANTITY,0) < 0 (issue/negative quantities).
  • MTL_TXN_REQUEST_LINES — move-order request lines, also flagged 'PICKED', filtered by PICK_SLIP_NUMBER IS NOT NULL. This branch supplies move-order context even where no inventory transaction has yet posted.

The three branches are combined with UNION ALL, with column aliases harmonized so that each branch maps cleanly onto the same projection.

Key Columns

  • PICK_SLIP_NUMBER — the driving identifier; all three branches require it to be non-null.
  • MOVE_ORDER_LINE_ID — the move-order line identifier, sourced natively from MTL_MATERIAL_TRANSACTIONS_TEMP and MTL_MATERIAL_TRANSACTIONS, and from LINE_ID in MTL_TXN_REQUEST_LINES. This is the key join back to move-order detail and is the term users most frequently search on.
  • FROM_SUBINVENTORY / FROM_LOCATOR_ID — source subinventory and locator.
  • TO_SUBINVENTORY / TO_LOCATOR_ID — destination subinventory and locator, derived from transfer_subinventory/transfer_to_location, transfer_locator_id, or to_subinventory_code/to_locator_id depending on branch.
  • PRIMARY_QTY — absolute primary quantity, normalized to positive.
  • LINE_STATUS — literal 'UNPICKED' or 'PICKED'.
  • DETAILING_DATE — creation_date, transaction_date, or pick_slip_date per branch.
  • TRANSACTION_ID — transaction_temp_id or transaction_id; null for move-order lines.
  • ITEM_PRIMARY_UOM_CODE / SECONDARY_UOM_CODE / SECONDARY_TRANSACTION_QUANTITY — UOM and secondary-quantity attributes for dual-UOM reporting.

Common Use Cases and Queries

The view supports pick-slip status reporting, reconciliation between staged and posted movements, and integration feeds that must associate move-order lines with pick slips. A representative query:

  • Query all lines for a slip: SELECT move_order_line_id, line_status, primary_qty FROM apps.wsh_pick_slip_v WHERE pick_slip_number = :slip;
  • Find unpicked lines: SELECT * FROM apps.wsh_pick_slip_v WHERE line_status = 'UNPICKED';
  • Join to move-order detail by MOVE_ORDER_LINE_ID to enrich with item and order attributes.

Because the view is a UNION ALL over volatile sources, filters on PICK_SLIP_NUMBER and MOVE_ORDER_LINE_ID should always be supplied to limit scan cost.