BOM Explosion

Hi,

Can somebody help me on how to explode BOM.

I want to get all the level of item in InvenTable.

Thanks.

Louie,

Did you get a solution for this?

Regards,

Isaac.

Call the sales line explosion function?

i get all the bom by creating my own loop using diff method that i created but i deleted the xpo of that project.

AdamRoue,

Just a quick one;

1: How can I call the function?

2: I have a requirement of developing a report using a data provide class that can drill down BOMs to the lowest level. Can you recommendation be used to come up with the business logic?

Kind regards,

Isaac.

Louie,

I need to come up with a business logic regarding that. I know this can be solved by either using loops or a recursive function. Are you able to give me an idea of how to go about this? I have come up with something but it seems to be having an endless loop. Check http://dynamicsuser.net/forums/p/57980/311019.aspx#311019.

I will appreciate your input.

Best regards,

Isaac.

Isaac,

I use temporary table to get all the bom levels and you must know what is the end point of the level of an item so it will not be going to an infinite loop.

Regards,

Louie

Hi,

I need to get all BOM explosion for the sales order that means explosion for every salesline in the sales order. Is there any such report/inquiry?

What if I want to do it in SSRS?

Any ideas?

thanks,

Abid

Hi Abid

I have just recently finished an SSRS report that uses BOM explosion. You may try out the code below; You may want to create a stored procedure and just supply the item id and dataareaid from the parameters.

WITH BOMCTE (ORIGBOMID,ORIGITEMID,BOMID,BOMNAME, BOMITEMID,BOMITEMNAME,ITEMID,QTY,LEVEL) AS

(

SELECT BV.BOMID,bv.ITEMID,bv.BOMID,BV.NAME,bv.ITEMID,IT.ITEMNAME,bm.ITEMID,BM.BOMQTY, 0 AS LEVEL FROM BOM BM

INNER JOIN BOMVERSION BV ON BM.BOMID = BV.BOMID AND BM.DATAAREAID = BV.DATAAREAID

INNER JOIN INVENTTABLE IT ON BV.ITEMID = IT.ITEMID AND BV.DATAAREAID = IT.DATAAREAID

WHERE BV.ITEMID = @ITEMID AND BM.DATAAREAID = @DATAAREAID

AND BV.ACTIVE =1 AND IT.ITEMTYPE IN (0,1)

AND (BV.FROMDATE <= GETDATE() AND BV.TODATE >= GETDATE()

UNION ALL

SELECT BC.BOMID,BC.ORIGITEMID,bv.BOMID,BV.NAME,bv.ITEMID,IT.ITEMNAME,bm.ITEMID,BM.BOMQTY, LEVEL + 1 FROM BOM BM

INNER JOIN BOMVERSION BV ON BM.BOMID = BV.BOMID AND BM.DATAAREAID = BV.DATAAREAID

INNER JOIN INVENTTABLE IT ON BV.ITEMID = IT.ITEMID AND BV.DATAAREAID = IT.DATAAREAID

INNER JOIN BOMCTE bc on bv.ITEMID= bc.ITEMID

WHERE BM.DATAAREAID = @DATAAREAID AND bv.ACTIVE = 1 AND IT.ITEMTYPE IN (0,1)

AND (BV.FROMDATE <= GETDATE() AND BV.TODATE >= GETDATE())

AND IT.STOPEXPLODE = 0

)

SELECT BC.ORIGBOMID,

BC.ORIGITEMID,

BC.BOMID,

BC.BOMITEMID,

UPPER(BC.BOMITEMNAME)AS BOMITEMNAME,

BC.ITEMID,

BC.QTY,BC.BOMNAME,

BC.LEVEL

FROM BOMCTE BC

Cheers

Mike

Thanks Mike!