Inventory Audit Report

MAR
03
2015
Inventory Audit Report

Inventory Audit Report

As most of you know, the common question about the inventory report in SAP Business one is: “Why my Inventory Audit Report does not match my inventory G/L account balance?”

Well, the simple answer¬†would be: “Because you have Manual Journal Entries created with the Inventory G/L accounts.”

But, as you may find in some SAP notes, Manual Journal Entries are not the only reason.

Here I’ll provide some queries that you can run to compare the results of the Inventory Audit Report and other transactions that can cause the inventory G/L accounts to have a different balance.

Inventory audit report per G/L account

Declare @FromDate Datetime
Declare @ToDate Datetime
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 a.InvntAct, 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 from ( Select N1.InvntAct, (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 Group By N1.InvntAct Union All select N1.InvntAct, 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   Group By N1.InvntAct
Union All 
select N1.InvntAct, 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
Group By N1.InvntAct
Union All 
select N1.InvntAct, 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 Group By N1.InvntAct ) a Group By a.InvntAct Having sum(a.OpeningBalance) + sum(a.INq) - sum(a.OUT) > 0

Manual Journal Entries to inventory G/L accounts

SELECT
 T1.Account, sum(T1.Debit-T1.Credit) Balance
FROM
OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
WHERE
T0.TransType = 30 and
T1.Account in (SELECT distinct InvntAct FROM OINM)
and T0.RefDate <= '[%0]'
GROUP BY
 T1.Account

Service AP Invoices and/or Credit Memos

SELECT A.AcctCode, SUM(A.Total) [Total]
FROM
(SELECT  T1.AcctCode, SUM(T1.LineTotal) [Total]
FROM OPCH T0
INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocType = 'S'
AND T1.AcctCode IN (SELECT distinct InvntAct FROM OINM)
AND T1.LineTotal <> 0 AND T0.DocDate <= '[%0]'
GROUP BY T1.AcctCode
UNION ALL
SELECT T1.AcctCode,sum( -1 * T1.LineTotal) [Total]
FROM ORPC T0
INNER JOIN RPC1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocType = 'S'
AND T1.AcctCode IN (SELECT distinct InvntAct FROM OINM)
AND T1.LineTotal <> 0 AND T0.DocDate <= '[%0]'
group by T1.AcctCode) A
group by A.AcctCode




Goods Issue or Goods Receipt with the wrong offset account

select DocEntry, ItemCode, LineTotal, AcctCode from IGE1 where AcctCode in (Select distinct InvntAct from OINM)

union all

select DocEntry, ItemCode, LineTotal,AcctCode from IGN1 where AcctCode in (Select distinct InvntAct from OINM)

Inventory Revaluation with wrong offset account

select DocEntry, ItemCode, Quantity, LineTotal, RIncmAcct from MRV1 where RIncmAcct in (Select distinct InvntAct from OINM)

#Updated: 03/09/2016

If you want to prevent manual JEs to be posted against Inventory Accounts, you want to check this post by Mike Taylor :

http://scn.sap.com/community/business-one/blog/2015/01/17/tips-tricks-inventory-audit-report-doesn-t-match-gl-balance




Leave a Reply

*

captcha *