Search Results csp_usage_histories




Overview
Explore in AI Chat

Overview

The CSP_USAGE_HISTORIES table is a core data repository within the Oracle E-Business Suite Spares Management (CSP) module. It serves as a central table for storing aggregated and summarized historical usage data for inventory items, specifically spare parts. This summarized data is critical for performing demand forecasting, inventory planning, and replenishment analysis. By tracking consumption patterns over defined periods, the table enables organizations to optimize their spare parts inventory levels, reduce carrying costs, and improve service levels for maintenance operations.

Key Information Stored

The table's structure is designed to categorize usage data by several key dimensions. Its primary key uniquely identifies a record through a combination of ORGANIZATION_ID, SUBINVENTORY_CODE, INVENTORY_ITEM_ID, HISTORY_DATA_TYPE, PERIOD_START_DATE, and PERIOD_TYPE. The HISTORY_DATA_TYPE column is particularly significant, as it classifies the nature of the historical data stored—such as actual consumption, forecasted usage, or adjusted figures—allowing the system to maintain and compare different data series. Other important columns include PARTS_LOOP_ID (linking to a specific maintenance loop), HIERARCHY_NODE_ID (for organizational hierarchies), TRANSACTION_TYPE_ID, and numeric fields like QUANTITY and COST that store the summarized usage values for the given period.

Common Use Cases and Queries

This table is primarily accessed for generating reports and feeding analytics engines within Spares Management. A common use case is analyzing consumption trends for a specific part within a subinventory to determine reorder points. For example, a query might retrieve the total quantity used per period for a given item, filtered by a specific HISTORY_DATA_TYPE like 'ACTUAL'. Another critical scenario involves comparing forecasted usage (one HISTORY_DATA_TYPE) against actual consumption (another HISTORY_DATA_TYPE) to measure forecast accuracy. Sample SQL patterns often involve aggregating the QUANTITY column grouped by PERIOD_START_DATE and INVENTORY_ITEM_ID, with joins to MTL_SYSTEM_ITEMS_B for item details.

Related Objects

The CSP_USAGE_HISTORIES table maintains integral relationships with several fundamental EBS tables, as documented by its foreign keys. These relationships ensure data integrity and enable comprehensive joins for reporting:

  • CSP_PARTS_LOOPS_B: Joined via PARTS_LOOP_ID to associate usage with a specific maintenance parts loop.
  • CSP_REP_HIERARCHIES: Joined via HIERARCHY_NODE_ID to align usage data with organizational reporting structures.
  • MTL_SYSTEM_ITEMS_B: Joined via INVENTORY_ITEM_ID and ORGANIZATION_ID to obtain item master information like part number and description.
  • MTL_SECONDARY_INVENTORIES: Joined via SUBINVENTORY_CODE and ORGANIZATION_ID to provide subinventory details.
  • MTL_TRANSACTION_TYPES: Joined via TRANSACTION_TYPE_ID to categorize the type of transaction that generated the usage.