Search Results wsh_pick_serial_temp_v

Overview

WSH_PICK_SERIAL_TEMP_V is a lightweight view owned by the APPS schema within the Oracle E-Business Suite Shipping Execution (WSH) module. Its status is documented as VALID in both the 12.1.1 and 12.2.2 releases. The view exists to expose serial number data that is staged during the picking process, presenting a simple, denormalized projection over the temporary serial number staging area.

The view is deliberately narrow in scope. Rather than joining across multiple serial control and inventory tables, it surfaces a minimal set of three columns drawn from a single underlying staging object. This design reflects its purpose: to provide the shipping/picking workflow a stable, pre-shaped row source for serial numbers that have been recorded against a pick transaction but that have not yet been committed into the permanent serial number records. Because it contains only temporary staging data, the view is typically read transiently during pick confirmation, pick release, or serial capture processing rather than used as a long-term reporting source.

Underlying Base Objects

The view is defined entirely over a single base object: MTL_SERIAL_NUMBERS_TEMP, accessed through its SYNONYM in the APPS schema. No additional joins, unions, filters, or aggregations are present in the view text. The documented definition is:

SELECT TRANSACTION_TEMP_ID SERIAL_TXN_TEMP_ID ,
       VENDOR_SERIAL_NUMBER ,
       'UNPICKED' PICK_STATUS
FROM MTL_SERIAL_NUMBERS_TEMP

Three important observations follow from this definition. First, the view is a pure column-renaming projection — the only transformation applied is the aliasing of TRANSACTION_TEMP_ID to SERIAL_TXN_TEMP_ID and the introduction of the literal 'UNPICKED' for PICK_STATUS. Second, because PICK_STATUS is a hard-coded literal, every row returned by the view carries the same status value; the view therefore represents only the un-picked state of staged serials. Third, MTL_SERIAL_NUMBERS_TEMP is a temporary/staging table, so the row population is transitory and tied to the lifecycle of a pick transaction. Serial records move out of this staging area into the permanent inventory serial tables once the transaction is processed, at which point they no longer appear through this view.

Key Columns

• SERIAL_TXN_TEMP_ID — Aliased from TRANSACTION_TEMP_ID on the base table. This is the internal identifier for the staged serial transaction row and serves as the effective primary key for correlating a row back to its originating pick transaction.
• VENDOR_SERIAL_NUMBER — The externally supplied vendor serial number captured for the serialized item. This is the column most directly relevant to the user search term "vendor_serial_number," since it carries the manufacturer/vendor-assigned serial value into the picking workflow.
• PICK_STATUS — A literal column that always returns the value 'UNPICKED'. It provides a constant status indicator so that consumers of the view can treat the result set uniformly as representing staged, not-yet-picked serials.

Common Use Cases and Queries

The view is most useful when you need to inspect or reconcile vendor serial numbers that are currently staged for picking against serialized inventory. A basic retrieval of all staged serials is:

SELECT serial_txn_temp_id,
       vendor_serial_number,
       pick_status
FROM   apps.wsh_pick_serial_temp_v;

To locate a specific vendor serial — the typical lookup implied by the search term "vendor_serial_number" — a filtered query is used:

SELECT serial_txn_temp_id,
       vendor_serial_number
FROM   apps.wsh_pick_serial_temp_v
WHERE  vendor_serial_number = :p_vendor_serial_number;

Because the view exposes the staging row identifier, it can be joined back to MTL_SERIAL_NUMBERS_TEMP to obtain additional attributes not projected by the view, or correlated to pick transaction data to trace which delivery or move order triggered the serial capture. Practical scenarios include troubleshooting pick confirmations that fail serial validation, verifying that vendor serials were correctly captured before staging records are purged, and building diagnostic reports during shipping integration testing. As the underlying object is a temporary table, queries should always be run while the pick transaction is active or immediately after capture; historical reporting should instead query the committed inventory serial number tables.