on 02-17-2009 12:00 PM
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,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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.
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
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.
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
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.
User | Count |
---|---|
91 | |
11 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.