Skip to Content
avatar image
Former Member

Query for orphaned child items - SAP Business One

Jan 22, 2018 at 03:17 PM | 104 Views

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

SAP Business One
SQL | bom
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • avatar image
    Jimmy Liang
    Jan 25, 2018 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?

    Regards,

    Jimmy

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 26, 2018 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

    John

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member
      Feb 05, 2018 at 04:43 PM

      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