Search Results csp_excess_lists_n5

Overview

The CSP.CSP_EXCESS_LISTS table is a transactional data object within the Oracle E-Business Suite 12.1.1 and 12.2.2 environments, owned by the CSP schema (typically associated with supply chain and advanced planning modules). This table stores records of items whose on-hand quantities in a given organization or subinventory exceed a defined maximum level. It serves as a persistent ledger of excess inventory positions, capturing both the initial excess quantity and the subsequent return activity, and is the foundation for reconciliation between excess stock and replenishment or return requisition lines.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. It is classified heuristically as a satellite-leaning object in a Data Vault model, since it records measurable, time-variant facts (excess, returned, and current return quantities) surrounding an inventory item within an organizational context rather than functioning purely as a hub or link. The primary key, CSP_EXCESS_LISTS_PK, is defined on the EXCESS_LINE_ID column, providing a unique surrogate identifier per excess record.

Key Information Stored

Each row represents one excess-inventory event keyed by EXCESS_LINE_ID. The most significant columns include the following:

• EXCESS_LINE_ID — surrogate primary key and unique business-key candidate via CSP_EXCESS_LISTS_U1; uniquely identifies each excess list record.
• ORGANIZATION_ID and SUBINVENTORY_CODE — location context where the excess exists; both are indexed for query performance.
• INVENTORY_ITEM_ID — the specific item in excess.
• EXCESS_QUANTITY — quantity above the maximum level.
• RETURNED_QUANTITY and CURRENT_RETURN_QTY — cumulative and current quantities returned to a return organization.
• REQUISITION_LINE_ID — links the excess record to a purchase requisition line, supporting replenishment or return workflows.
• EXCESS_STATUS and REASON_CODE — lifecycle state and rationale when an excess was not returned.
• RETURN_ORGANIZATION_ID and RETURN_SUBINVENTORY_NAME — destination for returned material.
• CONDITION_CODE — item condition applied to the excess stock.
• SECURITY_GROUP_ID — Data Vault security partitioning column.
• Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and a standard descriptive flexfield (ATTRIBUTE_CATEGORY plus ATTRIBUTE1–15).

Common Use Cases and Queries

Typical uses include excess-inventory reporting, subinventory overstock analysis, tracking return quantities, and linking excess stock to requisition demand. A representative query for excess items in a given organization might be:

• SELECT EXCESS_LINE_ID, INVENTORY_ITEM_ID, SUBINVENTORY_CODE, EXCESS_QUANTITY, EXCESS_STATUS FROM CSP_EXCESS_LISTS WHERE ORGANIZATION_ID = :org_id AND EXCESS_STATUS = 'OPEN';
• SELECT r.REQUISITION_LINE_ID, e.EXCESS_QUANTITY, e.CURRENT_RETURN_QTY FROM CSP_EXCESS_LISTS e JOIN PO_REQUISITION_HEADERS_ALL r ON e.REQUISITION_LINE_ID = r.REQUISITION_HEADER_ID;
• Aggregation by item and subinventory to rank top overstocked items, using the CSP_EXCESS_LISTS_N1 and CSP_EXCESS_LISTS_N2 indexes on ORGANIZATION_ID with INVENTORY_ITEM_ID and SUBINVENTORY_CODE respectively.

Because RETURN_ORGANIZATION_ID and RETURN_SUBINVENTORY_NAME are indexed by CSP_EXCESS_LISTS_N4 and CSP_EXCESS_LISTS_N5, reporting on inter-organization returns is also efficient.

Related Objects

The table maintains referential relationships to several core EBS entities:

• MTL_SYSTEM_ITEMS_B — joined via INVENTORY_ITEM_ID to resolve item details.
• MTL_SECONDARY_INVENTORIES — joined via SUBINVENTORY_CODE to resolve subinventory attributes.
• PO_REQUISITION_HEADERS_ALL — joined via REQUISITION_LINE_ID to link demand requisitions.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID for Data Vault security resolution.
• CSP_EXCESS_LIST_SERIAL_LOTS — the child table that references CSP_EXCESS_LISTS via EXCESS_LINE_ID, storing serial and lot detail for each excess record.

These relationships position CSP_EXCESS_LISTS as a central fact-bearing satellite for excess inventory tracking, with the serial/lot child table extending granular traceability.