Dear all,

i’m searching for a solution for my problem.

I would like to have a bill of material for all my product I sold in the last two years as after sales projcets to create a forecast for the next year.

What I already did or tried.

Wrote a query for all customer oders from the last two years with the table tdsls041.
Wrote a query of all customer speficic bill of materials for after sales projects with table tipcs022.
This two steps are not the big problem. Combining them in Excel via PowerQuery helps to get the wished overwiev of used material.

Problematic for me now ist to get the information of standard arcticles with table tibom010.

Select

Tibom010.mitm,

tibom010.sitm,

tibom010.qana

from

tibom010



shows me all standard articles beginning from the year 1998

For some standard articles we changed the art of article from manufaturing to purchaising items and from purchaising to manufacturing items.

For me just the manufacturing items are from interest. And here is the problem: I would like to link the table tibom010 with tiitm001. My idea was the following query:

Select

Tibom010.mitm,

tibom010.sitm,

tibom010.qana,

tiitm001.item,

tiitm001.kitm

from

tibom010,

tiitm001

where

tiitm001.kitm = tckim.manufacture and

tibom010.mitm = tiitem001.item

But now I just get the standard article at the top level but no bill of material.

Therefore for example I need the field tibom010.mitm twice in the query that it could look like this:

Select

Tibom010.mitm,

tibom010.sitm,

tibom010.qana,

tiitm001.item,

tiitm001.kitm

tibom010.mitm2

from

tibom010,

tiitm001

where

tiitm001.kitm = tckim.manufacture and

tibom010.mitm2 = tiitem001.item

I hope you can understand my problem a could give me a hint. Or do you think it would be easier to do this with a table from wahrehouse like tdinv700.

Thanks in advance.

When used a completely different company to forecast future needs. That way we could see everything all at one - what was on hand excess, what sub assemblies being built. That might be way more than you want to do.

Now my question is - are you doing this with a session or easy sql? If easy sql get you what you are looking for - then I would do this:

Select
Tibom010.mitm,
tiitm001.kitm,
tibom010.sitm,
tibom010.qana
from tibom010, tiitm001
where tibom010.mitm = tiitm001.item

This would get you a file with all make items and the kind of item that make item is. You could filter the information out in excel - select kitm not manaufactured and remove all the records. Or you can go to the easy sql report and only print records where the tiitm001.kitm = tckim.manufacture.

Now if doing a session I would just separate the queries - find on the item master all the make items as outer query. Then find all the recods on the bom table for each make item as the inner query. Then send each record to the report.

Hello,
have a look at this thread

http://www.baanboard.com/baanboard/showthread.php?t=71628&highlight=Bill+Material

You have to drill down the BOM levels.

Regards

Maybe they have simple boms. :) I can't imagine not having indented boms, but maybe in this case it is not needed.