Results for “csp_usage_histories”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

CSP_USAGE_HISTORIES is a table in the CSP (Spares Management) product schema of Oracle E-Business Suite, valid in releases 12.1.1 and 12.2.2. It stores summarized usage history information, providing a periodic, aggregated record of how spare and service parts have been consumed across inventory organizations, subinventories, and reporting hierarchies. Rather than capturing every individual inventory transaction, the table preserves pre-computed quantity summaries keyed by time period, item, and transaction context, which allows downstream planning and forecasting processes to evaluate demand and consumption trends without repeatedly scanning high-volume transaction tables.

From a dimensional modeling perspective, the mined foreign-key structure suggests classifying this object as a link table. Its primary key combines dimensional references (organization, subinventory, item, history data type, period, and period type) and it resolves relationships between inventory items, subinventories, transaction types, parts loops, reporting hierarchy nodes, and usage headers. This classification is a heuristic modeling suggestion rather than an EBS-imposed designation; the table functions operationally as a summarized fact-and-link hybrid within the Spares Management data model.

Key Information Stored

The documented physical schema in 12.2.2 contains 38 columns owned by the CSP schema. The most operationally significant columns are:

• USAGE_ID — Surrogate identifier and the registered business-key candidate, enforced by the unique index CSP_USAGE_HISTORIES_U1.
• ORGANIZATION_ID — Inventory organization dimension for the summarized usage.
• INVENTORY_ITEM_ID — The spare or service item whose usage is summarized.
• SUBINVENTORY_CODE — Subinventory within the organization where usage occurred.
• HIERARCHY_NODE_ID — Reference to the reporting hierarchy node used to aggregate demand by organizational or planning structure.
• PARTS_LOOP_ID — Links the record to the parts loop definition in Spares Management that governs the part's replenishment context.
• PERIOD_START_DATE and PERIOD_TYPE — Define the time bucket over which the summarized quantities were accumulated.
• HISTORY_DATA_TYPE — Distinguishes the category of summarized data held in the row.
• TRANSACTION_TYPE_ID — Inventory transaction type from which the usage was derived.
• QUANTITY — The summarized usage quantity for the period and dimensional combination.
• USAGE_HEADER_ID — Reference to the parent usage header record.
• SECURITY_GROUP_ID — Security grouping used for data access partitioning.
• Standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE) and concurrent program columns (REQUEST_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) record provenance.

The documented composite primary key CSP_USAGE_HISTORIES_PK covers ORGANIZATION_ID, SUBINVENTORY_CODE, INVENTORY_ITEM_ID, HISTORY_DATA_TYPE, PERIOD_START_DATE, and PERIOD_TYPE, while the USAGE_ID unique index provides the single-column surrogate key. The 15 ATTRIBUTE columns are descriptive flexfield segments.

Common Use Cases and Queries

Typical reporting scenarios include demand trend analysis by item and subinventory, hierarchical roll-up of usage across reporting nodes, and parts-loop performance evaluation. A representative query joining usage history to the item master and transaction types is:

• SELECT h.INVENTORY_ITEM_ID, h.SUBINVENTORY_CODE, h.PERIOD_START_DATE, h.PERIOD_TYPE, h.QUANTITY FROM CSP.CSP_USAGE_HISTORIES h WHERE h.ORGANIZATION_ID = :org_id AND h.HISTORY_DATA_TYPE = :type ORDER BY h.INVENTORY_ITEM_ID, h.PERIOD_START_DATE;
• Aggregation by hierarchy node: SELECT h.HIERARCHY_NODE_ID, h.PERIOD_START_DATE, SUM(h.QUANTITY) FROM CSP.CSP_USAGE_HISTORIES h GROUP BY h.HIERARCHY_NODE_ID, h.PERIOD_START_DATE;
• Joining to the item master: SELECT msib.SEGMENT1, h.QUANTITY FROM CSP.CSP_USAGE_HISTORIES h, MTL_SYSTEM_ITEMS_B msib WHERE h.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID AND h.ORGANIZATION_ID = msib.ORGANIZATION_ID;

Because the table is periodically summarized, queries are efficient for trend and forecasting reporting compared with scanning MTL_TRANSACTIONS directly.

Related Objects

• MTL_SYSTEM_ITEMS_B — joined on INVENTORY_ITEM_ID and ORGANIZATION_ID for item attributes.
• MTL_SECONDARY_INVENTORIES — joined on SUBINVENTORY_CODE and ORGANIZATION_ID for subinventory details.
• MTL_TRANSACTION_TYPES — joined on TRANSACTION_TYPE_ID for transaction type descriptions.
• CSP_PARTS_LOOPS_B — joined on PARTS_LOOP_ID for parts loop definitions.
• CSP_REP_HIERARCHIES — joined on HIERARCHY_NODE_ID for reporting hierarchy structure.
• CSP_USAGE_HEADERS — parent usage header referenced by USAGE_HEADER_ID.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID for security group context.

These relationships place CSP_USAGE_HISTORIES at the center of Spares Management usage analytics, connecting inventory dimensions to planning hierarchies and parts loop structures.