eConnect Error: Voucher Number already exists in PM Keys Master Table PM00400

By Steve Endow

At GPUG Summit 2015, I gave a presentation "5 Tips for Developing GP Integrations".  After the presentation, two attendees indicated that they were receiving these types of errors when import AP vouchers using eConnect:

Error Number = 305  Stored Procedure= taPMTransactionInsert  Error Description = Document Number (DOCNUMBR) already exists in the PM00400, PM10000, PM20000, PM30200, POP10300 or the MC020103 Table

Error Number = 306  Stored Procedure= taPMTransactionInsert  Error Description = Voucher Number (VCHNUMWK) already exists in PM Keys Master Table - PM00400


I'm very familiar with this error, as it occurred on my first eConnect integration, which I believe was with Great Plains 7.5, not too long after eConnect was released.

I could be wrong, since I was new to eConnect back then, but my recollection at the time was that there was no eConnect method or stored procedure to get the next voucher number.  I attempted to reverse engineer and reproduce the code that the Great Plains client used to get the next voucher number, but I apparently didn't have the SQL skills at the time to prevent duplicate numbers from being issued to my voucher integrations while 15 accounts payable clerks were pushing invoices into Great Plains all day.

However, in the years since, and now that I'm using the taGetPMNextVoucherNumber procedure, I haven't seen that error.  Perhaps all of my AP integrations since then have been in environments where there isn't much other AP import or entry activity, but my impression was that the taGetPMNextVoucherNumber stored procedure is pretty robust.

However, since I had two people in my GPUG Summit session tell me they were getting this issue, out of curiosity, I just setup 3 instances of an AP voucher import and had each of them import 1,000 vouchers at the same time.  They were running fine for a while, but then it happened.

Sure enough, having 3 simultaneous eConnect voucher imports produced the error pretty quickly.  So I opened up the procedure to see if I could tell how this could occur.

If you look at the main statement in the stored procedure, it would appear to be locking the PM next number table:

SELECT @O_vCNTRLNUM = NTVCHNUM
FROM PM40100 WITH (TABLOCKX HOLDLOCK);

However, looking at the rest of the procedure, I noticed that something was missing: The stored procedure doesn't use a SQL transaction!

So the procedure locks the table when selecting the next voucher number from PM40100, but after retrieving that next number it moves on to validate the number.  When it moves on, without an explicit transaction, this unlocks the PM40100 table, allowing another process to grab the same number from PM40100 before the incremented number can be written to the table.

As a test, I modified the stored procedure to add a transaction, along with appropriate rollbacks and a commit.

With the modified taGetPMNextVoucherNumber procedure in place, I launched 6 instances of my eConnect Batch Load Test tool and imported a total of 30,000 AP vouchers over 5 different runs.  The error didn't occur.

So it appears that adding a proper SQL transaction to the taGetPMNextVoucherNumber stored procedure will resolve the issue of duplicate voucher numbers during an eConnect import.

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

You can also find him on Google+ and Twitter

http://www.precipioservices.com