eConnect 2010 Does Not Properly Validate Purchasing Invoice Numbers

I developed a very interesting Purchasing Invoice integration using eConnect 2010 that recently went live after many months of work.  It was incredibly complex due to the client's unique business requirements and data, and the integration had to automatically handle a lot of unusual exceptions.

One situation was that vendors would sometimes split an invoice.  So on Monday, they may send data for Invoice 12345 that matches to 10 purchase receipts.  But then on Wednesday, they may send data for Invoice 12345 that matches to 5 other purchase receipts.  All of the data is technically valid, but because the data for Invoice 12345 is being received at two different times, Dynamics GP thinks that the second set of data for Invoice 12345 is a duplicate (since the client, like most GP customers, has enabled the option to not allow duplicate vendor invoice numbers).

To address this issue, I had to add a routine that would add a suffix to the invoice number when necessary, such as 12345-1, 12345-2, etc., so that we could avoid a duplicate invoice number error.  Because the import was for Enter/Match Invoice, we were matching against receipts, so there was little to no risk that a duplicate invoice would get imported.

This worked great, and handled the occasional situations where the same invoice number was transmitted on two different days.

So after quite a bit of testing, the integration went live this week, and we were able to import invoice data for the prior few weeks.  The import went very smoothly, bringing in thousands of vendor invoices.  The batches were reviewed and then posted, and we sighed in relief at the conclusion of a challenging project.

But when the client opened the Purchasing Batch window, some of the imported batches were still listed, and they contained some transactions.  When the client tried to post them again, the batch posting progress window would appear and the status would fly by quickly, but the transactions would still be sitting in the unposted batch.

I ran an Edit List on the batch, and to my surprise, the report said this under every transaction:

**ERROR: Can't post a document that uses a duplicate vendor document number.

This was odd, since during testing, we had obviously discovered that eConnect prevented the import of duplicate Purchasing Invoice document numbers, hence the need to add the -1, -2 suffix.

I checked the POP10300 and POP30300 tables, and confirmed that the invoice numbers were not duplicates.  But clearly GP thought they were, so it was obviously doing some type of additional validation.

So I opened SQL Server Profiler to start up a trace, and then opened the Purchasing Invoice Entry window to see what was going on.  When I modified my "duplicate" invoice number to a new value, then changed it back, sure enough, the GP UI gave me an error indicating that the number was a duplicate, even though eConnect had no problem importing the document.

I then looked at my SQL trace and found that in addition to checking  POP10300, GP is checking for the invoice number in PM00400.  Okay, that makes sense, but these particular invoices had never been entered before, so they shouldn't be in Payables either.

I then did a few inquiries on the invoice numbers, and sure enough, there they were.  They were brought in as Payables vouchers back in 2009.  Apparently, the vendor is re-using invoice numbers for some reason, and some of the numbers were now conflicting with invoices from 3 years ago.  This is the first time I've run into a vendor that re-issues invoice numbers, let alone a 7-digit invoice number in the course of 3 years.

When we looked back at the Purchasing batches that were still remaining, only transactions for one vendor were left.  So the problem is isolated to the one vendor, and only affected a few dozen invoices.

So, I learned that eConnect 2010 does not properly validate Purchasing Invoice numbers during import.  I now have to add extra validation to detect if the invoice number has been used in either Purchasing or Payables Management, and add a suffix to make it unique.

And we learned that when eConnect imports the Purchasing Invoices with duplicate invoice numbers, there will be no errors or warnings during posting.  We have to run the Edit List report to see the problem.

It has been an interesting journey...

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com