Search Results cst_elemental_costs




Overview
Explore in AI Chat

Overview

The CST_ELEMENTAL_COSTS table is a core data repository within the Oracle E-Business Suite (EBS) Bills of Material (BOM) module, specifically for manufacturing cost management. It serves as the historical ledger for standard elemental costs. Its primary role is to store a detailed, time-stamped record of cost changes for individual cost elements (such as material, resource, overhead, or outside processing) associated with inventory items. This historical data is critical for cost analysis, variance reporting, and auditing the evolution of product costs over time. The table is populated during cost update processes, capturing the state of costs before and after updates to provide a complete audit trail.

Key Information Stored

The table's structure is designed to uniquely identify a specific cost element's history for an item within an organization. Its primary key is a composite of COST_UPDATE_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, and COST_ELEMENT_ID. The COST_UPDATE_ID links the record to a specific cost update transaction in the CST_COST_UPDATES table. The ORGANIZATION_ID and INVENTORY_ITEM_ID identify the specific item whose cost is being tracked. The COST_ELEMENT_ID references the type of cost (e.g., Material, Labor) from the CST_COST_ELEMENTS table. While the provided metadata does not list all columns, typical data stored includes the old (previous) unit cost, the new (current) unit cost, and the effective date of the change for the specified cost element.

Common Use Cases and Queries

This table is essential for generating historical cost reports and analyzing cost trends. A common use case is tracing the cost history of a major component to understand the impact of supplier price changes or labor rate adjustments. Another key scenario is performing a cost rollup analysis to see how historical changes to subassembly costs propagated to a final assembly's total cost over time. A typical query would join to item and cost element descriptive tables to produce a readable report.

Sample Query Pattern:
SELECT cicd.organization_id,
msib.segment1 item_code,
cce.cost_element,
cec.old_cost,
cec.new_cost,
ccu.update_date
FROM bom.cst_elemental_costs cec
JOIN bom.cst_cost_updates ccu ON cec.cost_update_id = ccu.cost_update_id
JOIN inv.mtl_system_items_b msib ON cec.inventory_item_id = msib.inventory_item_id AND cec.organization_id = msib.organization_id
JOIN cst_cost_elements cce ON cec.cost_element_id = cce.cost_element_id
WHERE cec.organization_id = :p_org_id
AND cec.inventory_item_id = :p_item_id
ORDER BY ccu.update_date DESC, cce.cost_element;

Related Objects

The table maintains defined foreign key relationships with other critical costing tables, ensuring referential integrity. The documented relationships are:

  • CST_COST_UPDATES: Joined via CST_ELEMENTAL_COSTS.COST_UPDATE_ID = CST_COST_UPDATES.COST_UPDATE_ID. This links each elemental cost record to the master transaction that caused the update.
  • CST_COST_ELEMENTS: Joined via CST_ELEMENTAL_COSTS.COST_ELEMENT_ID = CST_COST_ELEMENTS.COST_ELEMENT_ID. This provides the descriptive name and type (e.g., Material, Resource) for the cost element.

In practice, queries also frequently join to MTL_SYSTEM_ITEMS_B for item descriptions and CST_ITEM_COSTS or CST_ITEM_COST_DETAILS for the current, non-historical cost perspective.