Skip to Content
Author's profile photo CHRISTOPHE AVEROUS

Recursive query for BOM exploration

Today for my first post, I will explain how we deal with BOM here in Durieu.

Everybody is aware about the BOM structure in SAP Business One:

OITT and IIT1.

One question that every people who deal with Bom can ask is :

How do I retrieve in one query all the information about a multi level BOM.

To answer to this question, you have to deal with recurssive query:

Here is the query to retrieve the BOM of an item:


WITH [BOM_cte]([Father],[ratio],[QtyTop], [Code], [Quantity],[Price],[ItmsgrpCod],[TreeType],[PriceList],[RecursionLevel]) — CTE name and columns

    AS (

        SELECT b.[Father],convert(numeric(19,6),1),a.qauntity, b.[Code],b.[Quantity],isnull(d.Price,0),c.ItmsGrpCod,c.Treetype,d.pricelist,0 — Get the initial list of components for the bike assembly

        FROM oitt a inner join itt1 b on a.code=b.father inner join oitm c on b.[code]=c.itemcode inner join itm1 d

        on d.Itemcode=c.itemcode and d.pricelist=b.pricelist

        WHERE b.[Father] = @Itemcode and a.treetype<>’T’


        UNION ALL

        SELECT b.[Father],convert(numeric(19,6),cte.quantity*cte.ratio/cte.qtytop),a.qauntity , b.[Code],b.[Quantity] ,isnull(d.Price,0),c.ItmsGrpCod,c.Treetype,d.pricelist,[RecursionLevel] + 1 — Join recursive member to anchor

        FROM [BOM_cte] cte

            INNER JOIN itt1 b inner join oitm c on b.[code]=c.itemcode inner join oitt a on a.code=b.[father] inner join itm1 d on b.code=d.Itemcode and d.pricelist=b.pricelist

            ON b.[father] = cte.[code] where  a.treetype<>’T’


SELECT b.[Father], b.[Code],b.ratio,b.qtytop, b.[quantity] AS [Quantity_itt1] ,(b.[quantity]*b.ratio)/b.qtytop as ‘Quantity’,b.price as ‘Price_Itt1’,((b.[quantity]*b.ratio*b.price)/b.qtytop)*1000000 as ‘Price’,b.ItmsgrpCod,b.Treetype,b.pricelist,[RecursionLevel]

    FROM [BOM_cte] b inner join itm1 t0 on t0.itemcode=b.code and T0.Pricelist=4


To avoid leak on price data, I multiply by 1000000 the price (don’t forget to divise by the same value when calculate price).


January 2014

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Fabio Bilicki
      Fabio Bilicki

      Perfect, thank you, this query helped me a lot, a long time researching how to multiply the quantities.

      Best regards,