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).
Christophe
January 2014
