Hi everyone!

 

I wanted to share some tips around troubleshooting an error we have seen periodically come into support.

 

When selecting the ALL Purchasing Transactions Navigation list, if you receive the error “Cannot insert the value Null into column 'DOCTYNAM', table 'tempdb.dbo#PODetailed." The stored procedure popselectpodocinquiry returned the following results.DBMS: 515; Microsoft Dynamics GP:0", you can use this blog to help guide you to a possible solution.

 

The error is typically due to some type of data corruption and will require investigation and knowledge of the Purchase Order Processing (POP) tables to determine what is causing the error and what should be deleted/updated.

 

The scripts below is where I would start. These scripts will not update anything, but instead pull specific data conditions that might be causing this issue. Each result needs to be reviewed. The scripts will look at any POTYPE and/or POPTYPE values that are not in a valid data condition, in addition will pull Vendor IDs that are blank. Typically one of these scenarios will cause this error.

 

The tables we focus on are:

  • POP10110 - Purchase Order Line              
  • POP10300 - Purchasing Receipt Work
  • POP30100 - Purchase Order History
  • POP30300 - Purchasing Receipt History  

 

Scripts:

I would recommend that you generate the provided SQL scripts into a TEXT file, so you can review the results in an easy format to read.

 

  1. Open a New Query window in SQL Server Management Studio.
  2. Copy and Paste in the scripts below:

 

Select * from POP10100 where POTYPE not in (1, 2, 3, 4) or VENDORID = ''

Select * from POP10300 where POPTYPE not in (1, 2, 3, 4, 5, 6, 7, 8) or VENDORID = ''

Select * from POP30100 where POTYPE not in (1, 2, 3, 4) or VENDORID = ''

Select * from POP30300 where POPTYPE not in (1, 2, 3, 4, 5, 6, 7, 8) or VENDORID = ''

 

  1. Change the database to your COMPANY database.
  2. Run the script to TEXT in the query menu (Go to Query > Results in Text).
  3. Execute the script (F5) or Query > Execute.

 

You do need to be familiar with the data to really understand what to look for and to determine what is and isn’t valid. Some things that appear to be invalid are actually valid. For example, one would think a blank Vendor ID would an issue, but if the document was voided it will not cause an error. The system knows it’s valid based on specific values in the tables. And a blank Vendor ID can occur depending on WHEN the user voided the document.

 

The POTYPE and POPTYPE fields of 0 typically come back as an issue for this error because 0 is not a valid value for these fields.   Please refer to the list below for the valid values for the POTYPE and POPTYPE fields:

 

POTYPE:

1 - Standard

2 - Drop-Ship

3 - Blanket

4 - Drop-Ship Blanket

 

POPTYPE:

1 - Shipment

2 - Invoice

3 - Shipment/Invoice

4 - Return

5 - Return w/Credit

6 - Inventory

7 - Inventory w/Credit

 

 

When you see results that appear to be invalid in the list, don’t delete it from the table as more investigation needs to take place.   My suggestion is to use the ALL PO and Receipts script or ALL POP Receipts script (link to those scripts below using instructions above) to review all of the data for the specific PO/receipt in question.  If the data is just in the work tables, it might be fine to just delete it, however if there are receipts posted against the PO or other integrations like Encumbrance Management in use, then you will need to consider these when determining the best direction to take to resolve the issue would be. It may end up where the data needs to updated or it maybe to delete and adjust out. Just all depends on what you find.

POP scripts: 

 

 

If you need help reviewing the scripts feel free to create a support request and ask for assistance. We are always happy to help.

I hope this helps identify potential issues causing the error!

Angela Ebensteiner | Sr. Technical Advisor | Microsoft Dynamics GP