Error when running a Manufacturing Standard Cost Rollup/Replace in GP "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as a...

Hi Everyone,

If you receive the following error during a Standard cost rollup and replace process you will want to try these steps below. 

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=,<, <=, >, >= or when the subquery is used as an expression*"

A subquery error means that there is a duplicate in either the MFG setup tables or the BOM tables. When a database is restored into a test database, the MFG setup tables do not get updated with the test company’s company ID. They retain the original company’s company ID. If they then go into one of the MFG Setup windows, it creates a second line in the MFG setup tables. This second line can cause the subquery error.

Steps to correct:

Step 1

Run the company ID script. This can be found in the below blog.

https://support.microsoft.com/en-us/topic/kb-set-up-a-test-company-that-has-a-copy-of-live-company-data-for-microsoft-dynamics-gp-by-using-microsoft-sql-server-6199295b-fc49-d963-3865-2d24a4b49211

If it returns duplicate errors, you will need to look at the table that is reporting the duplicate. If there are two rows in the table, delete out one of the lines and rerun the company ID script. You will need to use SQL to delete out the extra line.

Step 2

If this does not correct your issue, check for recursive BOM in the database.

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

Look to see if there is an MFG BOM with a name assigned to it. A manufacturing BOM cannot have a BOM Name. 

Run the scripts to see if an MFG BOM has a BOM Name.

select * from BM010115 where BOMCAT_I ='1' and BOMNAME_I <> ''

select * from BM010415 where BOMCAT_I ='1' and BOMNAME_I <> ''

If the about script return results, you must delete the line using SQL.

Step 4

Contact the support desk for assistance.