BOM standard Cost rollup query

[former_member230931]

Hi All,

I wonder if anyone has done a report/query for multilevel BOM standard cost rollup.  Requirement is to rollup the component standard cost stored at the warehouse level up the hierarchy for more than one level of parent items. Report will calculate the cost of parent items from the addition of child item costs. The trouble is when the BOM has more than one level its not easy. Below is an example:

Here the requirement is to get a query calculating L1 cost by adding two child levels of components.

Idea behind this query is to see how far misaligned parent standard cost compered to base component costs.  Since costs are maintained per warehouse new functionality around "production std cost" in 9.2 version cannot be used.

[former_member186712]

Hi Dick

This query is a recursive query and make the changes that you need to get the prices.

WITH BOM (Code, Level, TreeType) AS

( SELECT     T0.Code, 0 as Level, T2.TreeType   FROM     dbo.OITT T0 inner join OITM T2 on T0.Code = T2.ItemCode

UNION ALL 

SELECT         T1.Code , Level +1 , T2.TreeType   FROM  dbo.ITT1 AS T1 inner join OITM T2 on T1.Code = T2.ItemCode     JOIN BOM ON T1.Father = BOM.Code )

SELECT * FROM BOM OPTION (MAXRECURSION 99)

I hope it helps