Query for orphaned child items - SAP Business One

[Former Member]

Hi

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.

John

[Former Member]

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

John

[jimmyl]

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:

SELECT DISTINCT itemcode FROM OITM

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

EXCEPT

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.

Jimmy

[Former Member]

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

AND

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

John

[jimmyl]

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?

Regards,

Jimmy