CST Supply Chain Indented Bills of Material Cost
Description:
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 |
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
Used tables
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
Categories
Dependencies
If you would like to try one of these Oracle EBS SQLs without having Blitz Report installed, note that some of the reports require functions from utility package xxen_util.
Example Report
CST Supply Chain Indented Bills of Material Cost - Item Cost Detail 31-Oct-2023 055034 (1).xlsx
Report SQL
www.enginatics.com/reports/cst-supply-chain-indented-bills-of-material-cost/
Blitz Report™ import options
CST_Supply_Chain_Indented_Bills_of_Material_Cost.xml
# 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.
Oracle E-Business Suite Reporting Library
We provide an open source Oracle EBS SQLs as a part of operational and project implementation support toolkits for rapid Excel reports generation.
Blitz Report™ is based on Oracle EBS forms technology, and hence requires minimal training. There are no data or performance limitations since the output files are created directly from the database without going through intermediate file formats such as XML.
Blitz Report can be used as BI Publisher and Oracle Discoverer replacement tool. Standard Oracle BI Publisher and Discoverer reports can also be imported into Blitz Report for immediate output to Excel. Typically, reports can be created and version tracked within hours instead of days. The concurrent request output automatically opens upon completion without the need for re-formatting.
The Filters, Columns, Rows and Values fields are used to create and deliver the data in pivot table format with full drill down to details.
The Excel template upload functionality in Blitz Report allows users to create their own layouts by uploading an Excel template with additional sheets and charts, automatically refreshed when the report runs again. This allows to create custom dashboards and more advanced visualizations of report data.
You can download and use Blitz Report free of charge for your first 30 reports.
The installation and implementation process usually takes less than 1 hour; you can refer to our installation and user guides for specific details.
If you would like to optimize your Oracle EBS implementation and or operational reporting you can visit www.enginatics.com to review great ideas and example usage in blog. Or why not try for yourself in our demo environment.
Useful Links
Blitz Report™ – World’s fastest data upload and reporting for Oracle EBS
Oracle Discoverer replacement – importing worksheets into Blitz Report™
Blitz Report™ Questions & Answers
Supply Chain Hub by Blitz Report™
© 2025 Enginatics