At some point in your SAP Business One consulting career you will get the question as to WHY the Inventory Audit Report (Inventory >> Inventory Reports >> Inventory Audit Report) doesn’t match the GL balances or balance sheet totals.
Generally this question is during the year end closing process where accountants want to see what makes up the balance of the inventory balance.
SAP Business One is designed to give you this information UNLESS you manually adjust the inventory accounts via journal entry. At the point where you manually adjust the inventory accounts then SAP will no longer spit out the inventory audit report with the exact same balances as the GL accounts.
If your customer is using the cycle count, inventory posting, inventory revaluation and goods issues/receipts they should be able to manage their inventory properly without the need to manually adjust with a journal entry. This is just lazy.
I wish SAP had the option to turn inventory accounts into control accounts like the Accounts Receivable and Accounts Payable accounts but the option doesn’t exist yet. So what I did was to just make a TransactionNotification query that will block manual journal entries to any account linked to an Item Group.
Here it is:
— MJT: Block manual journal entries to inventory accounts
/*
IF (@transaction_type = ‘A’ OR @transaction_type = ‘U’) AND @object_type = ’30’
BEGIN
IF EXISTS(SELECT T0.[TransId] FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId] INNER JOIN OITB T2 ON T1.[Account] = T2.[BalInvntAc]
WHERE T1.[TransType] = 30 AND T0.[TransId] = @list_of_cols_val_tab_del) AND (SELECT S0.[DspIITMDoc] FROM ADM1 S0) = ‘Y’
BEGIN
SELECT @error = -10, @error_message = ‘Direct Journal Entries to Inventory Accounts Not Permitted [Journal Entry – Account Code] [Message 60110-30]’
END
END
To install it you would want do the following steps:
1. Open MS SQL Management studio
2. Open your live DB (or test db if you want to verify)
3. Go to Programmability >> Stored Procedures
4. Find SBO_SP_TransactionNotification
5. Right click SBO_SP_TransactionNotification and select “Modify”
6. Find the space in the query where it says “– ADD YOUR CODE HERE”
7. Paste my code in
8. Push “!Execute” along the top
When it runs it should say “Command(s) completed successfully.” and then manual journal entries will be blocked. All other system generated entries will work fine, just the manual entries to inventory accounts.
Then when they run the Inventory Audit Report it will match perfectly!
Mike
helpful