Odoo MRP の BoM コスト積み上げレポートを作成する方法

By the end of this recipe, your Odoo 19 manufacturing operation will produce a fully recursive Bill of Materials cost rollup showing the total cost of any finished good — components plus labor plus overhead — all the way down through sub-assemblies, with what-if scenarios for component price changes. Skill required: SQL fluency + Odoo MRP context. Time required: 3 hours setup, 30 minutes per scenario run. ECOSIRE has built this for manufacturers from food to electronics, and the recipe below is the playbook.

The reason most cost rollups in Odoo are wrong: they use the top-level BoM's component costs but ignore that those components themselves are manufactured (with their own BoMs). Without recursive explosion, the cost is understated by 30 to 60 percent in any operation with sub-assemblies. The recipe below uses a recursive CTE to roll up correctly.

What you will need

• Odoo version: 17, 18, or 19 with mrp (manufacturing) module installed.
• BoMs configured with components and operations.
• Work centers with hourly cost rates set on each.
• Read-only Postgres for SQL views.
• Time: 3 hours.

Step-by-step

1. Build the recursive BoM cost view

CREATE OR REPLACE VIEW bi.bom_cost_rollup AS
WITH RECURSIVE explosion AS (
    -- Level 0: top-level BoMs
    SELECT
        bom.id AS root_bom_id,
        bom.product_tmpl_id AS root_product_tmpl_id,
        bom.id AS bom_id,
        bom.product_qty AS bom_qty,
        bom_line.product_id AS component_id,
        bom_line.product_qty AS component_qty,
        0 AS level
    FROM mrp_bom bom
    JOIN mrp_bom_line bom_line ON bom.id = bom_line.bom_id
    UNION ALL
    -- Recurse into component BoMs
    SELECT
        e.root_bom_id,
        e.root_product_tmpl_id,
        sub_bom.id AS bom_id,
        sub_bom.product_qty,
        sub_bom_line.product_id,
        sub_bom_line.product_qty * (e.component_qty / e.bom_qty) AS component_qty,
        e.level + 1
    FROM explosion e
    JOIN product_product pp ON e.component_id = pp.id
    JOIN mrp_bom sub_bom ON sub_bom.product_tmpl_id = pp.product_tmpl_id
    JOIN mrp_bom_line sub_bom_line ON sub_bom.id = sub_bom_line.bom_id
    WHERE e.level < 10  -- safety: max 10 levels deep
)
SELECT
    e.root_bom_id,
    e.root_product_tmpl_id,
    e.component_id,
    pp.default_code AS component_sku,
    pt.name AS component_name,
    SUM(e.component_qty) AS total_qty,
    pt.standard_price AS unit_cost,
    SUM(e.component_qty) * pt.standard_price AS total_component_cost,
    e.level AS deepest_level
FROM explosion e
JOIN product_product pp ON e.component_id = pp.id
JOIN product_template pt ON pp.product_tmpl_id = pt.id
WHERE NOT EXISTS (
    -- Only count leaf components (those without their own BoM)
    SELECT 1 FROM mrp_bom WHERE product_tmpl_id = pt.id
)
GROUP BY 1,2,3,4,5,7,9;

Verification: for a known BoM, the sum of total_component_cost matches the manual rollup.

2. Add operation (labor) cost

CREATE OR REPLACE VIEW bi.bom_operation_cost AS
SELECT
    bom.id AS bom_id,
    SUM(op.time_cycle * wc.costs_hour / 60.0) AS total_labor_cost
FROM mrp_bom bom
JOIN mrp_routing_workcenter op ON bom.id = op.bom_id
JOIN mrp_workcenter wc ON op.workcenter_id = wc.id
GROUP BY bom.id;

time_cycle is in minutes per BoM batch. costs_hour is the work center's hourly cost (loaded with overhead).

Verification: a 10-minute operation at a $60/hour work center contributes $10 of labor.

3. Add overhead allocation

Overhead can be a flat percentage on top of component+labor, or per work-center burden. For a flat 20% overhead:

CREATE OR REPLACE VIEW bi.bom_total_cost AS
SELECT
    rollup.root_bom_id,
    rollup.root_product_tmpl_id,
    pt.name AS finished_good_name,
    SUM(rollup.total_component_cost) AS components_cost,
    COALESCE(op.total_labor_cost, 0) AS labor_cost,
    (SUM(rollup.total_component_cost) + COALESCE(op.total_labor_cost, 0)) * 0.20 AS overhead_cost,
    SUM(rollup.total_component_cost) +
        COALESCE(op.total_labor_cost, 0) +
        (SUM(rollup.total_component_cost) + COALESCE(op.total_labor_cost, 0)) * 0.20 AS total_unit_cost
FROM bi.bom_cost_rollup rollup
JOIN product_template pt ON rollup.root_product_tmpl_id = pt.id
LEFT JOIN bi.bom_operation_cost op ON rollup.root_bom_id = op.bom_id
GROUP BY rollup.root_bom_id, rollup.root_product_tmpl_id, pt.name, op.total_labor_cost;

Verification: total_unit_cost on a known SKU matches the cost accountant's manual computation.

4. Compare to standard cost

Add a column showing variance vs the SKU's standard_price:

SELECT
    finished_good_name,
    pt.standard_price AS booked_cost,
    total_unit_cost AS computed_cost,
    total_unit_cost - pt.standard_price AS variance,
    ROUND(100.0 * (total_unit_cost - pt.standard_price) / NULLIF(pt.standard_price, 0), 1) AS variance_pct
FROM bi.bom_total_cost
JOIN product_template pt ON root_product_tmpl_id = pt.id
ORDER BY ABS(variance_pct) DESC;

Items with variance > 5% need standard cost update.

Verification: large variances correlate with recently changed component costs.

5. What-if scenario: component price change

Build a function or stored proc that takes a component_id + new price and recomputes:

CREATE OR REPLACE FUNCTION bi.what_if_cost(p_component_id int, p_new_price numeric)
RETURNS TABLE (finished_good_name text, current_cost numeric, scenario_cost numeric, delta numeric) AS $$
    SELECT
        pt.name,
        btc.total_unit_cost,
        btc.total_unit_cost +
            (rollup.total_qty * (p_new_price - pt_comp.standard_price))
            FILTER (WHERE rollup.component_id = p_component_id) AS scenario_cost,
        (rollup.total_qty * (p_new_price - pt_comp.standard_price))
            FILTER (WHERE rollup.component_id = p_component_id) AS delta
    FROM bi.bom_total_cost btc
    JOIN bi.bom_cost_rollup rollup ON btc.root_bom_id = rollup.root_bom_id
    JOIN product_template pt ON btc.root_product_tmpl_id = pt.id
    JOIN product_product pp ON rollup.component_id = pp.id
    JOIN product_template pt_comp ON pp.product_tmpl_id = pt_comp.id
    WHERE rollup.component_id = p_component_id;
$$ LANGUAGE SQL;

Call: SELECT * FROM bi.what_if_cost(1234, 5.00); to see how a component price change to $5 affects all dependent finished goods.

Verification: the delta makes intuitive sense.

6. Build a Pivot view in Odoo

Expose bi.bom_total_cost as a non-editable Odoo model and add a Pivot view: rows by finished_good_name, columns by month, measure by total_unit_cost.

7. Schedule monthly cost roll

Cron rebuilds the rollup view nightly so dashboards always show current state:

REFRESH MATERIALIZED VIEW CONCURRENTLY bi.bom_total_cost_mat;

(If you turn the view into a materialized view for performance, you must REFRESH periodically.)

Verification: the dashboard reflects component price changes within 24 hours.

8. Tie back to actual production

After a manufacturing order closes, compare the actual cost (from stock.valuation.layer) to the rollup-predicted cost. Variance = MO efficiency or scrap. Track this as an MFG KPI.

Verification: positive variance = MO produced under budget; negative = over.

Common mistakes

• No recursion limit. A circular BoM (rare but happens) causes infinite recursion. Always cap at 10 levels.
• Using product_uom_qty without UoM conversion. Components in different units must be normalized.
• Ignoring scrap allowances. Real-world BoMs have scrap %; build a scrap-adjusted variant.
• Hardcoding overhead %. Run separate calculations for direct, indirect, and S&GA overhead.
• Not pinning to a date. Component prices change daily. Lock cost rollups to month-end snapshots.

Going further

Time-phased BoM: components effective on different dates. Add start_date/end_date filters. Useful when an old component is being phased out and a new one phased in — the rollup respects which is active on the manufacturing-order date.

Multi-currency components: components priced in foreign currencies need real-time FX translation. Pull current period-end rate from res.currency.rate and apply to the foreign-currency component cost. The rollup then shows total cost in company currency.

Yield-adjusted cost: pair with operational yield data to compute "as-built" cost vs theoretical. If the operational yield is 95% (5% scrap), divide theoretical cost by 0.95 to get the realistic per-unit cost. Pull yield from mrp.production historical data.

Dynamic re-routing for cost optimization: if a make-vs-buy decision is cheaper, flag the BoM for review. Compare manufactured cost (from rollup) against latest purchase price for the same item; if buy is 20%+ cheaper, alert the planner.

Lifecycle costing: sum up costs across multi-stage routings (raw material > sub-assembly > test > final). Each stage adds labor + scrap + WIP carry. Useful for high-precision manufacturing.

Volume-based cost curves: component prices vary by purchase volume. Build a "what would my cost be at 2x volume?" simulation by applying volume-discount tiers.

Outsourced operations: subcontracted operations have their own cost (subcontractor invoice). Add them as work-center types and include in the rollup.

ECN (Engineering Change Notice) impact analysis: when a design change adds a component, run the what-if function across all dependent finished goods to forecast the cost impact. Drives ECN approval workflows.

Cost variance dashboard: compare last-month standard cost vs current rollup cost per finished good. Items with >5% variance need standard cost update or process investigation.

Multi-plant cost comparison: same product made in two plants has different costs (labor rate differs, overhead allocation differs). Run rollup per plant and compare. Drives sourcing decisions.

For full MRP cost analytics including yield monitoring, scrap analysis, and predictive cost variance, ECOSIRE Odoo customization builds the entire stack. Pair this with how to calculate COGS by product category.

Frequently Asked Questions

How do I handle phantom BoMs?

Phantoms (products that explode at consumption time) need special treatment — recurse through them but don't book inventory. The BoM has type='phantom'; filter accordingly. Phantom components flow through to the parent BoM as if they were direct components of the finished good.

What about Variant BoMs?

Variants share BoM but with attribute-specific component substitutions. The rollup needs to evaluate the variant attributes per BoM line. Add bom_line.attribute_value_ids filter to the recursive query.

Should I use materialized views or regular views?

Materialized for read-heavy dashboards (refresh nightly). Regular for what-if scenarios that need real-time component prices. Mix both: a materialized view for the bulk of cases, regular views for ad-hoc what-if analysis.

How do I cost a one-off custom product?

Use the BoM "kit" feature for the custom product, with components from inventory. The rollup logic above handles it. For truly one-off products, use a manufacturing order with on-the-fly BoM that doesn't get saved as a permanent BoM record.

What's the difference between Standard, AVCO, and FIFO?

Standard cost: fixed value updated periodically. AVCO: weighted average cost recomputed per move. FIFO: first-in-first-out, components consumed at the cost they were received. AVCO is most common; FIFO for regulated industries (food, pharma).

How do I track variance from standard?

Compute variance = actual SVL value - standard cost × quantity. Post variance to a "Manufacturing Variance" account during MO close. The amount tells you how much your standard cost is off.

Can I run rollup on draft (uncommitted) BoMs?

Yes — the recursive CTE doesn't care about state. Useful for "what if I changed this BoM" preview before saving.

How do I cost work-in-progress?

WIP is the value of partially-completed manufacturing orders. Compute as MO start cost + materials consumed - finished good output. Track with a dedicated WIP account.

What about by-products (co-products from the same MO)?

Allocate cost between primary product and by-products based on relative sales value or quantity. Configure on the BoM with byproduct_ids. The rollup handles the split.

How do I handle subcontracted components?

Subcontracted operations have their own cost (subcontractor invoice). Add as mrp.routing.workcenter of type "subcontracted" or use the dedicated subcontracting module in v17+.

What's the typical accuracy of a cost rollup?

Within 1-3% of actual full-load cost is achievable with good data. Beyond 5% indicates missing overhead allocations, scrap modeling, or labor rate misalignment.

For complex MRP costing setups including ML-driven yield optimization, ECOSIRE Odoo customization builds full stacks. Or read how to track inventory turnover.