cancel
Showing results for 
Search instead for 
Did you mean: 

Inventory Audit Report

Former Member
0 Kudos

Hi All

i am trying this query for inventory audit report .in this query i am getting stock qty of opening balance, in quantity,out qty,closing balance qty. now i want to add value of opening balance ,in qty, out qty, closing qty.

-



Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @Group nvarchar(10)
 
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 @Group = (Select Max(s2.ItmsGrpCod) from OITB S2 Where S2.ItmsGrpNam = '[%2]')
 
Select 
a.Itemcode,
a.Dscription,
sum(a.[Opening Balance]) as [Opening Balance],
sum(a.[IN]) as [IN],
Sum(a.OUT) as OUT,
((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing from(
 
Select
N1.Itemcode,
N1.Dscription,
(sum(N1.inqty)-sum(n1.outqty)) as [Opening Balance],
0 as [IN],
0 as OUT
 
From OINM N1
Where
N1.DocDate < @FromDate
Group By
N1.ItemCode,N1.Dscription
 
Union All
select
N1.Itemcode,
N1.Dscription,
0 as [Opening Balance],
sum(N1.inqty) as [IN],
0 as OUT
 
From OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and
N1.Inqty >0
Group By
N1.ItemCode,N1.Dscription
 
Union All
select
N1.Itemcode,
N1.Dscription,
0 as [Opening Balance],
0 as [IN],
sum(N1.outqty) as OUT
 
From OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and
N1.OutQty > 0
Group By
N1.ItemCode,N1.Dscription)a, OITM I1
where
a.ItemCode=I1.ItemCode and
I1.ItmsGrpCod = @Group
 
Group By
a.Itemcode,a.Dscription

Thanks In advance

Areeba Ali

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

Would like similar report but to include Bin. I need a query that will provide Opening Balances as of a input date.

Item, item description, whse, bin, QOH

Thanks

Former Member
0 Kudos

Hi Areeba ,

Why do not you using the standard Inventory Audit Report , where you will get the Required Filteration.

Thanks

--

Ashish Ranjan

Former Member
0 Kudos

Hi Ashish

standard Inventory Audit giving all item detail of item group

i want only group total of each and every item Group transaction .

Former Member
0 Kudos

Hi Areeba ,

You can do the Total of the Group Item in the PLD's

and hiding the Detail Area .

Or

You can export the data into Excel and can modify as you wish.

Thanks

--

Ashish Ranjan

Former Member
0 Kudos

Thanks Ashish

It' s not possible because client have 100 item group how can they filter data in excel

Thanks

Areeba Ali

Former Member
0 Kudos

ok

Then what about the Group Total in PLD.

Thanks

--

Ashish

VamcParimisetti
Contributor
0 Kudos

Hi Arreba,

Try This :

Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @Group nvarchar(10) 
Declare @Whse nvarchar(10)

 
Set @FromDate = (Select min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]')
Set @ToDate = (Select max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]')
Set @Group = (Select Max(s2.ItmsGrpCod) from dbo.OITB S2 Where S2.ItmsGrpNam = '[%2]')
Set @Whse = (Select Max(s3.Warehouse) from dbo.OINM S3 Where S3.Warehouse = '[%3]'  )

 
Select 
@Whse as 'Warehouse',
a.Itemcode,
max(a.Dscription) as 'Description',MAX(a.Price) as 'Price',
sum(a.[Opening Balance]) as [Opening Balance],
sum(a.[IN]) as [Receipt],
sum(a.OUT) as [Issue],
((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing,
( MAX(a.Price) *  ((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) ) as ClosingValue
from(
Select
N1.Warehouse,
N1.Itemcode,
N1.Dscription,N1.Price,
(sum(N1.inqty)-sum(n1.outqty)) as [Opening Balance],
0 as [IN],
0 as OUT
 
From dbo.OINM N1
Where
N1.DocDate < @FromDate and N1.Warehouse = @Whse
Group By
N1.Warehouse,N1.ItemCode,N1.Dscription,N1.Price
 
Union All
select
N1.Warehouse,
N1.Itemcode,
N1.Dscription,N1.price,
0 as [Opening Balance],
sum(N1.inqty) as [IN],
0 as OUT
 
From dbo.OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and
N1.Inqty >0
and N1.Warehouse = @Whse
Group By
N1.Warehouse,N1.ItemCode,N1.Dscription,N1.price
 
Union All
select
N1.Warehouse,
N1.Itemcode,
N1.Dscription,N1.price,
0 as [Opening Balance],
0 as [IN],
sum(N1.outqty) as OUT
 
From dbo.OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and
N1.OutQty > 0
and N1.Warehouse = @Whse
Group By
N1.Warehouse,N1.ItemCode,N1.Dscription,N1.price) a, dbo.OITM I1
where
a.ItemCode=I1.ItemCode and
I1.ItmsGrpCod = @Group 
Group By
a.Itemcode

Order By a.Itemcode

Regards,

PV

Former Member
0 Kudos

Thanks Ashish

Not Showing Group In PLD

Former Member
0 Kudos

Thanks Vamsi

But it is showing only closing balance value

thanks

Areeba Ali

Former Member
0 Kudos

i got my query

hope it can help .

select T.ItmsGrpNam, T.ItemCode, T.ItemName, sum(T.OpeningQty) c1, sum(T.OpeningVal) c2,

sum(T.InQty) c3, sum(T.InVal) c4, sum(T.OutQty) c5, sum(T.OutVal) c6, sum(T.ClosingQty) c7,

sum(T.ClosingVal) c8 from ((SELECT T2.[ItmsGrpNam], T1.ItemCode, T1.ItemName, sum(T0.[InQty] - T0.[OutQty]) OpeningQty,

sum(case when T0.[OutQty]>0 then T0.[TransValue] else T0.[TransValue] end) OpeningVal, 0.00 InQty, 0.00 InVal, 0.00 OutQty, 0.00 OutVal,

0.00 ClosingQty, 0.00 ClosingVal FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod

where T0.DocDate < {?P1} and (T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL') GROUP BY T2.[ItmsGrpNam], T1.ItemCode, T1.ItemName)union

all(SELECT T2.[ItmsGrpNam], T1.ItemCode, T1.ItemName, 0.00, 0.00, sum(T0.[InQty]), sum(case when T0.[OutQty]>0 then 0.00

else T0.[TransValue] end), 0.00, 0.00, 0.00, 0.00 FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB

T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod where T0.DocDate >= {?P1} and T0.DocDate <= {?P2} and (T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL')

GROUP BY T2.[ItmsGrpNam], T1.ItemCode, T1.ItemName)union all(SELECT T2.[ItmsGrpNam], T1.ItemCode, T1.ItemName, 0.00, 0.00, 0.00, 0.00,

sum(T0.[OutQty]), sum(case when T0.[OutQty]>0 then -1*T0.[TransValue] else 0.00 end), 0.00, 0.00 FROM OINM T0 INNER JOIN OITM T1 ON

T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod where T0.DocDate >= {?P1} and T0.DocDate <= {?P2} and

(T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL') GROUP BY T2.[ItmsGrpNam], T1.ItemCode, T1.ItemName)union all(SELECT T2.[ItmsGrpNam],

T1.ItemCode, T1.ItemName, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, sum(T0.[InQty] - T0.[OutQty]), sum(case when T0.[OutQty]>0 then

T0.[TransValue] else T0.[TransValue] end) FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON

T1.ItmsGrpCod = T2.ItmsGrpCod where T0.DocDate <= {?P2} and (T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL') GROUP BY T2.[ItmsGrpNam],

T1.ItemCode, T1.ItemName)) T group by T.ItmsGrpNam, T.ItemCode, T.ItemName order by T.ItmsGrpNam, T.ItemCode, T.ItemName

i got my query

hope it can help .

select T.ItmsGrpNam, T.ItemCode, T.ItemName, sum(T.OpeningQty) c1, sum(T.OpeningVal) c2,

sum(T.InQty) c3, sum(T.InVal) c4, sum(T.OutQty) c5, sum(T.OutVal) c6, sum(T.ClosingQty) c7,

sum(T.ClosingVal) c8 from ((SELECT T2.ItmsGrpNam, T1.ItemCode, T1.ItemName, sum(T0.InQty - T0.OutQty) OpeningQty,

sum(case when T0.OutQty>0 then T0.TransValue else T0.TransValue end) OpeningVal, 0.00 InQty, 0.00 InVal, 0.00 OutQty, 0.00 OutVal,

0.00 ClosingQty, 0.00 ClosingVal FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod

where T0.DocDate < {?P1} and (T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL') GROUP BY T2.ItmsGrpNam, T1.ItemCode, T1.ItemName)union

all(SELECT T2.ItmsGrpNam, T1.ItemCode, T1.ItemName, 0.00, 0.00, sum(T0.InQty), sum(case when T0.OutQty>0 then 0.00

else T0.TransValue end), 0.00, 0.00, 0.00, 0.00 FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB

T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod where T0.DocDate >= {?P1} and T0.DocDate <= {?P2} and (T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL')

GROUP BY T2.ItmsGrpNam, T1.ItemCode, T1.ItemName)union all(SELECT T2.ItmsGrpNam, T1.ItemCode, T1.ItemName, 0.00, 0.00, 0.00, 0.00,

sum(T0.OutQty), sum(case when T0.OutQty>0 then -1*T0.TransValue else 0.00 end), 0.00, 0.00 FROM OINM T0 INNER JOIN OITM T1 ON

T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod where T0.DocDate >= {?P1} and T0.DocDate <= {?P2} and

(T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL') GROUP BY T2.ItmsGrpNam, T1.ItemCode, T1.ItemName)union all(SELECT T2.ItmsGrpNam,

T1.ItemCode, T1.ItemName, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, sum(T0.InQty - T0.OutQty), sum(case when T0.OutQty>0 then

T0.TransValue else T0.TransValue end) FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON

T1.ItmsGrpCod = T2.ItmsGrpCod where T0.DocDate <= {?P2} and (T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL') GROUP BY T2.ItmsGrpNam,

T1.ItemCode, T1.ItemName)) T group by T.ItmsGrpNam, T.ItemCode, T.ItemName order by T.ItmsGrpNam, T.ItemCode, T.ItemName

Former Member
0 Kudos

thank you so much for posting this.

this was such a huge help.

been looking for a report such as this.

thank you again!

former_member196081
Active Contributor
0 Kudos

Hi ,

Try below..



DECLARE @FromDate DateTime
DECLARE @ToDate DateTime
/* SELECT T0.DocDate FROM OINM T0 where T0.DocDate >=[%0] */
SET @FromDate = [%0]
/* SELECT T0.DocDate FROM OINM T0 where T0.DocDate <=[%0] */
SET @ToDate = [%1]
Select X.DocDate, X.ItemCode,
((Select isnull(sum(InQty-OutQty),'0')From OINM T0 Where T0.ItemCode=X.ItemCode And 
(T0.DataSource='I' or T0.DataSource='N') And T0.DocDate < X.DocDate)) As 'OpeningStock',
IsNull(max(X.price),0) as 'OpeningUnitPrice',
 
((Select isnull(sum(InQty-OutQty),'0')From OINM T0 Where T0.ItemCode=X.ItemCode And 
(T0.DataSource='I' or T0.DataSource='N') And T0.DocDate < X.DocDate)) *
IsNull(max(X.price),0) AS 'OPENING VALUE',
 
IsNull(sum(X.InQty),0) As 'Received Qty',
IsNull(max(X.price),0) as 'Purchase  Unit Price',
 
IsNull(sum(X.InQty),0) *
IsNull(max(X.price),0) as 'PURCHASE VALUE',
 
IsNull(sum(X.OutQty),0) As 'Consumed Qty',
IsNull(max(X.price),0) as 'Sale Unit Price',
 
IsNull(sum(X.OutQty),0) *
IsNull(max(X.price),0) AS 'SALE VALUE',
 
((Select isnull(sum(InQty-OutQty),'0') From OINM T0 Where T0.ItemCode=X.ItemCode And 
(T0.DataSource='I' or T0.DataSource='N') And T0.DocDate < X.DocDate)+ 
(Select isNULL(sum(T0.InQty),'0') From OINM T0 Where T0.ItemCode=X.ItemCode And T0.DocDate<=X.DocDate And 
(T0.DataSource='I' or T0.DataSource='N') And T0.DocDate = X.DocDate)-
(Select isNULL(sum(T0.OutQty),'0') From OINM T0 Where T0.ItemCode=X.ItemCode And T0.DocDate<=X.DocDate And 
(T0.DataSource='I' or T0.DataSource='N') And T0.DocDate = X.DocDate)) As 'ClosingStock', 
 
((((Select isnull(sum(InQty-OutQty),'0')From OINM T0 Where T0.ItemCode=X.ItemCode And 
(T0.DataSource='I' or T0.DataSource='N') And T0.DocDate < X.DocDate)) *
IsNull(max(X.price),0)) + (IsNull(sum(X.InQty),0) * IsNull(max(X.price),0)) - (IsNull(sum(X.OutQty),0) *
IsNull(max(X.price),0))) as 'Final Balance Value'
FROM OINM X Where ItemCode=X.ItemCode And 
(X.DataSource='I' or X.DataSource='N') 
Group by X.DocDate,X.ItemCode
Order by X.DocDate

Regards

Deepak Tyagi

Former Member
0 Kudos

thanks Deepak

But i want this report with additional parameter Item Group Wise .

0 Kudos

please send month wise report Gvenubabu82@gmail.com

Former Member
0 Kudos

waiting for Ans?