cancel
Showing results for 
Search instead for 
Did you mean: 

Query To Display Components Not In A BOM Or Production Order

RahF
Participant
0 Kudos
560

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member212181
Active Contributor
0 Kudos

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
Participant
0 Kudos

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
Active Contributor
0 Kudos

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
Participant
0 Kudos

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
Active Contributor
0 Kudos

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
Participant
0 Kudos

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

Answers (0)