Skip to Content
0

WHERE USED for all items in SAP B1

Nov 22, 2016 at 08:12 AM

157

avatar image

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

10 |10000 characters needed characters left characters exceeded

Anyone that can help here ?

Thanks

0
* Please Login or Register to Answer, Follow or Comment.

7 Answers

Best Answer
Gonzalo Gomez Dec 09, 2016 at 08:10 AM
1

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]

Share
10 |10000 characters needed characters left characters exceeded
DIEGO LOTHER Dec 09, 2016 at 01:46 AM
1

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


img1.png (44.7 kB)
Share
10 |10000 characters needed characters left characters exceeded
Enrico Lionello Dec 12, 2016 at 11:38 AM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Johan Hakkesteegt Nov 22, 2016 at 10:00 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Enrico Lionello Nov 22, 2016 at 12:53 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Enrico Lionello Dec 08, 2016 at 04:20 PM
0

Anyone that can help here ?

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Dec 09, 2016 at 08:34 AM
0
Share
10 |10000 characters needed characters left characters exceeded