Re:Query for items having Stock for a particular warehouse ..!!!

[nvs_revathy]

Dear SAP Members,

I need a query to display a list of items having stock by considering all the marketing documents,goods receipt,BOM,Production Order,etc.

I have written query like this:

SELECT T0.[ItemCode], T0.[ItemName], T1.[ItmsGrpNam],T0.[OnHand], 
T0.[IsCommited], T0.[OnOrder] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod 
WHERE T1.[ItmsGrpNam] ='[%0]' or  T0.[DfltWH] ='[%1]'

The issue is

The InStock shows the total by calculating all warehouses stock but i need the total for the default warehouse.

Plz suggest or modify my query if i have missed out any fields.

With Regards,

Revathy

[nvs_revathy]

Dear Gordon,BalaKumar and Neetu,

Thanks.Your Query is working fine but i need some modifications in that query.(i.e)For a parent item it shows the committed value but for a child item only if we raise the production order then only it is showing value in the committed column.Whether it is possible to create a query by considering all the child items also so that it will be better for production planning..

With Regards,

Revathy

[Former Member]

How many levels do you have for your BOM?

[nvs_revathy]

Dear Gordon,

We have BOM levels like this:

Parent Item->Component Item-> Subcomponent Item-subcomponent Item..

With Regards,

Revathy

[Former Member]

Dear Revathy,

Try this:

SELECT T0.[ItemCode], T0.[ItemName], T1.[ItmsGrpNam],T2.[OnHand], 
T2.[IsCommited], T2.[OnOrder] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod 
inner join OITW T2 on T2.ItemCode =T0.ItemCode and T0.[DfltWH] = T2.WhsCode
WHERE T1.[ItmsGrpNam] ='[%0]' and T2.WhsCode = [%1]

Thanks,

Gordon

[former_member206488]

Try this:

SELECT T0.[ItemCode], T0.[ItemName], T1.[ItmsGrpNam],T2.[OnHand], 
T0.[IsCommited], T0.[OnOrder] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod 
inner join OITW T2 on T2.ItemCode =T0.ItemCode  
WHERE T1.[ItmsGrpNam] ='[%0]' and T2.WhsCode = [%1] 

[kvbalakumar]

Hi Revathy,

Try this

SELECT T0.[ItemCode], T0.[ItemName], T1.[ItmsGrpNam],T2.[OnHand], 
T0.[IsCommited], T0.[OnOrder] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod 
inner join OITW T2 on T2.ItemCode =T0.ItemCode  
WHERE T1.[ItmsGrpNam] ='[%0]' and T0.[DfltWH] = T2.WhsCode

Or

SELECT T0.[ItemCode], T0.[ItemName], T1.[ItmsGrpNam],T2.[OnHand], 
T0.[IsCommited], T0.[OnOrder] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod 
inner join OITW T2 on T2.ItemCode =T0.ItemCode  
WHERE T1.[ItmsGrpNam] ='[%0]' and T2.WhsCode ='[%1]'

Regards,

Bala