Skip to Content

Query for orphaned child items - SAP Business One

Jan 22 at 03:17 PM


avatar image


I am struggling to construct a query in SAP B1 to identify child items NOT in a BOM.
We have been having a major clear up of old Finished pack codes and deleting old BOMS. I suspect many orphaned components are left.
I would like to have an option to select by "Sales Item Group" if possible.

Thanking in advance if there is anyone who could help.


SQL | bom
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Jimmy Liang Jan 25 at 09:10 AM

Hi John,

Have you tried to look at the OITT table which basically is the table for BOMs. From OITT, you should be able to SELECT DISTINCT and get a list of child item used in BOMs.

Then back to the item master, may I ask how you flag an item as a child item? Is there a UDF on item master so that you can have a list of all child items?



10 |10000 characters needed characters left characters exceeded
John Clarke Jan 26 at 02:56 PM

Hi Jimmy

If I understand your question correctly, In the Item master, all child items are set using the "Sales Item" field. The child items are either listed in the sales item field as "Packaging items" or "Raw items"

The father items are listed as "Manufactured items"

Hope this answers your question

Thanks very much


Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi John,

The system I work with may look different from yours. In my Item Master window, there are three check-boxes used for Inventory Item, Sales Item, and Purchasing Item.

I am guessing those check-boxes are the ones you were referring to. Assuming in your case the "Sales Item" check-box will be checked to flag the item is a child item, you may try this query:


WHERE validfor IN ('Y') AND sellitem IN ('Y') -- This is to select Active items from item master with the "Sales Item" check-boxed checked.


SELECT DISTINCT code FROM ITT1 -- This is to select all child item from the BOMs

And this should give you a list of child items not used in BOMs. You may have to run this in SQL Server as the built-in Query Generator doesn't seem to allow the EXCEPT statement.

Hope it helps.



Hi Jimmy
Sorry for not responding sooner.

What I ended up doing was to run the following query -

SELECT distinct T0.[Code], T2.[Code] FROM OITT T0INNER JOIN OITM T1 ON T0.[Code] = T1.[ItemCode] INNER JOIN ITT1 T2 ON T0.[Code] = T2.[Father]

WHERE T1.[ItmsGrpCod] =105


T1.[validFor] ='Y'

I was refering to the ItmsGrp field, in our system, the specific value being 105.

I then delta'd the unique child items from the full item list. Anything that did not match I take as orphaned components.

Thanks again for your input