Search Results packlist_serial_lot_id

Overview

The CSP_PACKLIST_SERIAL_LOTS table is a core transactional data repository within the Oracle E-Business Suite Spares Management (CSP) module. Its primary role is to maintain a granular, item-level record of serial and lot-controlled inventory specifically allocated to customer packlists. A packlist is a critical shipping document that details the parts and quantities being dispatched, often for service or repair orders. This table ensures precise traceability by linking individual serial numbers and lot identifiers directly to their corresponding packlist lines, forming an essential audit trail for inventory fulfillment and compliance in distribution and service logistics.

Key Information Stored

The table's structure is designed to capture the relationship between a packlist line item and its specific tracked inventory attributes. The primary key, PACKLIST_SERIAL_LOT_ID, uniquely identifies each record. The most critical foreign key column is PACKLIST_LINE_ID, which ties the serial/lot data to its parent line in the CSP_PACKLIST_LINES table. For serialized items, the SERIAL_NUMBER, along with the INVENTORY_ITEM_ID and ORGANIZATION_ID, references the MTL_SERIAL_NUMBERS table to validate the specific unit. Similarly, for lot-controlled items, the LOT_NUMBER, INVENTORY_ITEM_ID, and ORGANIZATION_ID reference the MTL_LOT_NUMBERS table. This design allows a single packlist line, which may have a quantity greater than one, to have multiple child records in this table for each individual serial number or lot.

Common Use Cases and Queries

A primary use case is generating detailed shipping and compliance reports that list every serialized unit shipped on a particular packlist or to a specific customer. Support teams query this table to trace the shipment history of a specific serial number returned for repair. Common SQL patterns involve joining to CSP_PACKLIST_LINES and the master shipping headers (CSP_PACKLISTS) to build a complete view. For example, to find all serial numbers shipped on a packlist, a query would join CSP_PACKLIST_SERIAL_LOTS to CSP_PACKLIST_LINES on PACKLIST_LINE_ID and filter by the header's packlist number. Another critical operational query validates that serial numbers selected for shipping are still available in inventory by checking their status in the referenced MTL_SERIAL_NUMBERS table.

Related Objects

The table maintains defined foreign key relationships with several fundamental EBS inventory and CSP objects, as documented in the ETRM metadata:

• CSP_PACKLIST_LINES: The primary parent table. Joined via CSP_PACKLIST_SERIAL_LOTS.PACKLIST_LINE_ID = CSP_PACKLIST_LINES.PACKLIST_LINE_ID. This establishes which packlist line the tracked inventory fulfills.
• MTL_SERIAL_NUMBERS: Referenced for serialized inventory. The join uses multiple columns: CSP_PACKLIST_SERIAL_LOTS.SERIAL_NUMBER, INVENTORY_ITEM_ID, and ORGANIZATION_ID to the corresponding columns in MTL_SERIAL_NUMBERS.
• MTL_LOT_NUMBERS: Referenced for lot-controlled inventory. The join uses CSP_PACKLIST_SERIAL_LOTS.LOT_NUMBER, INVENTORY_ITEM_ID, and ORGANIZATION_ID to the corresponding columns in MTL_LOT_NUMBERS.

These relationships ensure data integrity and enable comprehensive reporting by connecting packlist details to the core Inventory (INV) module's master serial and lot tables.