Results for “cst_elemental_cost_pk”

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

Overview

CST_ELEMENTAL_COSTS is a standard Oracle EBS table owned by the BOM schema that stores standard elemental cost history. It records the cost of an inventory item broken down by individual cost elements (such as material, material overhead, resource, outside processing, and overhead) for each cost update, organization, and item combination. This table effectively preserves a historical, element-level snapshot of standard costs, allowing organizations to trace how individual cost components evolved across successive cost updates rather than only storing the final rolled-up item cost.

In the ETRM 12.2.2 documented schema, the table contains 14 columns. Under an optional Data Vault modeling interpretation, CST_ELEMENTAL_COSTS is classified heuristically as a link table, since its primary key is composed entirely of foreign key references — COST_UPDATE_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, and COST_ELEMENT_ID — forming a many-to-many association between cost updates, inventory items, and cost elements. This classification is offered as a modeling suggestion rather than a mandatory design pattern.

Key Information Stored

The most significant columns fall into identifying keys, cost values, and audit/context fields.

• COST_UPDATE_ID — Identifies the cost update (costing run) to which this elemental cost record belongs. This is part of the primary key and a foreign key to CST_COST_UPDATES.
• ORGANIZATION_ID — The inventory organization in which the item cost applies. Part of the primary key.
• INVENTORY_ITEM_ID — The inventory item whose elemental cost is recorded. Part of the primary key.
• COST_ELEMENT_ID — The cost element (for example material, overhead, or resource) to which the cost value applies. Part of the primary key and a foreign key to CST_COST_ELEMENTS.
• STANDARD_COST — The standard cost amount for the given item, organization, cost update, and cost element. This is the core measure of the table.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns tracking who created and last modified each row and when.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program context columns identifying the process that populated or updated the row.

The surrogate primary key is defined by the constraint CST_ELEMENTAL_COST_PK, while the unique index CST_ELEMENTAL_COSTS_U1 (COST_UPDATE_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, COST_ELEMENT_ID) serves as the business-key candidate, enforcing one elemental cost record per cost update, organization, item, and cost element.

Common Use Cases and Queries

Typical scenarios include element-level cost analysis, cost rollup reconciliation, and historical trending of standard costs. A common query joins the table to CST_COST_ELEMENTS to resolve element names:

• Comparing elemental costs across two cost updates to explain changes in an item's standard cost.
• Reporting material versus overhead composition of an item's standard cost for a given organization.
• Auditing which concurrent program populated a particular elemental cost record.

A representative SQL pattern:

SELECT cec.inventory_item_id, cec.organization_id, cce.cost_element, cec.standard_cost
FROM cst_elemental_costs cec, cst_cost_elements cce
WHERE cec.cost_element_id = cce.cost_element_id
AND cec.cost_update_id = :cost_update_id
AND cec.organization_id = :org_id;

Related Objects

The following objects are most significant in relation to CST_ELEMENTAL_COSTS, based on documented foreign key relationships:

• CST_COST_UPDATES — Referenced via CST_ELEMENTAL_COSTS.COST_UPDATE_ID. Defines the cost update context for each elemental cost row.
• CST_COST_ELEMENTS — Referenced via CST_ELEMENTAL_COSTS.COST_ELEMENT_ID. Provides the cost element definitions.
• MTL_SYSTEM_ITEMS_B — Joined via INVENTORY_ITEM_ID to resolve item numbers and descriptions.
• HR_ORGANIZATION_UNITS / ORG_ORGANIZATION_DEFINITIONS — Joined via ORGANIZATION_ID to resolve organization names.
• CST_ITEM_COSTS — The item-level rolled-up cost table commonly compared against elemental costs during reconciliation.
• CST_COST_UPDATES and related costing APIs (for example, the standard cost update and cost rollup concurrent programs) generate and consume the rows in this table.