Developing for Microsoft Dynamics GP by David Musgrave (Australia) and the Microsoft Dynamics GP Developer Support Team (USA)Syndicated From: http://blogs.msdn.com/b/DevelopingForDynamicsGP/
Click on the link to see the full About Page.
From the Useful SQL Scripts Series.
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.
-- SOP Duplicatesselect SOPTYPE, SOPNUMBE, COUNT(*) as [COUNT] from (select SOPTYPE, SOPNUMBE from SOP10100 WUNION ALLselect SOPTYPE, SOPNUMBE from SOP30200 H) Cgroup by SOPTYPE, SOPNUMBEhaving COUNT(*) > 1
-- IVC Duplicatesselect DOCTYPE, INVCNMBR, COUNT(*) as [COUNT] from (select DOCTYPE, INVCNMBR from IVC10100 WUNION ALLselect DOCTYPE, INVCNMBR from IVC30101 H) Cgroup by DOCTYPE, INVCNMBRhaving COUNT(*) > 1
-- POP PO Duplicatesselect PONUMBER, COUNT(*) as [COUNT] from (select PONUMBER from POP10100 WUNION ALLselect PONUMBER from POP30100 H) Cgroup by PONUMBERhaving COUNT(*) > 1
-- POP Receivingss Duplicatesselect POPRCTNM, COUNT(*) as [COUNT] from (select POPRCTNM from POP10300 WUNION ALLselect POPRCTNM from POP30300 H) Cgroup by POPRCTNMhaving COUNT(*) > 1
-- RM Duplicatesselect RMDTYPAL, DOCNUMBR, COUNT(*) as [COUNT] from (select RMDTYPAL, RMDNUMWK as DOCNUMBR from RM10301 WUNION ALLselect RMDTYPAL, DOCNUMBR from RM10201 WUNION ALLselect RMDTYPAL, DOCNUMBR from RM20101 OUNION ALLselect RMDTYPAL, DOCNUMBR from RM30101 H) Cgroup by RMDTYPAL, DOCNUMBRhaving COUNT(*) > 1
-- PM Duplicatesselect DOCTYPE, VCHRNMBR, COUNT(*) as [COUNT] from (select DOCTYPE, VCHNUMWK as VCHRNMBR from PM10000 WUNION ALLselect DOCTYPE, VCHRNMBR from PM10300 PUNION ALLselect DOCTYPE, VCHRNMBR from PM10400 MUNION ALLselect DOCTYPE, VCHRNMBR from PM20000 OUNION ALLselect DOCTYPE, VCHRNMBR from PM30200 H) Cgroup by DOCTYPE, VCHRNMBRhaving COUNT(*) > 1
-- IV Duplicatesselect IVDOCTYP, DOCNUMBR, COUNT(*) as [COUNT] from (select IVDOCTYP, IVDOCNBR as DOCNUMBR from IV10000 WUNION ALLselect IVDOCTYP, DOCNUMBR from IV30200 H) Cgroup by IVDOCTYP, DOCNUMBRhaving COUNT(*) > 1
-- GL Duplicatesselect JRNENTRY, RCTRXSEQ, SEQNUMBR, ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, [YEAR], COUNT(*) as [COUNT] from (select WH.JRNENTRY, WH.RCTRXSEQ, WL.SQNCLINE as SEQNUMBR, WL.ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, WH.OPENYEAR as [YEAR] from GL10000 WH JOIN GL10001 WL ON WL.JRNENTRY = WH.JRNENTRYUNION ALLselect JRNENTRY, RCTRXSEQ, SEQNUMBR, ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, OPENYEAR as [YEAR] from GL20000 OUNION ALLselect JRNENTRY, RCTRXSEQ, SEQNUMBR, ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, HSTYEAR as [YEAR] from GL30000 H) Cgroup by JRNENTRY, RCTRXSEQ, SEQNUMBR, ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, [YEAR]having COUNT(*) > 1
Once the duplicate records have been identified, you will need to use SQL queries to check which of the transactions are the correct ones. Sometimes a duplicate transaction only has the key fields entered and the rest of the fields are blank and/or there are no line records associated with the header.
Once you have identified what is the incorrect data and have made a backup, you can remove the duplicate data using Transact-SQL commands.
The script is also available as an attachment at the bottom of this post.
You might also want to look at the Automated Solutions, the links are at the bottom of the General Articles & Links page.
Let me know if you find this useful.
Ref: Content Idea 101698
Other Microsoft Sites
I'm a Customer
I'm a Partner
Follow Microsoft Dynamics