Dynamics AX 2009 – Fully Exploded BOM / Where Used

The following sql will create a view that can be used to query fully exploded bill of materials as well as find all bill of materials that use an item at any level.

create view ExpBOM
as

with ExpBom (
    DataAreaId,
    Bom,
    Product,
    Material,
    BomDepth,
    BomPath
) as (
    select
        bv.DataAreaId,
        bv.BomId,
        bv.ItemId,
        b.ItemId,
        1,
        convert(varchar(max), bv.ItemId + '|' + b.ItemId) as BomPath
    from BomVersion bv
    join InventTable ibv
        on ibv.DataAreaId = bv.DataAreaId
        and ibv.ItemId = bv.ItemId
    join Bom b
        on b.DataAreaId = bv.DataAreaId
        and b.BomId = bv.BomId
    join InventTable ib
        on ib.DataAreaId = b.DataAreaId
        and ib.ItemId = b.ItemId
    where bv.Approved = 1
        and bv.Active = 1
        and bv.FromDate < getdate()
        and (bv.ToDate = '01-01-1900' or bv.ToDate >= getdate())
        and b.FromDate < getdate()
        and (b.ToDate = '01-01-1900' or b.ToDate >= getdate())
    union all
    select
        bv.DataAreaId,
        bv.BomId,
        bv.ItemId,
        eb.Material,
        eb.BomDepth + 1,
        convert(varchar(max), bv.ItemId + '|' + eb.BomPath) as BomPath
    from BomVersion bv
    join InventTable ibv
        on ibv.DataAreaId = bv.DataAreaId
        and ibv.ItemId = bv.ItemId
    join Bom b
        on b.DataAreaId = bv.DataAreaId
        and b.BomId = bv.BomId
    join ExpBom eb
        on eb.DataAreaId = b.DataAreaId
        and eb.Product = b.ItemId
    where bv.Approved = 1
        and bv.Active = 1
        and bv.FromDate < getdate()
        and (bv.ToDate = '01-01-1900' or bv.ToDate >= getdate())
        and b.FromDate < getdate()
        and (b.ToDate = '01-01-1900' or b.ToDate >= getdate())
)
select * from ExpBom

go

The joins to the InventTable in the common table expression are not necessary as long as your data is clean. However, using them will ensure that only valid product and material item numbers are included.

To view a fully exploded BOM…

select *
from ExpBom
where DataAreaId = 'DAT'
    and Product = 'BACON'

To find all BOMs that use an item at any level…

select *
from ExpBom
where DataAreaId = 'DAT'
    and Material = 'BACON'
Share