November 3, 2009 at 4:20 am
Hi Martin,
apologies, Im having a hard time exactly where you issue lies.
Please remember this data means nothing to me.
If i execute this...
DECLARE @itemcode VARCHAR (20)
DECLARE @prodnum VARCHAR (20)
SET @itemcode = 'P10004';
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 t2.itemname,T0.ItemCode, T0.InQty,
T1.BatchNum,T1.BaseEntry,T0.applobj,T1.BaseNum
from BOM join
oinm T0
on T0.ItemCode =BOM.code
inner join ibt1 T1 on T0.ItemCode = T1.ItemCode inner join oitm t2 on t1.itemcode = t2.itemcode
and T0.Warehouse = T1.WhsCode and T0.TransType = T1.BaseType
and T0.CreatedBy = T1.BaseEntry and T0.DocLineNum = T1.BaseLinNum
That gives me the full BOM heirarchy for the ItemCode in question.
Now you have extra filters, such as
T0.applobj = '202' and T1.BaseNum = '15'
I dont know the significance behind why you are filtering on that.
or indeed the next query you want is
T0.applobj = '202' and T1.BaseNum = @prodnum and t2.treetype = 'P'
As i dont know what this all means in the real world , its very hard to advise further.
Can you provide a simplified example ?
November 3, 2009 at 5:40 am
Ok,
Am sure you've heard of the recursive query or the BOM CTE.It works with hierachical data. It works this way :- You have a certain item say computer set, this computer is made up of a variety of items, at diffrent hierachies.
Computer set Made up of
1.Keyboard
2.Mouse
3.CPU
CPU made of
Inter processor
Graphics Card
Mother board
Keyboard and Mouse are single units.
Hence at a particular time, I would get a defective computer set. The identity of this computer set it batch number BT001.
Because of this defect I want to identify all the parts that made this particular computer set. They are identified by their batch numbers.
For the mouse and Keyboard this would be simple. However the CPU was produced from diffrent parts, and we would like to get the batch numbers of the CPU as well as its component parts or "child items". So, with one recursive query we would get the child items of the computer set which are :-
Keyboard
Mouse
CPU
But we need another recursion to get the child items of the CPU. This is the tricky part since for the first recursion the BOM cte works fine.
Each a computer set is produced there is a production number assinged to it.Same for the CPU.
The computer set and keyboard additionaly have itemcodes to identify them uniquely in the table.
The Query
When you execute this part below you get the full BOM hierachy of the computer set, but not of the CPU which we require as well: -
DECLARE @itemcode VARCHAR (20)
DECLARE @prodnum VARCHAR (20)
SET @itemcode = 'P10004';
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 t2.itemname,T0.ItemCode, T0.InQty,
T1.BatchNum,T1.BaseEntry,T0.applobj,T1.BaseNum
from BOM join
oinm T0
on T0.ItemCode =BOM.code
inner join ibt1 T1 on T0.ItemCode = T1.ItemCode inner join oitm t2 on t1.itemcode = t2.itemcode
and T0.Warehouse = T1.WhsCode and T0.TransType = T1.BaseType
and T0.CreatedBy = T1.BaseEntry and T0.DocLineNum = T1.BaseLinNum
The filtering
T0.applobj = '202' -- In the tables I provided this number simply stands for production. Every row with '202' is production, just standard stuff to identify rows in the tables that store production content.
T1.BaseNum = '15' -- This is the production number of the computer set. As I indicated earlier every time a computer set is made or produced a production number is assigned.
t2.treetype = 'P' -- Items such as the CPU and Computer set have treetype 'P' as seen in the OITM table. P means that its a parent item.
So with this query here below, I was trying to get the production number and item code of the CPU so as to send them to the CTE and get the child details.
select @prodnum =t0.AppObjAbs , @itemcode = t2.itemcode
from oinm T0
inner join ibt1 T1 on T0.ItemCode = T1.ItemCode inner join oitm t2 on t1.itemcode = t2.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 T1.BaseNum = @prodnum and t2.treetype = 'P'
With the normal BOM CTE without the joins of the additional tables I would get the full BOM hierachy for the computer set upto the child items of the CPU and any other level. Just from the itemcode itselft.
But more detail is required to be displayed like the batch numbers for each item which makes this a bit more complicated than it could have been without this information.
For the query that I provided change item code @itemcode = 'P000040'
to @itemcode = 'P000030' which is one of the child items. This will give the child items of the particular item.
Hope this helps. In case of any more questions just ask.
November 3, 2009 at 7:28 am
martin.edward (11/3/2009)
T1.BaseNum = '15' -- This is the production number of the computer set. As I indicated earlier every time a computer set is made or produced a production number is assigned.
This is where i start to get confused with your requirements.
As the CPU is returned with this
DECLARE @itemcode VARCHAR (20)
DECLARE @prodnum VARCHAR (20)
SET @itemcode = 'P10004';
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 t2.itemname,T0.ItemCode, T0.InQty,
T1.BatchNum,T1.BaseEntry,T0.applobj,T1.BaseNum
from BOM join
oinm T0
on T0.ItemCode =BOM.code
inner join ibt1 T1 on T0.ItemCode = T1.ItemCode inner join oitm t2 on t1.itemcode = t2.itemcode
and T0.Warehouse = T1.WhsCode and T0.TransType = T1.BaseType
and T0.CreatedBy = T1.BaseEntry and T0.DocLineNum = T1.BaseLinNum
But naturally , as soon as you add "where T1.BaseNum = '15'" it is filtered out.
November 4, 2009 at 12:20 am
Yes, It gives everything. Thanks for that. But if the CPU was produced 20 times or there were 20 production runs for the CPU then It would display all the production runs.
Hence I used the parameter 15 to get the details for production number 15 only.
I have an idea, If I were to group all the result by the production number without using the "T1.BaseNum = '15'" filter, I would get a result set grouped by production numbers however the problem would be finding out which of the diffrent production runs resulted in the computer set batch no. BA001
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply