We recently had to calculate standard costs for some new assemblies based on standard or actual costs for their components.

Rodin's sculpture The Thinker

I made a recursive view in Microsoft SQL Server that traverses the bill of material tree and calculates the total assembly cost.

Be warned, although I’ve included code to do unit conversions, our application did not require any conversions, and that code has not been tested. Use at your own risk.

So here’s the script:

CREATE FUNCTION [dbo].[SSC_BomCost]
(
	@PartNumber char(31)
)
RETURNS @TheResult
TABLE 
(
	OriginalItemNmbr char(31),
	Cost numeric(19,5)
)
AS
BEGIN

with

-- Here is the base item cost and unit of measure information
-- We use current cost if present, otherwise standard cost

ItemCost as (
  select
    i.itemnmbr,
	case when i.currcost>0 then i.currcost when i.stndcost>0 then i.stndcost else 0 end as Cost,
	i.SELNGUOM as UOFM,
	i.UOMSCHDL
  from dbo.IV00101 i
)
,

-- Here we recurse down the bill of material tree until we find non-zero costs, 
-- or hit leaf nodes, whichever comes first on each path

ComponentTree as (
  select
    c.itemnmbr,
	c.Cost,
	cast(d.EQUOMQTY as numeric(19,5)) as Quantity,
	cast(c.UOFM as char(9)) as UOFM,
	c.UOMSCHDL
  from ItemCost c
  join dbo.BM00101 b
  on c.itemnmbr=b.itemnmbr
  join dbo.IV40202 d
  on d.UOMSCHDL=c.UOMSCHDL
  and d.UOFM=c.UOFM
  and d.EQUIVUOM=b.UOFM
  where c.itemnmbr=@PartNumber
    
  union all
  
  select 
	cast(b.cmptitnm as char(31)),
	c.Cost,
	cast(b.design_qty * d.EQUOMQTY as numeric(19,5)) as Quantity,
	cast(b.UOFM as char(9)) as UOFM,
	c.UOMSCHDL
  from ComponentTree a
  join bm00111 b
  on a.itemnmbr=b.itemnmbr
  join ItemCost c
  on b.cmptitnm=c.itemnmbr
  join IV40202 d
  on d.UOMSCHDL=c.UOMSCHDL
  and d.UOFM=c.UOFM
  and d.EQUIVUOM=b.UOFM
  where a.Cost=0
)
,


-- Here we add all the component costs

bom as (
  select
    cast(@PartNumber as char(31)) as OriginalItemNmbr,
    sum(Quantity * Cost) as Cost
  from ComponentTree
)


-- And return the result

insert into @TheResult
  select 
    OriginalItemNmbr,
	Cost
  from bom
;

return;

end;

And here’s how I invoked it for our purpose. All the new assemblies began with “6-“.

select c.*
from dbo.bm00101 b
cross apply dbo.SSC_BomCost(b.itemnmbr) c
where b.ITEMNMBR like '6-%'
and b.ITEMNMBR in (
  select ITEMNMBR
  from dbo.iv00101
  where ITEMTYPE=1
  and CURRCOST=0
  and STNDCOST=0)
order by 1;

Here’s some sample output. I’ve replaced the calculated costs with random numbers in this listing.

Two columns, part number and cost

Sample output (costs random)

Hope this helps someone else!

One Response to “Calculating New Assembly Costs from Existing Component Costs”

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available