Search Results csp_packlist_serial_lots_v




Overview
Explore in AI Chat

Overview

CSP_PACKLIST_SERIAL_LOTS_V is a reporting view owned by the APPS schema within the CSP (Spares Management) product family of Oracle E-Business Suite. Its documented purpose is to expose the serial numbers or lot numbers that are available for receipt against a given packlist line. In the spares logistics flow, a packlist represents the physical shipment of goods being returned or transferred, and the receiving clerk must confirm which serialized units or lot-controlled quantities have actually arrived. This view consolidates that confirmation data by joining the packlist and picklist structures to the transaction temporary tables that Oracle uses during receiving and transaction processing.

The view is relevant to users who search for mtl_transaction_lots_temp, since that table is one of the principal sources the view reads from. Rather than querying the transient transaction temp tables directly, the view presents a stable, denormalized projection that returns one row per packlist line with either a serial number, a lot number, both, or neither, depending on the item's control attributes. This makes it suitable for reports, forms, and integration extracts that need to reconcile shipped versus received serial and lot detail.

Underlying Base Objects

The documented base objects referenced by the view are all APPS synonyms: CSP_PACKLIST_LINES, CSP_PACKLIST_SERIAL_LOTS, CSP_PICKLIST_LINES, MTL_MATERIAL_TRANSACTIONS_TEMP, MTL_SERIAL_NUMBERS_TEMP, and MTL_TRANSACTION_LOTS_TEMP. The view text is a UNION of three separate SELECT statements, each joining a different combination of these objects.

  • The first branch joins CSP_PACKLIST_LINES to CSP_PICKLIST_LINES on PICKLIST_LINE_ID, then to CSP_PACKLIST_SERIAL_LOTS on PACKLIST_LINE_ID, and finally to MTL_SERIAL_NUMBERS_TEMP on TRANSACTION_TEMP_ID, matching the packlist serial number to the "from" serial number. This branch returns serial-only rows (LOT_NUMBER is NULL).
  • The second branch joins the packlist and picklist lines, CSP_PACKLIST_SERIAL_LOTS, MTL_MATERIAL_TRANSACTIONS_TEMP, and MTL_TRANSACTION_LOTS_TEMP, matching lot numbers. It filters with NVL(MMTT.ITEM_SERIAL_CONTROL_CODE,1) IN (1,6), restricting output to non-serialized or lot-controlled items.
  • The third branch combines MTL_TRANSACTION_LOTS_TEMP and MTL_SERIAL_NUMBERS_TEMP, linked via SERIAL_TRANSACTION_TEMP_ID, to return rows carrying both a lot number and a serial number.

Because the three branches are unioned, the view supplies a complete picture of serial, lot, and combined control scenarios within a single result set.

Key Columns

  • PACKLIST_LINE_ID — The identifier of the packlist line to which the serial or lot belongs. This is the primary correlation key back to CSP_PACKLIST_LINES.
  • QUANTITY — The quantity associated with the serial or lot. For serialized rows it is drawn from CPSL.QUANTITY; for lot rows it is taken from the transaction quantity in MTL_TRANSACTION_LOTS_TEMP.
  • SERIAL_NUMBER — The serial number of the unit available for receipt, or NULL when the row represents lot-controlled stock.
  • LOT_NUMBER — The lot number available for receipt, or NULL when the row represents serial-only stock.

Common Use Cases and Queries

Typical use cases include generating receiving worksheets, validating that all serialized items on a packlist are accounted for, and reconciling lot-controlled replenishment returns. A simple query returning all serial and lot detail for a packlist line follows:

SELECT packlist_line_id, quantity, serial_number, lot_number
FROM   apps.csp_packlist_serial_lots_v
WHERE  packlist_line_id = :p_packlist_line_id;

To isolate serialized receipts only, filter on a non-null serial number:

SELECT packlist_line_id, serial_number, quantity
FROM   apps.csp_packlist_serial_lots_v
WHERE  serial_number IS NOT NULL;

Analysts troubleshooting the underlying temporary transaction data referenced by this view can join results back to MTL_TRANSACTION_LOTS_TEMP or MTL_SERIAL_NUMBERS_TEMP using the same PACKLIST_LINE_ID and packlist line context. This supports root-cause analysis when a packlist line appears without expected serial or lot detail during receipt confirmation.