Results for “pick_status”

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

Overview

WSH_PICK_SERIAL_V is a lightweight database view owned by the APPS schema in Oracle E-Business Suite, defined within the Shipping Execution (WSH) product. Its purpose is to expose serial-number information associated with picking activity in a simplified, presentation-ready format. The view is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2, and its definition is intentionally narrow: it does not aggregate, filter, or enrich data beyond the minimal transformation required to present a transactional identity and a status literal.

Functionally, the view serves as a picked-serial projection. Every row returned represents a unit transaction record from the inventory serial tracking infrastructure, relabeled with a static pick status. Its role in reporting and integration is to provide a stable, low-complexity interface for downstream consumers—such as shipping confirmation reports, integration extracts, and pick-confirmation inquiries—that require an identifier and serial number without needing the full breadth of the underlying inventory transaction model.

Underlying Base Objects

The view is defined over a single base object: MTL_UNIT_TRANSACTIONS, exposed within the APPS schema through a synonym. MTL_UNIT_TRANSACTIONS is the core Oracle Inventory table that records individual unit-level movements and status changes for serialized and lot-controlled material. Each row in that table corresponds to a discrete transaction performed against a unit, capturing the transaction type, the parent transaction reference, and the serial identifier involved.

Because WSH_PICK_SERIAL_V selects directly from MTL_UNIT_TRANSACTIONS without joins, filters, or grouping, there is a strict one-to-one relationship between the rows returned by the view and the qualifying rows in the base table. The view imposes no WHERE clause on transaction type in the documented definition; consumers that require only pick-related serials must therefore apply their own filtering logic against the source table's transaction-type context. This is an important characteristic: the PICK_STATUS literal is a constant, not a derived or conditional value.

Key Columns

• SERIAL_TXN_ID — Exposes TRANSACTION_ID from MTL_UNIT_TRANSACTIONS. This is the unique identifier of the unit transaction that moved or affected the serialized unit. It provides the join key back to inventory transaction records and is the primary correlation point for integration extracts.
• SERIAL_NUMBER — Exposes the SERIAL_NUMBER value from MTL_UNIT_TRANSACTIONS. This is the human-readable and system-recognized serial identifier of the unit, used in shipping documents, packing slips, and serial traceability inquiries.
• PICK_STATUS — A hard-coded character literal, always 'PICKED'. It is not read from any column; it is a constant projection. Consumers should treat it as a label indicating the semantic intent of the view rather than as transactional state derived from data.

Common Use Cases and Queries

Typical usage centers on retrieving serial numbers tied to inventory unit transactions for shipping and traceability reporting. Because the view carries no transactional filtering, callers commonly join it to parent inventory or shipping tables to constrain results to a specific pick, delivery, or move order.

A basic retrieval of picked serials:

SELECT serial_txn_id, serial_number, pick_status
FROM   apps.wsh_pick_serial_v;

Joining to restrict serials to a specific delivery or move order is the more realistic pattern, using SERIAL_TXN_ID as the correlation path into MTL_UNIT_TRANSACTIONS and then into WSH_DELIVERY_ASSIGNMENTS or WSH_PICKING_HEADERS as the business context requires. A representative query:

SELECT v.serial_number
FROM   apps.wsh_pick_serial_v v,
       apps.mtl_unit_transactions mut
WHERE  v.serial_txn_id = mut.transaction_id
AND    mut.transaction_type = <pick_transaction_type>;

Integration extracts frequently use this view as a simplification layer so that consuming systems receive a stable two-column payload (transaction identity plus serial) without absorbing the wider MTL_UNIT_TRANSACTIONS schema. Report developers should note that any transactional-state semantics must be sourced from the base table, since PICK_STATUS is constant.