Error when running a Manufacturing Standard Cost Rollup/Replace in GP: Arithmetic overflow error converting IDENTITY to data type int.

Hi Everyone,

If you are running a Standard cost Rollup/Replace and you received an Arithmetic overflow error converting IDENTITY to datatype int error, this can be caused from values in the BOMLEV1 and BOMLEV2 tables. The BOMLEV tables can have a  dex_row_ids in the billions or the Identity field is no longer working.

Steps to correct:

Step 1

Rebuild the BOMLEV1 and BOMLEV2 tables.

**Please have a backup before running the SQL maintenance.
a. Go to Microsoft Dynamics GP | Maintenance | SQL.
b. Select your company database and a Product of Manufacturing.
c. Scroll down and select Bomlevel_1 and Bomlevel_2 from the list.
d. Check all 6 boxes on the right side.
e. Click process. It will have a notice that it is going to delete the information in the table which is fine. These tables hold temp information for the rollup process and are cleared each time.

Step 2

Verify there are no recursive BOMs.

Run Low Level Codes Utility (Tools | Utilities | Manufacturing | MRP Low Level Codes). I would suggest setting the Max Number of Levels for any BOM to 110.

I would suggest also running the executable in SQL, even if you have ran the Utilities tool. Run the below executable against your company database.

                  exec mbomLLCUtility 110

Make sure there are no recursive BOMs check the LLC in IVR10015 with the following script.

Select * from IVR10015 where LLC =’110’

If the above script returns results, it means that there is some type of recursive BOM in their system. There will be a Make/Buy Code column in the results. Look for items that have a code of 3. These are subassemblies. These subassemblies contain their finished good as a component. You will need to delete these BOMs. If it is not apparent which BOM needs to be deleted run the script again but set the LLC to a smaller number, say 105. Look at this item it is usually the item or is assigned directly to the item causing the duplicate.

Step 3

Contact the support desk.