Results for “cst_layer_cost_details_v”

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

Overview

CST_LAYER_COST_DETAILS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, classified under the BOM (Bills of Material) product family and the Cost Management (CST) schema areas. It exposes costs by cost layer, cost level, and cost element, consolidating granular layer-level cost records into a form suitable for inquiry screens, cost rollup diagnostics, and integration queries. The view is valid and documented in ETRM 12.2.2, with equivalent availability in 12.1.1.

The view aggregates detail rows from CST_LAYER_COST_DETAILS and joins to CST_QUANTITY_LAYERS to attribute each cost layer to a specific inventory item and organization. It also resolves the LEVEL_TYPE code through MFG_LOOKUPS using lookup type CST_LEVEL, and resolves cost element identity through CST_COST_ELEMENTS. This structure makes the view a practical access point for reporting both the raw level-type code and its descriptive equivalent, the user-facing value exposed as LEVEL_TYPE_DSP.

Underlying Base Objects

Four documented base objects underpin the view:

• CST_LAYER_COST_DETAILS — the primary fact source, providing LAYER_ID, LEVEL_TYPE, COST_ELEMENT_ID, and ITEM_COST amounts. This is the granular cost-layer detail table.
• CST_QUANTITY_LAYERS — supplies the LAYER_ID to INVENTORY_ITEM_ID and ORGANIZATION_ID associations, linking each layer to its item and inventory organization.
• MFG_LOOKUPS — a lookup view queried with LOOKUP_TYPE = 'CST_LEVEL' to translate the LEVEL_TYPE code into a descriptive meaning (presented as LEVEL_TYPE_DSP).
• CST_COST_ELEMENTS — a synonym resolving the cost element description and validating COST_ELEMENT_ID.

The join between CST_LAYER_COST_DETAILS and MFG_LOOKUPS is an outer join (LU3.LOOKUP_CODE (+) = CLCD.LEVEL_TYPE), so layers whose level type has no matching lookup value are still returned, with LEVEL_TYPE_DSP null. The view groups by item, organization, layer, level type, lookup meaning, cost element, and cost element ID, summing ITEM_COST and selecting MAX(ROWID) for a surrogate row identifier.

Key Columns

• ROW_ID — surrogate identifier derived from MAX(CLCD.ROWID); useful for de-duplication or direct row addressing.
• INVENTORY_ITEM_ID — the item for which layered costs are reported.
• ORGANIZATION_ID — the inventory organization owning the cost layer.
• LAYER_ID — the cost layer identifier, shared by CST_LAYER_COST_DETAILS and CST_QUANTITY_LAYERS.
• LEVEL_TYPE — the coded cost level from CST_LAYER_COST_DETAILS, validated against lookup type CST_LEVEL.
• LEVEL_TYPE_DSP — the descriptive meaning of the level type resolved through MFG_LOOKUPS. This is the column most commonly sought by users searching on level_type_dsp, and it is the human-readable counterpart to LEVEL_TYPE.
• ITEM_COST — the summed cost amount for the layer/level/cost-element grouping.
• COST_ELEMENT and COST_ELEMENT_ID — the cost element description and its identifier, joined from CST_COST_ELEMENTS.

Common Use Cases and Queries

Typical scenarios include cost layer analysis, level-type reporting, and reconciliation between layer detail tables and summarized cost views. A common query retrieves cost by level type description for a given item and organization:

• Layer cost by level type: SELECT level_type_dsp, cost_element, SUM(item_cost) FROM cst_layer_cost_details_v WHERE inventory_item_id = :item AND organization_id = :org GROUP BY level_type_dsp, cost_element;
• Level-type drilldown: SELECT layer_id, level_type, level_type_dsp, item_cost FROM cst_layer_cost_details_v WHERE inventory_item_id = :item ORDER BY layer_id;
• Cost element distribution: SELECT cost_element, COUNT(*) layers, SUM(item_cost) FROM cst_layer_cost_details_v WHERE organization_id = :org GROUP BY cost_element;

Because the view is a GROUP BY aggregate, joins against it should account for the fact that cost amounts are pre-summed per layer, level type, and cost element combination. The presence of LEVEL_TYPE_DSP allows report definitions to avoid separately joining MFG_LOOKUPS, simplifying query design in cost rollup and inventory valuation reporting.