Skip to Content
0
Jun 07, 2018 at 12:11 PM

I have created Inventory Audit Report Query in SAP Business one & need In Value and Out Value also.

81 Views

Code is Written Below.

Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @Whse nvarchar(10)
select @FromDate = min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]'
select @ToDate = max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]'
select @Whse = Max(s2.Warehouse) from dbo.OINM S2 Where S2.Warehouse = '[%2]'
Select @Whse as 'Warehouse',(SELECT j.ItmsGrpNam FROM OITM i INNER JOIN OITB j ON i.ItmsGrpCod=j.ItmsGrpCod where i.ItemCode=a.Itemcode) as ItemGroup, (select i.U_SubGroup from OITM i where i.ItemCode =a.Itemcode) as SubGroup, a.Itemcode, max(a.Dscription) as ItemName, sum(a.OpeningBalance) as OpeningBalance,SUM(a.OpeningValue) as OpeningValue,
sum(a.INq) as 'IN', sum(a.OUT) as OUT, ((sum(a.OpeningBalance) + sum(a.INq)) - Sum(a.OUT)) as ClosingBalance,(SUM(a.OpeningValue) + SUM(a.ClosingValue)) as ClosingValue,
(Select i.InvntryUom from OITM i where i.ItemCode=a.Itemcode) as UOM



from ( Select N1.Warehouse, N1.Itemcode, N1.Dscription, (sum(N1.inqty)-sum(n1.outqty)) as OpeningBalance,CASE when sum(N1.InQty)> 0 then SUM(N1.TransValue)
when sum(N1.OutQty) > 0 then SUM(-N1.TransValue) end As OpeningValue, 0 as INq, 0 as OUT, 0 AS ClosingValue From dbo.OINM N1

Where N1.DocDate < @FromDate and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,
N1.Dscription Union All select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance,0 as OpeningValue, sum(N1.inqty) , 0 as OUT, 0 as ClosingValue
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
Union All
select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance,0 as OpeningValue, 0 , sum(N1.outqty) as OUT, 0 as ClosingValue
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
Union All
select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance,0 as OpeningValue, 0 , 0 as OUT, SUM(N1.TransValue) as ClosingValue
From dbo.OINM N1 Where N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,N1.Dscription) a Group By a.Itemcode Having sum(a.OpeningBalance) + sum(a.INq) - sum(a.OUT) > 0 Order By a.Itemcode

I tried a lot but i am new to this and help me out here.