Search Results csp_usage_histories_v1

Overview

The CSP_USAGE_HISTORIES_V1 view is a reporting object owned by the APPS schema within the CSP (Spares Management) product family of Oracle E-Business Suite. It is documented as a VALID view and, per the ETRM metadata, is used by a spreadtable to display usage history. In practical terms, this view provides a consolidated, pre-aggregated representation of historical spare-parts usage, sales-order demand, and forecast demand, keyed by period and inventory organization. It serves reporting, analytics, and integration consumers that need summarized usage history rather than the raw transactional grain held in the underlying base table.

Because the view performs aggregation and filtering internally, consumers are relieved of the responsibility to filter by history data type or to group by the dimensional keys. This makes CSP_USAGE_HISTORIES_V1 a convenient foundation for spares-planning dashboards, replenishment analysis, and any downstream integration that requires periodic demand history with usage, sales-order, and forecast quantities already separated into distinct columns.

Underlying Base Objects

The documented referenced base object is CSP_USAGE_HISTORIES, exposed through a SYNONYM. The view text confirms this dependency, selecting from CSP_USAGE_HISTORIES CUH. The base table stores usage history records tagged by a HISTORY_DATA_TYPE, and the view restricts its result set with the predicate WHERE HISTORY_DATA_TYPE = 0. Consequently, only the history records belonging to data type 0 are surfaced through the view; other history buckets retained in the base table are excluded.

The view groups on PERIOD_START_DATE, PERIOD_TYPE, INVENTORY_ITEM_ID, ORGANIZATION_ID, PARTS_LOOP_ID, SUBINVENTORY_CODE, and HIERARCHY_NODE_ID. Aggregation is performed via conditional sums over the QUANTITY column, decoded by TRANSACTION_TYPE_ID. Transaction type 93 contributes to one summed output, type 33 contributes to another, and type -1 contributes to a third. Both conditional sums for types 93 and 33 are combined into a single TOTAL column. Missing values are handled with NVL(..., 0) to avoid null propagation.

Key Columns

• PERIOD_START_DATE — The beginning date of the reporting period; a core grouping and time-bucketing key.
• PERIOD_TYPE — The granularity or classification of the period (for example, daily, weekly, or monthly buckets as defined by the product).
• INVENTORY_ITEM_ID — The item identifier for which usage is being summarized.
• ORGANIZATION_ID — The inventory organization that owns the usage record.
• PARTS_LOOP_ID — Identifier linking the record to a parts loop used in spares planning configuration.
• SUBINVENTORY_CODE — The subinventory within the organization where the movement occurred.
• HIERARCHY_NODE_ID — The node in the product hierarchy that the record rolls up to.
• USAGE_QUANTITY — Derived from transaction type 93 sums; represents usage activity for the period.
• SO_QUANTITY — Derived from transaction type 33 sums; represents sales-order related quantity.
• FORECAST_QUANTITY — Derived from transaction type -1 sums; represents forecasted demand quantity for the period.
• TOTAL — The combined sum of the type 93 and type 33 contributions.

Common Use Cases and Queries

A frequent requirement, and the reason this view is often retrieved when users search on the term forecast_quantity, is to compare actual usage against forecasted demand for a given item and organization across periods. The view exposes FORECAST_QUANTITY alongside USAGE_QUANTITY and SO_QUANTITY directly, so no recalculation of transaction types is required.

SELECT period_start_date,
       period_type,
       inventory_item_id,
       organization_id,
       usage_quantity,
       so_quantity,
       forecast_quantity,
       total
FROM   csp_usage_histories_v1
WHERE  inventory_item_id = :item_id
AND    organization_id  = :org_id
ORDER BY period_start_date;

Another common scenario is assembling a spares replenishment worksheet that aggregates forecast versus usage variance over a rolling window. Because the view already performs grouping, such queries reduce to filtering and simple arithmetic. Reporting tools and spreadtable-based pages consume this view directly to populate usage-history grids, leveraging its aggregated shape to minimize application-side processing.