Now Available in Community - MBAS 2019 Presentation Videos
Catch the most popular sessions on demand and learn how Dynamics 365, Power BI, PowerApps, Microsoft Flow, and Excel are powering major transformations around the globe. | View Gallery
2019 release wave 2 Discover the latest updates to Dynamics 365Release overview guides and videos Release Plan | Early Access Availability
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Talent TechTalks
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:
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.
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 = ''
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:
1 - Standard
2 - Drop-Ship
3 - Blanket
4 - Drop-Ship Blanket
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.
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
Business Applications communities