BOM creation using Oracle SQL

I have two tables
The first one is tblItemDB has two columns (ItemID, ComponentID) The second one is tblItemInfo has three columns (ItemID, MerchanNumber,

Type)

All items listed in first table contains of various number of components.
For example we have two produced goods "A" and "M"

Good "A" has "B", "C" and "D" components

Good "M" has "C", "E" components

Component B consists of "C" and "F" and is also produced in our plant

Component C consists of "E" , "H" and "F" and is also produced in our plant

"D" "E" "H" and "F" are purchased. It's mentioned in tblItemInfo field Type='p'
if item is manufactured (produced in our plant) Type='m'

items having field MerchanNumber = ' 01' are finished goods other goods

have MerchanNumber = '05'

I need to have query which will show me what goods i need to purchase to produce all my finished products, without components produced in our

plant

can anyone suggest solution

Thanx Received on Tue Feb 07 2006 - 13:59:11 CST