Search Results csp_stock_lists




Overview
Explore in AI Chat

Overview

CSP_STOCK_LISTS is a table in the CSP (Spares Management) module of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. According to the ETRM documentation, it "stores information about how the stock list item was created," making it a reference table for spares and inventory provisioning behaviors within the CSP schema. In practice it records, per organization and inventory item, whether stock list records were generated manually or automatically and the associated reason code, together with the subinventory context in which the item is managed.

The table is owned by the CSP schema and is currently VALID in the data dictionary. Its documented physical schema comprises 11 columns. The primary key is CSP_STOCK_LISTS_PK, defined on the composite of ORGANIZATION_ID and INVENTORY_ITEM_ID. Under the heuristic Data Vault classification derived from the foreign-key structure, CSP_STOCK_LISTS is satellite-leaning: it carries descriptive, changing attributes about an inventory item (the creation method and reason), rather than acting as a classic hub or link. This classification is offered as a modeling suggestion only, based on the observed FK topology.

Key Information Stored

The most significant columns are:

  • ORGANIZATION_ID — inventory organization identifier; part of the primary key and of both unique indexes.
  • INVENTORY_ITEM_ID — the inventory item to which the stock list record applies; part of the primary key and both unique indexes.
  • SUBINVENTORY_CODE — the subinventory in which the item resides; appears in both unique indexes, so an item may carry distinct rows across subinventories.
  • MANUAL_AUTO — the flag describing whether the stock list item was created manually or automatically, the core descriptive attribute the table is designed to store.
  • REASON_CODE — the reason the stock list item was created; a descriptive qualifier supporting audit and reporting.
  • SECURITY_GROUP_ID — references FND_SECURITY_GROUPS and drives multi-tenant / data-group filtering.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Oracle EBS who-columns capturing row audit information.

The surrogate primary key is CSP_STOCK_LISTS_PK (ORGANIZATION_ID, INVENTORY_ITEM_ID). Two additional unique indexes act as business-key candidates: CSP_STOCK_LISTS_U1 on (INVENTORY_ITEM_ID, SUBINVENTORY_CODE, ORGANIZATION_ID) and CSP_STOCK_LISTS_U2 on (ORGANIZATION_ID, SUBINVENTORY_CODE, INVENTORY_ITEM_ID). Their presence indicates that the true business identity of a row includes SUBINVENTORY_CODE, while the declared PK omits it.

Common Use Cases and Queries

Typical reporting scenarios include determining how many stock list entries were created automatically versus manually for a given organization, and reconciling CSP-generated stock lists against the item master. A representative query joins to MTL_SYSTEM_ITEMS_B to resolve item detail:

  • SELECT c.ORGANIZATION_ID, c.INVENTORY_ITEM_ID, c.SUBINVENTORY_CODE, c.MANUAL_AUTO, c.REASON_CODE FROM CSP.CSP_STOCK_LISTS c WHERE c.ORGANIZATION_ID = :org_id;
  • Join pattern: FROM CSP_STOCK_LISTS c, MTL_SYSTEM_ITEMS_B m WHERE c.INVENTORY_ITEM_ID = m.INVENTORY_ITEM_ID AND c.ORGANIZATION_ID = m.ORGANIZATION_ID
  • Aggregate by creation method: SELECT MANUAL_AUTO, COUNT(*) FROM CSP_STOCK_LISTS GROUP BY MANUAL_AUTO;

These patterns support dashboarding, audit of automated replenishment rules, and troubleshooting of unexpected stock list generation. Because the who-columns are populated, change tracking over time is feasible by filtering on LAST_UPDATE_DATE.

Related Objects

The following objects are most significant in relation to CSP_STOCK_LISTS, based on the documented foreign-key and key relationships:

  • MTL_SYSTEM_ITEMS_B — joined on INVENTORY_ITEM_ID and ORGANIZATION_ID; supplies item master attributes.
  • FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for data-group security.
  • CSP_STOCK_LIST_ITEMS / CSP-managed stock list APIs — the CSP spares management components that insert and read these rows.
  • MTL_SUBINVENTORIES — contextual source for SUBINVENTORY_CODE validation.
  • MTL_PARAMETERS — organization-level inventory setup aligned to ORGANIZATION_ID.

Together these objects define the referential and functional neighborhood within which CSP_STOCK_LISTS operates.