cancel
Showing results for 
Search instead for 
Did you mean: 

Consumption Query

Former Member
0 Kudos

Hi All,

Can we create the query in the following format -

Selection Creteria -

a) Item Codes - From ___ To ___

b) Date - From ___ To ___

Output :-

Item Codes I Item Descriptions I Opening Stock I Receipts I Issue I Closing Stock I

-


I Qty. I Value Rs. I Qty. I Value Rs. I Qty. I Value Rs. I

Any Standard report available for the same?

Regards,

Abhishek

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi........

you can refer the stock posting list report for that.

Regards,

Former Member
0 Kudos

Hi Rahul,

I have gone through that but it only gives the value for Closing balance. I need value for each of the column like opening balance - Qty & value, Receipt - Qty & value, Issue - Qty & value and Closing Balance - Qty & value.

Regards,

Abhishek

Former Member
0 Kudos

Hi!

There is no standard report available but we can write a customized query.

Former Member
0 Kudos

Hi!

Please check this thread

Try to modify the query as per your requirement.

If, u want any help get back.

Former Member
0 Kudos

Hi Thanga Raj K,

I have gone thru the codes mentioned in the thread but the columns contains only quantity not value. I want qty as well as value for each column. Any related codes for the same?

Regards,

Abhishek

Former Member
0 Kudos

Hi!

For geting the values you just add new columns as

For example : To get Opening Value

(isnull((Select sum(isnull(transvalue,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select sum(isnull(transvalue,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0))

i have just replaced inqty and outqty with transvalue.

do the same rest all.

Former Member
0 Kudos

Hi Thanga Raj K,

The earlier codes of quantity is not showing any output -

Declare @FromDate Datetime Declare @ToDate Datetime Declare @ItmsGrpNam nvarchar(50) Declare @ItmsGrpNam1 nvarchar(50) Declare @Whscode nvarchar(8) Declare @Whscode1 nvarchar(8) Declare @Location nvarchar(50) Declare @Location1 nvarchar(50) Declare @itemcode nvarchar(50) Declare @itemcode1 nvarchar(50) set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]') set @ToDate = (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]') set @ItmsGrpNam = (select max(S2.ItmsGrpNam) from OITB S2 where S2.ItmsGrpNam ='[%2]') set @ItmsGrpNam1 = (select max(S22.ItmsGrpNam) from OITB S22 where S22.ItmsGrpNam ='[%3]') set @Whscode = (Select max(s3.Whscode) from OWHS s3 where s3.whscode = '[%4]') set @Whscode1 = (Select max(s33.Whscode) from OWHS s33 where s33.whscode = '[%5]') set @Location = (select max(s4.Location) from OLCT s4 where s4.Location ='[%6]') set @Location1 = (select max(s44.Location) from OLCT s44 where s44.Location ='[%7]') set @itemcode = (Select max(S5.ItemCode) from OINM S5 Where S5.ItemCode ='[%8]') set @itemcode1 = (Select max(S55.ItemCode) from OINM S55 Where S55.ItemCode ='[%9]') select * from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)) as [OB Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (13,15)),0) as [sale Quatity], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Closing Stock] FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode INNER JOIN OLCT C1 ON W1.Location=C1.Code where (B1.ItmsGrpNam>=@ItmsGrpNam and B1.ItmsGrpNam<=@ItmsGrpNam1) and (W1.Whscode>=@Whscode and W1.Whscode<=@Whscode1) and (C1.Location>=@Location and C1.Location<=@Location1) and T0.itemcode >=@itemcode and T0.itemcode <=@itemcode1 Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0

Please check.

Regards

Former Member
0 Kudos

Hi Thanga Raj K,

The earlier codes of quantity is not showing any output -

Declare @FromDate Datetime Declare @ToDate Datetime Declare @ItmsGrpNam nvarchar(50) Declare @ItmsGrpNam1 nvarchar(50) Declare @Whscode nvarchar(8) Declare @Whscode1 nvarchar(8) Declare @Location nvarchar(50) Declare @Location1 nvarchar(50) Declare @itemcode nvarchar(50) Declare @itemcode1 nvarchar(50) set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]') set @ToDate = (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]') set @ItmsGrpNam = (select max(S2.ItmsGrpNam) from OITB S2 where S2.ItmsGrpNam ='[%2]') set @ItmsGrpNam1 = (select max(S22.ItmsGrpNam) from OITB S22 where S22.ItmsGrpNam ='[%3]') set @Whscode = (Select max(s3.Whscode) from OWHS s3 where s3.whscode = '[%4]') set @Whscode1 = (Select max(s33.Whscode) from OWHS s33 where s33.whscode = '[%5]') set @Location = (select max(s4.Location) from OLCT s4 where s4.Location ='[%6]') set @Location1 = (select max(s44.Location) from OLCT s44 where s44.Location ='[%7]') set @itemcode = (Select max(S5.ItemCode) from OINM S5 Where S5.ItemCode ='[%8]') set @itemcode1 = (Select max(S55.ItemCode) from OINM S55 Where S55.ItemCode ='[%9]') select * from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)) as [OB Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (13,15)),0) as [sale Quatity], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Closing Stock] FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode INNER JOIN OLCT C1 ON W1.Location=C1.Code where (B1.ItmsGrpNam>=@ItmsGrpNam and B1.ItmsGrpNam<=@ItmsGrpNam1) and (W1.Whscode>=@Whscode and W1.Whscode<=@Whscode1) and (C1.Location>=@Location and C1.Location<=@Location1) and T0.itemcode >=@itemcode and T0.itemcode <=@itemcode1 Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0

Please check.

Regards

Former Member
0 Kudos

Hi Thang Raj K,

Any Inputs?

Regards

Former Member
0 Kudos

Hi!

Try this, i have adde Value fields for Opening,OB, and Purchase Check this..

Declare @FromDate Datetime 
Declare @ToDate Datetime 
Declare @ItmsGrpNam nvarchar(50) 
Declare @ItmsGrpNam1 nvarchar(50) 
Declare @Whscode nvarchar(8) 
Declare @Whscode1 nvarchar(8) 
Declare @Location nvarchar(50) 
Declare @Location1 nvarchar(50) 
Declare @itemcode nvarchar(50) 
Declare @itemcode1 nvarchar(50) 
set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]') 
set @ToDate = (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]') 
set @ItmsGrpNam = (select max(S2.ItmsGrpNam) from OITB S2 where S2.ItmsGrpNam ='[%2]') 
set @ItmsGrpNam1 = (select max(S22.ItmsGrpNam) from OITB S22 where S22.ItmsGrpNam ='[%3]') 
set @Whscode = (Select max(s3.Whscode) from OWHS s3 where s3.whscode = '[%4]') 
set @Whscode1 = (Select max(s33.Whscode) from OWHS s33 where s33.whscode = '[%5]') 
set @Location = (select max(s4.Location) from OLCT s4 where s4.Location ='[%6]') 
set @Location1 = (select max(s44.Location) from OLCT s44 where s44.Location ='[%7]') 
set @itemcode = (Select max(S5.ItemCode) from OINM S5 Where S5.ItemCode ='[%8]') 
set @itemcode1 = (Select max(S55.ItemCode) from OINM S55 Where S55.ItemCode ='[%9]') 
select * from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, 
(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], 

(isnull((Select sum(isnull(Transvalue,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,67,-2,21,19,60,15,67,-2,13)),0))as [Opening Value], 

(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)) as [OB Quantity], 

(isnull((Select sum(isnull(Transvalue,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)) as [OB Value],

isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 
and O1.transtype in (20,18)),0) as [Purchase Quantity], 

isnull((Select sum(isnull(Transvalue,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 
and O1.transtype in (20,18)),0) as [Purchase Value], 


isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (13,15)),0) as [sale Quatity], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Closing Stock] FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode INNER JOIN OLCT C1 ON W1.Location=C1.Code where (B1.ItmsGrpNam>=@ItmsGrpNam and B1.ItmsGrpNam<=@ItmsGrpNam1) and (W1.Whscode>=@Whscode and W1.Whscode<=@Whscode1) and (C1.Location>=@Location and C1.Location<=@Location1) and T0.itemcode >=@itemcode and T0.itemcode <=@itemcode1 Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]+a.[OB Quantity]+a.[Purchase Quantity]+ a.[Purchase Return Quantity]+a.[Sale Return Quantity]+a.[sale Quatity]) !=0

Former Member
0 Kudos

Hi Thang Raj K,

This codes are not giving any results other than selection creteria.

Regards,

Abhishek

Former Member
0 Kudos

Hi!

I will check it and get back.

Former Member
0 Kudos

Hi!

For me the query is showing result.

Have u filled all the selection parameters ?

1. Select Whole Fysical Year

2. Select all Item Group (From To)

3. Select all Warehouses

4. Select all Locations

5. Select all Items

Former Member
0 Kudos

I will run with all selection creteria and let you know.

Regards

Edited by: Abhishek A on Aug 31, 2009 10:41 AM

Former Member
0 Kudos

Hi Thang Raj K,

The query is not giving correct results. In the query results many of the items showing negative inventory (qty as well as value) which is not the case. Please check the codes.

Regards

Former Member
0 Kudos

Hi!

Please run the Report and compare the same with your Audit Report.

U can also find which item is coming wrong and why ? (Any other Special Transactions used for that item ? Stock Posting, Adjusting...)

Please cross check with SAP Audit Report (Use Posting Date option in Audit Report)