BOM Where Used (Exploded) Stored Procedure for Dynamics AX

Thu Dec 29 2011

Below is a stored procedure that will find all BOM parents where the given item is used. The item filter will search for all parts with a wildcard.

create procedure sp_IV_BOMWhereUsed
  @itemFilter nvarchar(30)
  set nocount off;

  -- define globals
  declare @dataAreaId nvarchar(5); set @dataAreaid = 'dat'

  -- define temp table for gathering parents
  declare @temp table
    bomId     nvarchar(30),
    itemId    nvarchar(30),  
    bomlevel  int,
    seen      bit,    
    rowgroup  nvarchar(100),
    recType   int,
    recId     int identity  

  -- declare variables to track state
  declare @recId  int; set @recId = 0;
  declare @itemid nvarchar(30);
  declare @bomlevel int; set @bomlevel = 0  
  declare @pass int; set @pass = 0;

  -- add first level items to check
  insert into @temp 
    select '', itemId, 1, 0, '', 1
    from inventTable 
      dataAreaId = @dataAreaId and 
      itemId like replace(@itemFilter, '*', '%')

  -- set first level row group string
  update @temp set rowGroup = cast(power(100,bomLevel) + recId as nvarchar(100)) where rowGroup=''

  -- loop while records
  while exists(select top 1 recId from @temp where seen = 0)

    -- get the next record to process
    set @itemId = null
    select top 1 
      @itemId = itemId,
      @bomLevel = bomLevel,
      @recId = recId
    from @temp 
    where seen = 0

    -- check if no more records
    if @itemId is null break

    -- add the next level of records
    insert into @temp
      select distinct bv.bomId, bv.itemId, @bomLevel + 1, 0, '', 2
      from bomVersion bv inner join bom b on bv.dataAreaId = b.dataAreaid and bv.bomId = b.bomId
        bv.dataAreaId = @dataAreaId and
        b.itemId = @itemId and
        not exists( select recId from @temp where bomId = bv.bomId and itemId = bv.itemId)  

    -- set the row group for these new records
    update @temp set 
      rowGroup = (select top 1 rowGroup from @temp where recid=@recid) + cast(100 + recId as nvarchar(100)) 

    -- increate the pass
    set @pass = @pass + 1

    -- remember this record was seen
    update @temp set seen = 1 where recid = @recId


  -- return the records
  select t.*, i.itemName,
    replicate('.', 4 * (t.bomlevel-1)) as indent
    @temp t
    inner join
    inventTable i
    on i.dataAreaId = @dataAreaid and
       t.itemId = i.itemId

  order by