Identifying Duplicate Transactions

By David Musgrave

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/05/identifying-duplicate-transactions.aspx

Because Microsoft Dynamics GP is designed to have separate tables for WORK, OPEN and HISTORY transactions, there are times where it is possible for a transaction record to exist in more than one table. This is usually the result of an error or interruption.

These duplicate records become an issue when it comes time to move a transaction from one table to another. For example: when it is posted, fully applied, or paid transaction removal is used. At this time, the duplicate will generate a duplicate key error and the process will be aborted.

Another time when duplicate records can cause problems is when using an Inquiry window which can show data from more than one of the WORK, OPEN and HISTORY tables at the same time. To achieve this, the Inquiry windows transfer data into a single temporary table and use this table for their display. If duplicate records exist, you will get an error when the data is being copied into the temporary table.

An error message that may be generated by several inquiry windows, when there are duplicates, refers to a createSQLTmpTable stored procedure. This stored procedure does not actually exist, but is in fact referring to pass through SQL script called from the Dexterity code. For example: The error message from the Payables Transaction Inquiry window is The stored procedure createSQLTmpTable returned the following results, DMBS: 2627, Microsoft Dynamics GP: 0. Error 2627 is a SQL Cannot insert duplicate key error.

To make it easier to find duplicates, I am providing the following SQL queries to look for duplicate headers in the core Distribution and Financial modules. While these will not find every possible duplicate in every table, they are a great basis for checking for duplicate transactions.

SQL Script to look for duplicate headers in SOP, IVC, POP, RM, PM, IV, & GL modules

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/05/identifying-duplicate-transactions.aspx

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/