Exploded BOM

Submitted by Nathan Clouse on 10/19/15.

This will give a fully exploded BOM for products in AX. This will include circular BOMs, if you have any, up to level 99. This is based on this post; I just gave it a few upgrades. Usage remains the same between the two different versions. This is written for 2012 by will work with 2009. You can also use the linked post as a starting point in 2009.

AX Version:

AX 2012 R3

Module:

Inventory Management

CREATE view [dbo].[vw_inventExplodedBOM]
as
 with ExpBom (
    DataAreaId,
    Bom,
    Product,
    ProductCountry,
    ProductInventDimId,
    ProductConfigId,
    ProductInventSizeId,
    ProductInventColorId,
    productInventStyleId,
    ProductName,
    Material,
    MaterialCountry,
    MaterialInventDimId,
    MaterialConfigId,
    MaterialInventSizeId,
    MaterialInventColorId,
    MaterialInventStyleId,
    MaterialName,
    MaterialQty,
    BomDepth,
    BomPath
) AS (SELECT	bv.dataareaid, 
                bv.bomid, 
                bv.itemid,
                ibv.OrigCountryRegionId,
                dimProduct.inventdimid, 
                dimProduct.configid, 
                dimProduct.inventsizeid, 
                dimProduct.inventcolorid, 
                dimProduct.inventstyleid, 
                erptProduct.name,  
                b.itemid, 
                ib.OrigCountryRegionId,
                dimMaterial.inventdimid, 
                dimMaterial.configid, 
                dimMaterial.inventsizeid, 
                dimMaterial.inventcolorid, 
                dimMaterial.inventstyleid, 
                erptMaterial.name, 
                b.bomqty, 
                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 
                     AND ibv.partition = bv.partition 
                JOIN bom b 
                  ON b.dataareaid = bv.dataareaid 
                     AND b.bomid = bv.bomid 
                     AND b.partition = bv.partition 
                JOIN inventtable ib 
                  ON ib.dataareaid = b.dataareaid 
                     AND ib.itemid = b.itemid 
                     AND ib.partition = b.partition 
                JOIN inventdim dimProduct 
                  ON dimProduct.inventdimid = bv.inventdimid 
                     AND dimProduct.dataareaid = bv.dataareaid 
                     AND dimProduct.partition = bv.partition 
                JOIN inventdim dimMaterial 
                  ON dimMaterial.inventdimid = b.inventdimid 
                     AND dimMaterial.dataareaid = b.dataareaid 
                     AND dimMaterial.partition = b.partition 
                JOIN dbo.ecoresproduct erpProduct 
					 ON ibv.product = erpProduct.recid 
					 and erpProduct.partition = bv.partition
				JOIN dbo.ecoresproducttranslation erptProduct 
					 ON erptProduct.product = erpProduct.recid 
					 and erptProduct.partition = bv.partition
					 AND erptProduct.languageid = 'en-us' 
                JOIN dbo.ecoresproduct erpMaterial 
					 ON ib.product = erpMaterial.recid 
					 and erpMaterial.partition = bv.partition
				JOIN dbo.ecoresproducttranslation erptMaterial
					 ON erptMaterial.product = erpMaterial.recid 
					 and erptMaterial.partition = bv.partition
					 AND erptMaterial.languageid = 'en-us'
         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, 
                ibv.OrigCountryRegionId,
                dimProduct.inventdimid, 
                dimProduct.configid, 
                dimProduct.inventsizeid, 
                dimProduct.inventcolorid, 
                dimProduct.inventstyleid, 
                erptProduct.name,  
                eb.material, 
                ib.OrigCountryRegionId,
                dimMaterial.inventdimid, 
                dimMaterial.configid, 
                dimMaterial.inventsizeid, 
                dimMaterial.inventcolorid, 
                dimMaterial.inventstyleid, 
                erptMaterial.name, 
                eb.materialqty, 
                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 
                     AND ibv.partition = bv.partition 
                JOIN bom b 
                  ON b.dataareaid = bv.dataareaid 
                     AND b.bomid = bv.bomid 
                     AND b.partition = bv.partition 
                JOIN inventtable ib 
                  ON ib.dataareaid = b.dataareaid 
                     AND ib.itemid = b.itemid 
                     AND ib.partition = b.partition 
                JOIN expbom eb 
                  ON eb.dataareaid = b.dataareaid 
                     AND eb.product = b.itemid 
                JOIN inventdim dimProduct 
                  ON dimProduct.inventdimid = bv.inventdimid 
                     AND dimProduct.dataareaid = bv.dataareaid 
                     AND dimProduct.partition = bv.partition 
                JOIN inventdim dimMaterial 
                  ON dimMaterial.inventdimid = eb.materialinventdimid 
                     AND dimMaterial.dataareaid = eb.dataareaid 
                JOIN dbo.ecoresproduct erpProduct 
					 ON ibv.product = erpProduct.recid 
					 and erpProduct.partition = bv.partition
				JOIN dbo.ecoresproducttranslation erptProduct 
					 ON erptProduct.product = erpProduct.recid 
					 and erptProduct.partition = bv.partition
					 AND erptProduct.languageid = 'en-us' 
                JOIN dbo.ecoresproduct erpMaterial 
					 ON ib.product = erpMaterial.recid 
					 and erpMaterial.partition = bv.partition
				JOIN dbo.ecoresproducttranslation erptMaterial
					 ON erptMaterial.product = erpMaterial.recid 
					 and erptMaterial.partition = bv.partition
					 AND erptMaterial.languageid = 'en-us'
         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() ) 
                AND eb.bomdepth + 1 < 99 
         UNION ALL 
         SELECT bv.dataareaid, 
                bv.bomid, 
                bv.itemid, 
                ibv.OrigCountryRegionId,
                dimProduct.inventdimid, 
                dimProduct.configid, 
                dimProduct.inventsizeid, 
                dimProduct.inventcolorid, 
                dimProduct.inventstyleid, 
                erptProduct.name,  
                eb.material, 
                ib.OrigCountryRegionId,
                dimMaterial.inventdimid, 
                dimMaterial.configid, 
                dimMaterial.inventsizeid, 
                dimMaterial.inventcolorid, 
                dimMaterial.inventstyleid, 
                erptMaterial.name, 
                eb.materialqty, 
                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 
                     AND ibv.partition = bv.partition 
                JOIN bom b 
                  ON b.dataareaid = bv.dataareaid 
                     AND b.bomid = bv.bomid 
                     AND b.partition = bv.partition 
                JOIN inventtable ib 
                  ON ib.dataareaid = b.dataareaid 
                     AND ib.itemid = b.itemid 
                     AND ib.partition = b.partition 
                JOIN expbom eb 
                  ON eb.dataareaid = b.dataareaid 
                     AND eb.product = b.itemid 
                JOIN inventdim dimProduct 
                  ON dimProduct.inventdimid = bv.inventdimid 
                     AND dimProduct.dataareaid = bv.dataareaid 
                     AND dimProduct.partition = bv.partition 
                JOIN inventdim dimMaterial 
                  ON dimMaterial.inventdimid = eb.materialinventdimid 
                     AND dimMaterial.dataareaid = eb.dataareaid 
                 JOIN dbo.ecoresproduct erpProduct 
					 ON ibv.product = erpProduct.recid 
					 and erpProduct.partition = bv.partition
				JOIN dbo.ecoresproducttranslation erptProduct 
					 ON erptProduct.product = erpProduct.recid 
					 and erptProduct.partition = bv.partition
					 AND erptProduct.languageid = 'en-us' 
                JOIN dbo.ecoresproduct erpMaterial 
					 ON ib.product = erpMaterial.recid 
					 and erpMaterial.partition = bv.partition
				JOIN dbo.ecoresproducttranslation erptMaterial
					 ON erptMaterial.product = erpMaterial.recid 
					 and erptMaterial.partition = bv.partition
					 AND erptMaterial.languageid = 'en-us'
         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() ) 
                AND eb.bomdepth + 1 > 98 
                AND eb.bomdepth + 1 < 100) 
SELECT * 
FROM   expbom 
GO