Query To Display Components Not In A BOM Or Production Order

[RahF]

Hello All

I am trying to write a query that will display any components that isn't in any BOM's or Open production Orders

The query should only display Item codes that belong to the ItemGroupCode = 287 and should display item codes which are on hand

I have written the below, but doesn't work

Hoping someone can point me or guide me in the right direction

SELECT T0.[ItemCode],

T0.[ItemName],

T0.[OnHand],

(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock'

FROM OITM T0  INNER JOIN ITT1 T1 ON T0.ItemCode = T1.Code INNER JOIN WOR1 T2 ON T0.ItemCode = T2.ItemCode

WHERE T0.[ItemCode] not in T1.[Code] and  T2.[ItemCode] and T0.[ItmsGrpCod] = 287 and  T0.[OnHand] >= 1

ORDER BY T0.[ItemName]

Thanks and Regards

Rahul

[former_member212181 · Accepted solution]

Dear Rahul,

Please try below query

--------------

;WITH BOM_PDN AS

(Select A.Code [Item_Code]

from ITT1 A

Group By A.Code

Union

Select B.ItemCode [Item_Code]

from WOR1 B

Inner Join OWOR C on B.DocEntry = C.DocEntry

Where C.Status in ('P','R')

Group By B.ItemCode

)

SELECT T0.[ItemCode],

T0.[ItemName],

T0.[OnHand],

(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock'

FROM OITM T0

WHERE T0.[ItemCode] not in (Select T1.Item_Code from BOM_PDN T1)

and T0.[ItmsGrpCod] = 287

and  T0.[OnHand] >= 1

ORDER BY T0.[ItemName]

----------------

Thanks

Unnikrishnan

[RahF · Accepted solution]

Hi Unnikrishnan

I tried both and the first one displays all components that are in BOMs and production orders

The second one gives and error message

I need the query to only display the item codes that have stock and are not in any BOM's and production orders

Thanks and Regards

Rahul

[former_member212181 · Accepted solution]

Hi Rahul,

that is not two separate queries.

You have to run together.

First part is Common table expression (CTE) creating a temporary table and store some values in that table.

Second part is another query, which we are taking taking data from both SAP tables and Temp table.

Please run it together and reply

All the best

Unnikrishnan

[RahF · Accepted solution]

Hi Unnikrishnan

Sorry my bad

Yes it works, and the query result made me realise  forgot one more criteria

Would it be possible to exclude any Item Codes that have the letters PW in it?

Thanks and Regards

Rahul.

[former_member212181 · Accepted solution]

Hi Rahul,

Please try this

----------

;WITH BOM_PDN AS

(Select A.Code [Item_Code]

from ITT1 A

Group By A.Code

Union

Select B.ItemCode [Item_Code]

from WOR1 B

Inner Join OWOR C on B.DocEntry = C.DocEntry

Where C.Status in ('P','R')

Group By B.ItemCode

)

SELECT T0.[ItemCode],

T0.[ItemName],

T0.[OnHand],

(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock'

FROM OITM T0

WHERE T0.[ItemCode] not in (Select T1.Item_Code from BOM_PDN T1)

and T0.[ItmsGrpCod] = 287

and  T0.[OnHand] >= 1

and T0.ItemCode Not like '%PW%'

ORDER BY T0.[ItemName]

------

Thanks

Unnikrishnan

[RahF · Accepted solution]

Hi Unnikrishnan

Thanks for that

It works

I have like 600 code I need to check & make sure why they are not in any BOM's & production orders

Thanks and Regards

Rahul