Thanks for this suggestion.

I created a test database and table that would not accept duplicates.  I then started a trace and attempted to add duplicate data on the primary key.

The picture below shows the trace catching it but I don't see anything that associates this with a Primary Key Violation.  This is the error I get within Microsfot Management Studio:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_TestTable'. Cannot insert duplicate key in object 'dbo.TestTable'.
The statement has been terminated.

You would expect the same thing within the trace and then hopefully the exact values being passed, right?

How do I see this information?

Thanks!

oops forgot the picture:
trace.JPG

Hi,

One question:  Does Cogsdale prepare the refunds and then send them directly to AP?

Or does Cogsdale first sent the refunds as credits to the AR module, and then you use the GP Refunds Checks feature to send the refunds to the AP module?

If Cogsdale is sending the refunds directly to GP, then I believe you are seeing a classic AP voucher integration issue.  If not, then you can ignore the rest of this post and we'll have to come up with a new strategy to troubleshoot.

Assuming you have a direct AP integration, let's start with the error message, which is actually two errors.  

The first error message is telling you that something is trying to insert a "duplicate key" (a duplicate value) in the PM10000 table.  The key is set in the "PKPM10000" index, which is a composite index that includes the following fields:

BCHSOURC  (Batch source)
BACHNUMB  (Batch number)
VCHNUMWK  (Voucher number)

If you attempt to insert a record into PM10000 with values for these 3 fields, and there is already a record in PM10000 with the same values for those three fields, you will get that primary key error.  This is really caused by a duplicate voucher number.

Based on my experience integrating hundreds of thousands of payables invoices, this problem is due to at least one poor design choice in the integrating application.  

The integrating app is likely attempting to use the Payables Setup table to get the next voucher number from GP.  Although this would seem to be sensible, it is fraught with complications, and invariably you will get this error.  Due to some whacky weirdness that GP does when it issues voucher numbers to users, it is simply not possible for an external application to reliably get the next voucher number directly from the PM00400 table (by reading the table, getting the next voucher number, incrementing the number, and then updating the table with the new next number).  It may work 95% of the time, but you will eventually run into this problem if you are importing very many vouchers.

The second error is the PKPM00400 error, which is related to the PM00400 table (PM Key Master File).  This table keeps a copy of every document number that was issued out of Payables.  

Since the integration is attempting to insert a duplicate document number into PM10000, it is no surprise that it is also attempting to insert a duplicate documnet number into PM00400.  

The quick fix for this problem is to have all users close any Payables windows, especially the Payables Transaction Entry window, where voucher numbers are issued.

Then open the Payables Setup window (Dynamics GP -> Tools -> Setup -> Purchasing -> Payables), click on the Options button, and then change the Next Voucher Number to increase it to a new number that has not been used yet.

Click on OK to save the change and close all windows.

In general, this problem will occur whenever users have a Payables Transaction Entry window open, and you try and run the AP invoice integration.

The integration can be designed to avoid this error, but would require the external application to be modified.

Let me know if this makes sense.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

Sendow

Good stuff!   Could you explain this process one more time:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The integrating app is likely attempting to use the Payables Setup table to get the next voucher number from GP.  Although this would seem to be sensible, it is fraught with complications, and invariably you will get this error.  Due to some whacky weirdness that GP does when it issues voucher numbers to users, it is simply not possible for an external application to reliably get the next voucher number directly from the PM00400 table (by reading the table, getting the next voucher number, incrementing the number, and then updating the table with the new next number).  It may work 95% of the time, but you will eventually run into this problem if you are importing very many vouchers.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

When you got to, "...get the next voucher number directly...."   What is it about the read on the table (by the integrating app) that is not synchronized?

That's what I'm missing.  Thanks!

John

By the way gothamite, I found the check box to enable 'User Error Message'

Thanks!

You guys !  Between the two of you I was able to take a good look at this.  Thanks!

Steve ,

Again thanks.

We attempted (quick fix) to increment the Next Voucher Number.  However, it wasn't something they could just look at or consider and choose to back out.  They couldn't get out of the dialog and also were unable to pull any transactions up.   The A/P side shows batches with the correct number of transactions in it.

Any comments here?

Thanks

But, I can not get out of the screen you wanted me to go to even with Control/ Alt Delete.(GP>Tools>Setup>Purchasing>Payables then options ) this is where I can't get out of without making a change????  
I had Jenny look at the A/P side and it show batches with the correct number of transactions in it but no transactions will pull up????

Sorry for the extra comments there.  I was trying to transfer the message and forgot to delete that stuff.

:|

Hi,

Is there an error message or dialog that appears when you try and close the Options window?  Or is GP completely unresponsive or did it hang?

Normally the Options window should not require any values to be changed, and you should be able to close the window by clicking on the X in the upper right, or the OK button.

And is it the Options window that you can't close, or is the the Payables Management Setup window that won't close?

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

Steve,

It was the Payables Management Setup window.

One of our consultants logged in and corrected the 'freeze' but I don't know what that correction was.  The user attempted to close the window by using the X in the upper right.  I'm not sure but this user may have felt forced to enter a value and therefore did not want to select OK.

The consultant is aware of fix and is handling this issue.  I just figured I'd pick your brain for as much as I could until something came to a head.

Thanks!

I looked at that article you provided.  It says:

There is already a stored procedure for this called taGetPMNextPaymentNumber! All you need to do is call it.

Are you saying that the Payables Management Setup window will trigger this stored procedure to increment the voucher number?

The user was getting an error message similar to, "Invalid entry...."   In other words, they Window could not be X'd out of, but maybe it could have been OK'd out of.  Either way, you are saying that the user does not have to generate the number manually - they system will do it by calling the taGetPMNextPaymentNumber()  

The Payables Management Setup window - right?  

Hi,

taGetPMNextPaymentNumber is an eConnect stored procedure that can be called by an external application that needs to integrate / import payables transactions into GP.

Last I checked, which was a few years ago, the GP voucher window has it's own separate stored procedure that it calls to get the next voucher number.

The Payables Setup Options window simply SETS the next number.  It does not get the next number--that is done during data entry, or during transaction import.

My point is that if your Cogsdale application is trying to get the next voucher number without using taGetPMNextPaymentNumber, then that is likely the cause of the occasional error that you are seeing.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

Thanks for your help on this one.

How about taking a look at this one regarding an 'sa' password change and the affect on GP

https://www.experts-exchange.com/questions/26173531/How-to-change-the-sa-password-in-GP.html


Thanks!