Search Results cst_cg_item_costs_view




Overview
Explore in AI Chat

Overview

CST_CG_ITEM_COSTS_VIEW is a cost-management view owned by the APPS schema in Oracle E-Business Suite, associated with the Bills of Material (BOM) product family. Its documented purpose is to expose the this-level, previous-level, and total elemental costs of an item within an average costing organization. In EBS costing terminology, "elemental costs" refers to the individual cost elements—material, material overhead, resource, outside processing, and overhead—that roll up into the total item cost.

The view reconciles two data sources. The first is the item cost record held in CST_ITEM_COSTS, which stores elemental and rollup cost attributes for an inventory item in a cost group. The second is the cost quantity layer stored in CST_QUANTITY_LAYERS, which represents the average-cost layer created when an inventory transaction or cost update introduces quantity and value into the organization. The view is therefore central to any reporting or integration that must present per-layer average costing data alongside the standard item cost definition. Its LAYER_QUANTITY column, matching the user's search term, is the quantity associated with a given costing layer.

Underlying Base Objects

The view is defined over three documented base objects, exposed to APPS through synonyms:

  • CST_ITEM_COSTS (SYNONYM) — the item-level cost record containing elemental and total cost columns for the item and cost group.
  • CST_QUANTITY_LAYERS (SYNONYM) — the average-cost quantity layers, keyed by layer, cost group, and inventory item.
  • MTL_PARAMETERS (SYNONYM) — the organization parameters table, providing the organization's primary cost method and default cost group used in the DECODE logic.

The join logic is expressed through DECODE constructs. When a cost quantity layer exists for the item (CQL.INVENTORY_ITEM_ID is not null), the view returns the layer's cost group and layer-quantity values; otherwise it falls back to the item-cost record and organization defaults. Similarly, each elemental cost column is drawn from the layer when present, and from CST_ITEM_COSTS otherwise.

Key Columns

  • LAYER_ID — the quantity layer identifier; defaults to 1 when no layer is present.
  • COST_GROUP_ID — the cost group for the layer, or the organization default cost group (with primary cost method 1 mapping to 1) when no layer exists.
  • LAYER_QUANTITY — the quantity belonging to the costing layer; defaults to 1 when no layer is present. This is the column most relevant to the user's search.
  • INVENTORY_ITEM_ID, ORGANIZATION_ID — the item and inventory organization identifiers.
  • INVENTORY_ASSET_FLAG, LOT_SIZE, SHRINKAGE_RATE, BASED_ON_ROLLUP_FLAG, DEFAULTED_FLAG, COST_UPDATE_ID — item-cost attributes carried from CST_ITEM_COSTS.
  • Previous-level (PL_*), this-level (TL_*), and total elemental cost columns — material, material overhead, resource, outside processing, and overhead — each resolved from the layer or the item cost record as appropriate.

Common Use Cases and Queries

Typical uses include average-cost layer reporting, reconciliation of layer quantity against elemental layer value, and integration feeds requiring this-level versus previous-level cost breakdowns.

To inspect layer quantities for an item in an organization:

SELECT inventory_item_id, organization_id, layer_id,
       cost_group_id, layer_quantity, material_cost, resource_cost
FROM   apps.cst_cg_item_costs_view
WHERE  organization_id = :org_id
AND    inventory_item_id = :item_id;

To summarize total layer quantity by item:

SELECT inventory_item_id, SUM(layer_quantity) total_qty
FROM   apps.cst_cg_item_costs_view
WHERE  organization_id = :org_id
GROUP  BY inventory_item_id;

Queries should always be constrained by organization and item, since the view spans all average-costing organizations and items accessible to the APPS schema.