One of our clients recently encountered the following error message when attempting to post a batch:
Text: Unhandled database exception: A save operation on table ‘PM_Distribution_HIST’ has created a duplicate key.
A solution that is commonly suggested is to rebuild the PM Keys table (PM00400) then run Check Links on the Payables series, but this did not resolve the error the client was experiencing. We ended up needing to go into the backend, and this is where we found our resolution.
In the SQL database, we noticed something odd. It seemed the batch was interrupted during posting right at the beginning of the process, so records from the first voucher number in the batch were present in both the work and historical tables. The batch was immediately throwing the save operation error whenever a user attempted to re-post it due to this duplication.
In order to resolve the error, we needed to remove the duplicate records from the following historical tables:
PM30300 – Apply To History
PM30600 – GL Distributions for Historical Transactions
To figure out which records were the duplicates, we ran a select statement against each voucher number present in the problematic batch. Since the batch was not fully posted, any voucher numbers associated with the batch that appeared in these tables were data we needed to remove.
The tables mentioned above can hold multiple copies of the same voucher number (VCHRNMBR) from different years, so we needed to include a secondary modifier in order to remove only the problematic transaction. In our case we used the Vendor ID (VENDORID), but values such as the posting date will work as well. Here are the scripts we used, replacing XXX with the Voucher Number, YYY with the Vendor ID, and ZZZ with the Batch ID.
delete PM30600 where VCHRNMBR = ‘XXX’ and VENDORID = ‘YYY’
delete PM30300 where VCHRNMBR = ‘XXX’ and VENDORID = ‘YYY’
update SY00500 set MKDTOPST=0, BCHSTTUS=0 where BACHNUMB = ‘ZZZ’
The delete statements were what removed the duplicate Voucher Number data; the update statement reset the batch to the Available status so that users could successfully post it.
This article was written by Daniel Gonet, Dynamics GP Application Consultant for Tridea Partners. Tridea is a leading Microsoft Dynamics provider serving CA, AZ, UT, CO and surrounding regions.