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™. Oracle EBS module: Cost Management · all Cost Management reports

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

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

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, cst_sc_sourcing_rules, gl_ledgers, cst_organization_definitions, po_vendors, po_vendor_sites_all, mfg_lookups

Report Categories

BI Publisher, Enginatics, R12 only

INV Items, CAC Last Standard Item Cost, CAC New Standard Item Costs

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

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
SQL Source CST Supply Chain Indented Bills of Material Cost.sql
Full Report 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 Solution

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

Technical Architecture (High Level)

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

Parameters & Filtering

Performance & Optimization

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.

© Enginatics GmbH