Error message when you print the Historical Stock Status report in Microsoft Dynamics GP: Unhandled script exception: Divide by zero

If you receive a Divide by Zero error printing the Historical Stock Status report (HSSR) in Microsoft Dynamics - GP, run through the following steps to help identify the problem records causing the error.  Exact error below:

Unhandled Script exception:

SCRIPTS - data area

EXCEPTION_CLASS_SCRIPT_DIVIDE_BY_ZERO

SCRIPT_CMD_DIV

Note: Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.

Cause

This problem is typically caused by some type of data corruption in the IV30300 table. Most common are:

1. Specific document types in the IV30300 are missing a transaction source.

    

2. Specific document types with invalid values of 0 in the DECPLCUR and DECPLQTY fields.

Cause 1

Specific document types in the IV30300 are missing a transaction source. Each document that is posted through Microsoft Dynamics GP should have a transaction source associated with it. Due to a posting interruptions or an integration/customization issues, this value may be missing. Use the script below to identify any documents that have a blank transaction source. All of these document types listed should have a transaction source. If they do not have a transaction source the records need to be fixed or removed.

1. Run the following statement against the company database in SQL Server Management Studio to identify transactions with this condition:

SELECT * FROM IV30300 WHERE TRXSORCE  = '' and  doctype in (1, 2, 3, 4, 5, 6, 7) 

1. If any documents get returned using the script above, run a select statement on the document number listed against the following Inventory tables (IV30300, SEE30303, IV10200, IV10201) and investigate where the corruption is or if it’s only in the IV30300. It might be a corrupt record or it might be partially updated. Each line returned will have to be investigated and determined how to proceed with fixing.  If it’s something that can be deleted, we suggest deleting based on DEX_ROW_ID.

Example:

DELETE IV30300 WHERE DEX_ROW_ID = XXX

Example from a case: Below is a select statement based on one of the documents that was returned in a case I worked on. Notice both lines being almost identical. The document should have only one line returned. I figured it must have been some type of posting interruption that caused the corruption because all the other tables in inventory posted just one line. I removed corrupt record missing the transaction source and the report printed.

If assistance is needed in determining what to do with the data, please reach out to your Microsoft Partner or contact Technical Support for assistance.

Cause 2

Specific document types with invalid values of 0 in the DECPLCUR and DECPLQTY fields.

Note The DECPLCUR field refers to the captured currency decimal place and the DECPLQTY field refers to the captured quantity decimal place for the item. On all valid transactions this should be populated with a value.

1. Run the following statement against the company database in SQL Server Management Studio to identify transactions with this condition:

Select * from IV30300 where (DECPLQTY = 0 or DECPLCUR = 0) and  doctype in (1, 2, 3, 4, 5, 6, 7)           

If you find documents with 0 decimal places, these need to be investigated. Correct the corrupted transaction by using SQL Query Analyzer by updating the fields to the appropriate value.

Decimal place values in the IV30300 are as follows:

Example update statement below:

UPDATE IV30300 set DECPLQTY = 3 WHERE DEX_ROW_ID = XXX

If the results are all old transactions and you prefer to Remove history for the corrupted transactions, you can remove history by pointing to Tools on the Microsoft Dynamics GP menu, point to Utilities, point to Inventory, and then click Remove Transaction History and removing the appropriate document number(s).

More information:

If neither script from Cause 1 or 2 return any results, focus on identifying the problem item by printing the Historical Stock Status Report by item number. Narrow in on the item numbers using a range.   Start with a smaller range and work your way through the alphabet to identify the problem item. For example, enter a range of items starting with the letter A through D. Print that range and see if you receive the error. If you don’t continue down the alphabet until you have identified the item with the issue. Once you identified the item, get the results of the IV30300 for that item and start investigating.  

If you still are unable to find the issue item/document, run a SQL Profile Trace and trap the error. Make sure to leave the error on the screen (don’t click past it) and then stop the trace. Typically, at the end of the trace you should see the document number that’s causing the error and can investigate from there. If you clean up the one item and still receive the error, create a new trace and see if it’s finding another culprit transaction.

Possible Workaround: Another option is to try printing with or without the “Use GL Posting date” on the Report Option to see if one option works verses the other. It might give you an option to print the report while you investigate the issue and identified the problem.

Hope this information helps you identify what is causing the Divide by zero error on the Historical Stock Status report (HSSR).

Thanks!

Angela Ebensteiner | Sr. Technical Advisor | Microsoft Dynamics GP