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:

  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

To report this post you need to login first.

17 Comments

You must be Logged on to comment or reply to a post.

  1. Kathy Marshall

    Hi Mike,

    This is really helpful, except that our database apparently does not have this or any SBO_ stored procedure.  Under the Stored Procedures folder is just another folder labeled System Stored Procedures, all of which start with sys.sp_ and none of them are named TransactionNotification.  We would love to use your code. Any thoughts?

    (0) 
    1. Mike Taylor Post author

      Hi Kathy,

      Your database has to have these stored procedures, there is no way that you would have a functioning database without them.

      Go to SQL Management Studio.  Click Databases >> <Your SBO Database> >>Programmability >> Stored Procedures >> SBO_SP_TransactionNotification.  Then right click >> modify.

      Please confirm you can see this StorProc, thank you,

      Mike

      (0) 
    2. Mike Taylor Post author

      You could try going into your SAP Business One client then selecting, Help >> Support Desk >> Restore >> Restore Stored Procedures… Make sure you only do this in a test db, not production.

      You should also make sure you are logged in with the sa user account.  Maybe if you are logging in under a Windows Authentication to the SQL Management Studio you don’t have access?

      Two things to try,

      Mike

      (0) 
      1. Kathy Marshall

        Thanks Mike, Logging on as “sa” did the trick.  Just so you know, your code has an unmatched /* comment block indicator.  Also, for other users’ info, Window copied all the quote marks as curly quote marks which I had to re-type.

        The code did not work for us because in table OITB, field BalInvntAc values are all null.  GL determinations are made from table OGAR. I’ll see if I can modify the code.

        Thanks again.  Kathy

        (0) 
        1. Mike Taylor Post author

          Yes, I fixed the open comment.  I also modified the syntax to be SQL highlighted so it should copy properly without the curly quotes.

          For your BalInvntAc this means you likely do your GL account determination by warehouse.  Is this correct?  Open up any item master and check the inventory tab.  It will display “Set G/L Accounts By” and this should say “Item Group” for my code to work.  I will have to post a warehouse validation method.

          Mike

          (0) 
            1. Mike Taylor Post author

              Hi Kathy,

              This should do it now for both methods.  Any inventory account attached to an item group or warehouse will trigger the block.

              Mike

              (0) 
              1. Kathy Marshall

                We tried the warehouse code today.  Still didn’t work only because our users don’t use the OWHS table.  They use the OGAR (GL account determinations table) and OACP (Rules table).  So I copied your exists section twice and put in each of these tables with join ON T1.[Account] = T2.[StockAct].  That did work.  Thanks again.

                (0) 
                1. Mike Taylor Post author

                  Try, Administration >> System Initialization >> Company Details.  “Basic Initialization” tab and then tell me if “Enable Advanced G/L Account Determination” is checked.  There is no way you don’t have the OWHS, BUT you may have enabled advanced GL account determination from day 1 and don’t have anything in the columns.  Is this the case?

                  (0) 
  2. Kathy Marshall

    Hi again Mike,

    What is DspIITMDoc from table ADM1 and why do you check it in your code?  I’m writing code to make sure a Goods Receipt PO has a corresponding PO, and I don’t know if I need to check this field.  Thanks, Kathy

    (0) 
    1. Mike Taylor Post author

      Hi Kathy,

      I was using it as a bypass.  The checkbox under Administration >> System Initialization >> General Settings >> Inventory Tab.  Rename the checkbox “Display Inactive Items in Marketing Documents” to something like “Allow Journal Entries to Inventory Accounts.”  Then when you check this off it will allow a user to toggle the ability to do journal entries when normally it’s blocked.

      You can switch this checkbox somewhere else, this is just what I used.

      For your validation you would not need to check this field.  You can make a UDF to bypass the validation.

      I always like to have a bypass because there is always some sort of scenario when a user doesn’t want the validation to block.  You can even do it by user if you need to.

      Mike

      (0) 

Leave a Reply