BOM Report without drill down

[Former Member]

Hello,

I am trying to create a BOM report where the user inputs the father item and all the other child items are displayed below it, Including the childs of other father items inside the BOM.

Has anyone done this before ?

Some BOMs items are made of other BOMs.

Kind Regards,

[MD1]

Dear,

Do you want this kind of report

[former_member12741]

WITH DirectReports (code, Father, grandfather,pqty,cqty) AS ( select t0.code, t0.code as Father, t0.code as grandfather,t0.Qauntity [pqty],t1.Quantity [cqty] from OITT t0 LEFT join ITT1 t1 on t0.Code=t1.Code where t0.Code in ('[%0]') UNION ALL -- Recursive member definition select t0.code, t0.Father, d.Father as grandfather,t0.Quantity,t0.Quantity from ITT1 t0 INNER join DirectReports as d on d.code =t0.Father ) -- Statement that executes the CTE SELECT d.Father [Parent Item],i1.ItemName [Parent Name],d.pqty [Planned Quantity],d.code [Child Items],i2.ItemName [Child Name], d.cqty [Child Qty] FROM DirectReports d , oitm i1 , oitm i2 where d.father = i1.itemcode and d.code = i2.itemcode

[former_member196647]

Hello Martin,

You can create such a report by using recursive query. But such a report is already available in B1, wouldn't that suffice.

Rahul

[Former Member]

Hello,

The problem with the production report in business one is that it does not show the batch numbers. I need one that can display the items with the batch numbers.

I achieved this with the code below. However obtaining the batch number per item seems to be a bit complicated. this is because we are using BOMs within BOMs in production going to depths of 2 to 8

The CTE BOM functionality for getting the tree is as follows : -

WITH BOM (Father,Code,Quantity) AS
(

SELECT     ROOT.Father, ROOT.Code, ROOT.Quantity
FROM         dbo.ITT1 AS ROOT
WHERE     ROOT.Father =  @itemcode
UNION ALL
SELECT     CHILD.Father, CHILD.Code, CHILD.Quantity
FROM         dbo.ITT1 AS CHILD
JOIN BOM ON CHILD.Father = BOM.Code

)

SELECT   Father,(SELECT ItemName FROM OITM WHERE OITM.ItemCode = Father) AS 'Father Name',Code,OITM.ItemName AS 'Child Name',Quantity 
,OITM.TreeType 
FROM BOM INNER JOIN OITM  ON BOM.Code = OITM.ItemCode
ORDER BY Fathe

I am trying to create a sub report for getting the batch numbers recursively. Anyone with expirience on Batch numbers in production BOM report ?

Edited by: Martin Kamau on Oct 27, 2009 3:16 PM

[Former Member]

Hello,

try the following stored procedure,

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure BoomTree(@itemcode nvarchar(20))

AS

BEGIN

WITH BOM (Father,Code,Quantity) AS
(
SELECT     ROOT.Father, ROOT.Code, ROOT.Quantity
FROM         dbo.ITT1 AS ROOT
WHERE     ROOT.Father =  @itemcode
UNION ALL
SELECT     CHILD.Father, CHILD.Code, CHILD.Quantity
FROM         dbo.ITT1 AS CHILD
JOIN BOM ON CHILD.Father = BOM.Code
)
 SELECT   Father,Code,Quantity FROM BOM 

OPTION (MAXRECURSION 2);
RETURN
END

GO

Regards,

J.

[Former Member]

Thanks, but where do I specify the batch numbers. How do I attach the IBT1 tables with the batch number information with the BOM ?

[Former Member]

Hello,

YOu can connect the Batches only to the work order / production order not ot the BoM.

You can join the IBT1 table by Production Orders

select ItemCode, BatchNum, BsDocEntry, BsDocLine from ibt1 where BsDocType in ('202')

Otherwise you have wtitten a report on Bill Of Materials. This was your original scope? Not the produced Quantity and Batch numbers?

The Query, (Procedure) is requirsive, but the Used Quantity is not requirsice inside !

Regards,

J.

[Former Member]

Thanks Janos,

The reason I was writing this query was that I want to be able to trace all the child elements from a particular item. For instance for production item P0001, I can get all the child items and their batch numbers used in its production as well.

Hence I first created the BOM report with the view of using a subreport to get the batch numbers. Which hasn't been going very well. Is there another way around this ?

Kind Regards,

Edited by: Martin Kamau on Oct 28, 2009 8:29 AM

[Former Member]

Hi,

Let's think together,

1. We have the list of the WH journal in the OINM table, and we can filter it as production transactions....

2. We have the bacth transaction details in IBT1 table, and we can link this table to the OINM....

select T0.ItemCode, T0.InQty, T0.OutQty , T1.BatchNum
from oinm T0 inner join ibt1 T1 on T0.ItemCode =  T1.ItemCode and T0.Warehouse = T1.WhsCode and T0.TransType = T1.BaseType and T0.CreatedBy = T1.BaseEntry and T0.DocLineNum = T1.BaseLinNum
where T0.applobj = '202' 

3. We have a reqursive query which gives as the list of the subcomponents...

So combine them

Let's modify the prevoius stored procedure.....

Alter Procedure BoomTree(@itemcode nvarchar(20))
 
AS
 
BEGIN
 
WITH BOM (Code) AS
(
SELECT     ROOT.Code as Code
FROM         dbo.OITT AS ROOT
WHERE     ROOT.Code =  @itemcode
UNION ALL
SELECT     CHILD.Code
FROM         dbo.ITT1 AS CHILD
JOIN BOM ON CHILD.Father = BOM.Code
)
 
select T0.ItemCode, T0.InQty, T0.OutQty
, T1.BatchNum
from oinm T0 
inner join ibt1 T1 on T0.ItemCode =  T1.ItemCode and T0.Warehouse = T1.WhsCode and T0.TransType = T1.BaseType and T0.CreatedBy = T1.BaseEntry and T0.DocLineNum = T1.BaseLinNum
where T0.applobj = '202' 
and T0.ItemCode in (select code from BOM)
 
OPTION (MAXRECURSION 10); -- Handles 10 levels dept of BOM
RETURN
END
 
GO

exec boomtree 'P0001'

So it was easy.....

Regards,

J.

[Former Member]

Ok,

The query runs fine, thanks. But there is one little thing though. This query returns all the batch numbers associated with the item code for all the production runs, but given that I only require batches associated with a

certain item batch no. means some tweaking will be required on this. What I mean is the user enters the itemcode and batchno. and all the child items specific to this particular

item batch number are displayed.

select T0.ItemCode, T0.InQty, T0.OutQty , T1.BatchNum
from oinm T0 inner join ibt1 T1 on T0.ItemCode =  T1.ItemCode and T0.Warehouse = T1.WhsCode and T0.TransType = T1.BaseType and T0.CreatedBy = T1.BaseEntry and T0.DocLineNum = T1.BaseLinNum
where T0.applobj = '202'

Thanks for your help, lets see if we can get something about this.

Edited by: Martin Kamau on Oct 30, 2009 9:09 AM

[Former Member]

Hello,

this query gives only the bacthes which are connected to production (appobj = '202') means production.....

You can filter the result by batch number......

Regards,

J.

[Former Member]

The scenario is this way, if the user enters the item code --X001 and batch number -- BT001 for product X, the query returns the results in this way -

1. All the child items and batches that were used to create the product X batch BT001. Considering that product X has child items that also have children, then that would mean that the query gets those parent items and the children and so on that were involved in producing Product X Batch BT001 upto the last level.

Hope this helps.

[Former Member]

Hi Janos,

Given the evolution of this thread to our current stage, I would like to create a new message beginning from the where we have left off.

Am still looking at what we have at the moment and will add progress information.

Kind Regards,

[Former Member]

Did you get a resolution on this ?