Getting Cumulative Values in System Currency (SC) from Inventory Audit Report

[macdonald_ngowi]

Hi guys,

I was looking into getting SC values from the Inventory Audit Report but unfortunately the SAP report only provides values in Local Currency (LC). So I started researching on a solution and the only way I know so far is through a query. I made some development on the query and I was able to get LC values now the problem is to get correct values for SC. Currently the SC values i'm getting are very long numbers which don't make any sense. The query is below:

Declare @FromDate Datetime

Declare @ToDate Datetime

Declare @Whse nvarchar(10)

Declare @ItemCode nvarchar(8)

select @FromDate = min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='2017-01-01 00:00:00.000'

select @ToDate = max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='2017-01-24 00:00:00.000'

select @Whse = Max(S2.Warehouse) from dbo.OINM S2 Where S2.Warehouse = '01'

select @ItemCode = Min(S3.ItemCode) from dbo.OINM S3 Where S3.ItemCode = 'HW008003'

select @ItemCode = Max(S4.ItemCode) from dbo.OINM S4 Where S4.ItemCode = 'HW008003'

Select @Whse as 'Warehouse', a.Itemcode, max(a.Dscription) as ItemName,

sum(a.OpeningBalance) as OpeningBalance, sum(a.INq) as 'IN', sum(a.OUT) as OUT,

((sum(a.OpeningBalance) + sum(a.INq)) - Sum(a.OUT)) as Closing,

(Select i.InvntryUom from OITM i where i.ItemCode=a.Itemcode) as UOM,

sum(a.[Cummulative Value LC]) as 'Cummulative Value (LC)',

sum(a.[Cummulative Value SC])*(select rate from ortt
where ratedate = '2017-01-25 00:00:00.000'
and currency = (select syscurrncy from oadm)) as 'Cummulative Value (SC)'

from( Select N1.Warehouse, N1.Itemcode, N1.Dscription, (sum(N1.inqty)-sum(n1.outqty))

as OpeningBalance, 0 as INq, 0 as OUT, sum(N1.TransValue) as 'Cummulative Value LC', sum(N1.TransValue)*(select rate from ortt
where ratedate = '2017-01-25 00:00:00.000'
and currency = (select syscurrncy from oadm)) as 'Cummulative Value SC' 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,

sum(N1.inqty) , 0 as OUT, sum(N1.TransValue) as 'Cummulative Value LC', sum(N1.TransValue)*(select rate from ortt
where ratedate = '2017-01-25 00:00:00.000'
and currency = (select syscurrncy from oadm)) as 'Cummulative Value SC' 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 , sum(N1.outqty) as OUT, sum(N1.TransValue) as 'Cummulative Value LC', sum(N1.TransValue)*(select rate from ortt
where ratedate = '2017-01-25 00:00:00.000'
and currency = (select syscurrncy from oadm)) as 'Cummulative Value SC'

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) a, dbo.OITM I1

where a.ItemCode=I1.ItemCode

Group By a.Itemcode Having sum(a.OpeningBalance) + sum(a.INq) + sum(a.OUT) > 0 Order By a.Itemcode

The exchange rate is from today 25th of January 2016 and the SC currency is USD and LC currency is Tanzanian Shillings (TZS). I might be missing something on the SC calculation part or the formula is wrong somewhere. If anyone can assist, I'll be very grateful.

Thanks a bunch,

- Macdonald

[julie_jamieson2]

The above will be revaluing all your transactions at the rate on 25th Jan, is that what you want to do? It will also only be making the calculation on lines where the currency is already the same as the system currency