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'
OR
EXISTS(
SELECT
T0.[TransId]
FROM
OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OWHS 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
(You may have to manually convert curly single quotes to simple single quotes or "Paste as Text")
To install it you would want do the following steps:
- Open MS SQL Management studio
- Open your live DB (or test db if you want to verify)
- Go to Programmability >> Stored Procedures
- Find SBO_SP_TransactionNotification
- Right click SBO_SP_TransactionNotification and select “Modify”
- Find the space in the query where it says “– ADD YOUR CODE HERE”
- Paste my code in
- 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
