Are you unable to Invoice a Purchase Order because of the following error: Cannot insert the value NULL into column 'UseQtyOverageTolerance' table?

Hello Community!

I hope you are all are doing well this wonderful day!

I wanted to take some time today discuss an error I’ve seen recently when you go to post an invoice receipt against a Purchase Order. “[Microsoft] [SQL Server Native Client 11.0] [SQL Server] Cannot insert the value NULL into column 'UseQtyOverageTolerance' table
‘tempdb.dbo.#PODetailed_____” 


After clicking OK to the above error message, the following error message will display.
“The stored procedure popSelectPOItemsLoadDexTables returned the following results: DBMS: 515, Microsoft Dynamics GP: 0."
Please consult the alert message documentation provided by your DBMS.”



As noted above, this error occurs when posting an invoice receipt, this can be either a shipment/invoice or an Enter/Match Invoice.

In this blog, I will describe what we’ve found to be the cause and a solution for this error.

Cause
This issue occurs when items are set as inventoried on the Purchase Order Line (POP10110) table, but do not exist on the Item Master (IV00101) table.

Resolution
Run the following SQL statement against the company database were the issue is present:

SELECT NONINVEN, * FROM POP10110 WHERE ITEMNMBR NOT IN (SELECT ITEMNMBR FROM IV00101)

For any of the items returned in this SQL statement, you will want to review each one individually to determine whether they should be non-inventory or inventoried items. 

If the items returned should be non-inventory, then you can update NONINVEN = 1 on the POP10110 table.

An example of what this statement would look like is:

UPDATE POP10110 SET NONINVEN = '1' WHERE ITEMNMBR = 'X'
*Replace X with the Item Number where the item should be non-inventory.

**NOTE:
Before running SQL statements that will change your database it is recommended that you test them in a test environment first. If this is not possible, make sure you have a recent restorable backup of the company database.

You can use the following test company if you do not have one and wish to create one.
KB - Set up a test company that has a copy of live company data for Microsoft Dynamics GP by using Microsoft SQL Server

If the items returned should be inventoried and the IV00101 record is missing, you will need to delete and recreate this item via the Item Maintenance window (Inventory >> Cards >> Item). Once the item has been re-created you will need to remove the line item from the Purchase Order and re-add it back.

I hope that the information offered has proven helpful to you and you are now able to invoice your purchase order without receiving the error “[Microsoft] [SQL Server Native Client 11.0] [SQL Server] Cannot insert the value NULL into column 'UseQtyOverageTolerance' table
‘tempdb.dbo.#PODetailed_____” when invoicing a Purchase Order

Until next time,
Nicole Fiskum | Support Engineer | Microsoft Dynamics