Skip to Content
avatar image
Former Member

WHERE USED for all items in SAP B1

Dear Gurus good morning,

I have been looking trough and extensively googled the subject but can't find an answer.

Thanks in advance for your help.

I'm trying to run a report that show in one go all the children --> parents relationships.

I'm looking to show all BOMs that one particular item is used in.

I need all parents for one item.

I found a similar question here but it was left unasvered. https://archive.sap.com/discussions/thread/3590976

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Best Answer
    Dec 09, 2016 at 08:10 AM

    In the post you mentioned there is a query in the accepted answer, simply cut the final: WHERE T0.[Code] =[%0] and you get all items

    SELECT T0.[Code] As 'Father Item Code', T1.[Code] As 'Component Item Code', T2.[ItemName] As 'Component Item Name', T1.[Quantity] As 'Component Item Quantity', T1.[Warehouse] As 'Component Warehouse', T1.[Price] As 'Component Warehouse', T1.[IssueMthd] As 'Component Issue Method' FROM OITT T0 INNER JOIN ITT1 T1 ON T0.[Code] = T1.[Father] INNER JOIN OITM T2 ON T0.[Code] = T2.[ItemCode]

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 09, 2016 at 01:46 AM

    Hi Enrico,

    If I understood right your need, try this query. First you need do a recursive CTE to build your BOM structure, after that you should do a select on the CTE. The query is not beautiful but works fine :D

    WITH BOM
    AS
    (
    	-- Select anchor --
    SELECT 
    	CONCAT('/', 0, CAST(T1.Code AS NVARCHAR(MAX))) AS Path, 
    	0 AS Level, 
    	0 AS 'Component Position',
    	CAST('' AS NVARCHAR(20)) AS 'Father Item Code', 
    	T1.[Code] As 'Component Item Code', 
    	T2.[ItemName] As 'Component Item Name', 
    	T1.[Qauntity] As 'Component Item Quantity'
    FROM 
    	OITT T1
    	INNER JOIN OITM T2 ON T2.Itemcode = T1.Code
    UNION ALL
    -- Definição recursiva
    SELECT 
    	CONCAT(T1.Path, '/', T0.[ChildNum], CAST(T0.Code AS NVARCHAR(MAX))) AS Path, 
    	T1.Level + 1,  
    	T0.[ChildNum] AS 'Component Position',
    	T3.[Code] As 'Father Item Code', 
    	T0.[Code] As 'Component Item Code', 
    	T2.[ItemName] As 'Component Item Name', 
    	T0.[Quantity] As 'Component Item Quantity'
    FROM 
    	ITT1 T0
    	INNER JOIN BOM AS T1 ON T1.[Component Item Code] = T0.Father
    	INNER JOIN OITM T2 ON T2.Itemcode = T0.Code
    	INNER JOIN OITT T3 ON T3.Code = T0.Father
    )
    SELECT
    	DISTINCT
    	T1.Path,
    	CONCAT(REPLICATE(' ', T1.Level * 3), T1.[Component Item Code]) AS 'Tree',
    	--T1.[Father Item Code],
    	--T1.[Component Item Code],
    	T1.[Component Item Name],
    	T1.[Component Item Quantity]
    FROM 
    	BOM T0
    	INNER JOIN BOM T1 ON T1.Path like CONCAT('/', SUBSTRING(T0.Path, 2, CASE WHEN CHARINDEX('/', T0.Path, 2) = 0 THEN LEN(T0.Path) ELSE CHARINDEX('/', T0.Path, 2) - 2 END)) + '%'
    WHERE
    	T0.Path like '%your item id%'
    ORDER BY T1.Path
    

    If you don't want filter by any item, just remove the where clause on the last select.

    A image of a result in my database:

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 12, 2016 at 11:38 AM

    Thank you Gonzalo, this seem to deliver the result I expected. Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 22, 2016 at 10:00 AM

    Hi Enrico,

    In the post you mentioned there is a query in the accepted answer, that should give you what you need.

    If this query does not give you the expected result, could you please explain in more detail what result you would expect?

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 22, 2016 at 12:53 PM

    Thanks Johan for your reply.

    the proposed query in that answer would only give me all children of a parent item. Instead, I need all parents for ALL items.

    I hope this clarify.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 08, 2016 at 04:20 PM

    Anyone that can help here ?

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 09, 2016 at 08:34 AM
    Add comment
    10|10000 characters needed characters exceeded