Search Results csp_excess_list_serial_lots




Overview
Explore in AI Chat

Overview

CSP_EXCESS_LIST_SERIAL_LOTS is a transactional child table within the Oracle E-Business Suite Spares Management (CSP) module. It stores the serial number and lot number detail associated with lines on an excess list — the inventory disposition mechanism that allows organizations to identify, publish, and process surplus or excess spare parts for reuse, transfer, or disposal. Because a single excess list line can reference multiple discrete serialized units or multiple lot-controlled quantities, this table exists to hold that one-to-many detail beneath the parent excess line.

In Data Vault modeling terms, the heuristic classification derived from the foreign key structure is satellite-leaning. This reflects the table's role as a descriptive detail store attached to a parent business entity (the excess line) rather than an independent hub of business keys. A modeler would typically treat CSP_EXCESS_LIST_SERIAL_LOTS as a satellite of the excess list line, capturing the serial and lot attributes that describe the specific units in scope.

Key Information Stored

The table contains 13 documented columns. The most significant are:

The surrogate key (EXCESS_LIST_SERIAL_LOT_ID) is distinct from the business-key linkage to the parent line. Serial and lot identifiers themselves are stored as attributes rather than as enforced unique business keys in the documented metadata.

Common Use Cases and Queries

Typical usage centers on interrogating the serial and lot detail behind an excess list for reporting, reconciliation, or downstream transfer processing. A common query pattern joins the detail back to its parent line:

  • Listing all serialized and lot-controlled units on a given excess list, filtered by EXCESS_LINE_ID.
  • Verifying quantity and revision per serial number for physical inventory verification.
  • Reporting excess inventory by locator to support transfer or disposal decisions.
  • Auditing creation and update timestamps for change-tracking and compliance reporting.

A representative join follows:

  • SELECT s.SERIAL_NUMBER, s.LOT_NUMBER, s.QUANTITY, s.REVISION, s.LOCATOR_ID FROM CSP.CSP_EXCESS_LIST_SERIAL_LOTS s JOIN CSP.CSP_EXCESS_LISTS l ON s.EXCESS_LINE_ID = l.EXCESS_LINE_ID WHERE l.EXCESS_LIST_ID = :list_id;

Because SECURITY_GROUP_ID participates in row-level security, production queries should respect the security group context, particularly in multi-organization deployments.

Related Objects

The most significant objects related to this table, based on the documented foreign key relationships, are:

  • CSP_EXCESS_LISTS — the parent excess list table, joined via CSP_EXCESS_LIST_SERIAL_LOTS.EXCESS_LINE_ID = CSP_EXCESS_LISTS.EXCESS_LINE_ID. This is the primary relationship.
  • FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID, governing data access security.
  • CSP_EXCESS_LIST_SERIAL_LOTS_PK / CSP_EXCESS_LIST_SERIAL_LOTS_U1 — the primary key constraint and unique index enforcing EXCESS_LIST_SERIAL_LOT_ID uniqueness.
  • MTL_SYSTEM_ITEMS_B and MTL_ITEM_LOCATIONS — referenced indirectly through item, revision, and locator semantics for the serialized and lot-controlled units.
  • INV_SERIAL_NUMBERS / MTL_LOT_NUMBERS — the inventory serial and lot master tables that provide the corresponding unit identities consumed by Spares Management excess processing.