Search Results csp_picklist_serial_lots




Overview
Explore in AI Chat

Overview

The CSP_PICKLIST_SERIAL_LOTS table is a core transactional data object within the Oracle E-Business Suite Spares Management (CSP) module. It functions as a detailed subsidiary ledger, specifically designed to track and associate serial and lot-controlled inventory items with individual picklist lines. When a picklist is generated for fulfilling a service parts order, this table records the precise serial numbers and lot numbers allocated to each line item, ensuring granular traceability from the sales order through to shipment. Its role is critical for maintaining accurate inventory records for controlled items, supporting warranty tracking, recall management, and compliance with industry-specific traceability requirements.

Key Information Stored

The table's primary purpose is to link a picklist line to specific inventory instances. Its key columns, as indicated by its foreign key relationships, include the PICKLIST_SERIAL_LOT_ID (the primary key), and the PICKLIST_LINE_ID which ties each record to its parent line in CSP_PICKLIST_LINES. For serialized items, the SERIAL_NUMBER, INVENTORY_ITEM_ID, and ORGANIZATION_ID columns are populated, creating a reference to MTL_SERIAL_NUMBERS. For lot-controlled items, the LOT_NUMBER, INVENTORY_ITEM_ID, and ORGANIZATION_ID columns are used, referencing MTL_LOT_NUMBERS. This structure allows a single picklist line to have multiple child records in this table if multiple serials or quantities from a lot are allocated.

Common Use Cases and Queries

A primary use case is generating a detailed packing slip or shipping manifest that lists the exact serial numbers being shipped against a customer order. Support personnel may query this table to identify which serialized unit was shipped to a specific customer for warranty validation. Common reporting SQL involves joining to CSP_PICKLIST_LINES and the underlying order management tables. For example, to find all serials picked for a given sales order line, one might query: SELECT psl.serial_number, psl.lot_number, pl.picklist_line_id FROM csp_picklist_serial_lots psl, csp_picklist_lines pl WHERE psl.picklist_line_id = pl.picklist_line_id AND pl.source_line_id = <order_line_id>. Data from this table is also essential for reconciling inventory on-hand quantities with allocated and picked quantities for controlled items.

Related Objects

The table maintains integral relationships with several key inventory and spares management entities. Its documented foreign key relationships are as follows:

  • CSP_PICKLIST_LINES: Linked via CSP_PICKLIST_SERIAL_LOTS.PICKLIST_LINE_ID. This is the primary parent relationship, defining which picklist line the serial/lot allocation belongs to.
  • MTL_SERIAL_NUMBERS: Linked via a composite foreign key using columns SERIAL_NUMBER, INVENTORY_ITEM_ID, and ORGANIZATION_ID. This validates and provides attributes for the specific serialized inventory unit.
  • MTL_LOT_NUMBERS: Linked via a composite foreign key using columns LOT_NUMBER, INVENTORY_ITEM_ID, and ORGANIZATION_ID. This validates and provides attributes for the specific lot-controlled inventory.

As a child of CSP_PICKLIST_LINES, it is indirectly related to the entire picklist and spares fulfillment hierarchy, including CSP_PICKLIST_HEADERS and the source order management tables.