Hi,
Can somebody help me on how to explode BOM.
I want to get all the level of item in InvenTable.
Thanks.
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!