CST Supply Chain Indented Bills of Material Cost – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

This report is based on the (static) Oracle Supply Chain Indented Bills of Material Cost Report and merely sums up the available information from the Cost Type. It does not do a Cost Rollup and as a result, the “Extended Cost” column might not add up to the total item cost for the assembly especially if changes have been made to the bills of material, routing or item costs, since the last cost rollup. If this is the case, run a Supply Chain Cost Rollup in Pending or some other cost type (such as Current) for reporting purposes, to synchronize the cost information and then use this report, using the same cost type, to correctly report your item costs.

Imported from BI Publisher Description: Supply Chain Indented Bills of Material Cost Report Application: Bills of Material Source: Supply Chain Indented Bills of Material Cost Report (XML) Short Name: CSTRSCCRI_XML DB package: BOM_CSTRSCCR_XMLP_PKG

–	Version Modified on Modified by Description
–	======= =========== ============== =========================================
–	1.0 04-APR-2023 Eric Clegg Initial Conversion
–	1.1 24-DEC-2023 Eric Clegg Added Multi-Select Item Parameter

Report Parameters

Cost Type, Organization, Assignment Set, Report Number of Levels, Exclude Level, Past Rollup, Effective Date, Include Inactive BOMs, Include Unimplemented ECOs, Alternate Bill, Engineering Bills, Item, Item From, Item To, Category set, Category From, Category To

Oracle EBS Tables Used

cst_sc_bom_structures, mtl_system_items_vl, mtl_item_categories, mtl_categories_kfv, org_organization_definitions, gl_sets_of_books, fnd_currencies, bom_parameters, bom_inventory_components, hr_organization_information, cst_item_costs, mtl_parameters, cst_item_cost_details, cst_cost_elements, bom_resources, bom_departments

Report Categories

BI Publisher, Enginatics, R12 only

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource	Link
Excel Example Output	CST Supply Chain Indented Bills of Material Cost - Item Cost Detail 31-Oct-2023 055034 (1).xlsx
Blitz Report™ XML Import	CST_Supply_Chain_Indented_Bills_of_Material_Cost.xml
Full SQL on Enginatics	www.enginatics.com/reports/cst-supply-chain-indented-bills-of-material-cost/

Case Study & Technical Analysis: CST Supply Chain Indented Bills of Material Cost

Executive Summary

The CST Supply Chain Indented Bills of Material Cost report is the “Blueprints with Price Tags” for manufacturing finance. It provides a hierarchical view of an assembly’s cost structure, exploding the Bill of Material (BOM) level by level to show exactly how the final cost is built up. Unlike a flat “Item Cost” report, this report preserves the parent-child relationships, allowing users to see not just that an assembly costs $100, but that $40 came from Sub-Assembly A (which in turn has $20 of raw material and $20 of labor).

Business Challenge

Understanding the cost of a complex product (like a car or a server) is difficult because the costs are buried in layers of sub-assemblies.

The “Black Box” Assembly: If a top-level assembly cost increases by 5%, where did it come from? A raw material price hike at Level 4? A routing change at Level 2?
Supply Chain Complexity: In a multi-org environment, Sub-Assembly A might be manufactured in Plant 1 and shipped to Plant 2. The cost at Plant 2 includes the manufacturing cost at Plant 1 plus freight and transfer charges.
Make vs. Buy Analysis: To decide whether to outsource a sub-assembly, you need to know its full “rolled-up” cost, including all lower-level components and value-added activities.

The Solution

This report visualizes the “Cost Rollup” results in a tree structure.

Indented Visualization: It uses indentation (Level 1, .Level 2, ..Level 3) to represent the BOM depth. This makes it easy to trace a cost component from the bottom of the structure to the top.
Supply Chain Awareness: It leverages the Supply Chain Cost Rollup tables (CST_SC_BOM_STRUCTURES), which means it understands cross-organization sourcing rules. If Item X is sourced from Org Y, the report follows that path.
Detailed Cost Elements: For each component at every level, it breaks down the cost into:
- Material: The cost of the component itself.
- Resource/Overhead: The value added at that specific step.
- Extended Cost: Quantity per Assembly * Unit Cost, showing the total contribution of that component to the parent.

Technical Architecture (High Level)

The query is based on the output of the Oracle Supply Chain Cost Rollup engine.

CST_SC_BOM_STRUCTURES: This is the core table. When you run a Supply Chain Cost Rollup, Oracle populates this table with the exploded BOM structure. The report queries this table using the Rollup ID generated by that process.
Static vs. Dynamic: As noted in the description, this report is static. It reads the snapshot taken during the last cost rollup. It does not re-calculate costs based on the current live BOM. This is a critical distinction for users: if you change a BOM today, you must run a Cost Rollup before this report will reflect the change.
CTE Structure:
- q_assembly: Fetches the top-level item details.
- q_components: Fetches the exploded children from CST_SC_BOM_STRUCTURES.
- q_cost_details: Joins to CST_ITEM_COST_DETAILS to get the granular cost element breakdown for each component.

Parameters & Filtering

Cost Type: The cost set to report (e.g., “Frozen”, “Pending”).
Rollup ID / Assignment Set: Identifies the specific cost rollup run to analyze.
Report Number of Levels: Limits the depth of the explosion (e.g., show only top 3 levels).
Effective Date: Used to filter BOM components based on effectivity.

Performance & Optimization

Pre-Calculated Structure: Because the heavy lifting of exploding the BOM is done by the C++ Cost Rollup engine (and stored in CST_SC_BOM_STRUCTURES), this report is relatively fast compared to a query that tries to walk the BOM_INVENTORY_COMPONENTS tree recursively in real-time.
Indexed Access: The query relies heavily on ROLLUP_ID, which is indexed, ensuring efficient retrieval of the specific rollup data.

FAQ

Q: Why doesn’t the “Extended Cost” add up to the “Item Cost”? A: This usually happens if the BOM or Item Costs have changed after the last Cost Rollup was run. Since the report reads the static rollup structure but might join to current item details (depending on the exact join logic), discrepancies can occur. Always run a fresh Cost Rollup before running this report for accurate analysis.

Q: Can I see costs for a specific Department? A: Yes, the report joins to BOM_DEPARTMENTS and BOM_RESOURCES, allowing you to see the resource costs contributed by specific work centers.

Q: What is “Phantom” logic? A: A “Phantom” assembly is a non-stocked sub-assembly that is built transiently. The report respects the Phantom Flag, typically blowing through the phantom level to show the underlying components directly attached to the parent.

CST Supply Chain Indented Bills of Material Cost | Oracle EBS SQL Report

This report is based on the (static) Oracle Supply Chain Indented Bills of Material Cost Report and merely sums up the available information from the Cost…