Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)
Search Form
Options
Tags
News

Dates of Interest

11-Jul-2008: Blog Created by David Musgrave.
03-Oct-2008: Syndicated to the Dynamics Communities.
10-Oct-2008: First Post by Scott Stephenson.
04-Nov-2008: First Post by Dave Dusek.
11-Nov-2008: First Post by Beth Gardner.
28-Nov-2008: First Post by Chris Roehrich.
30-Dec-2008: First Post by Patrick Roth.
24-Feb-2009: First Post by Greg Willson.
22-Apr-2009: First Post by David Clauson.
04-May-2009: First Post by Ryan Wigestrand.
19-Jun-2009: First Post by Dawn Langlie.
03-Jul-2009: First Post by Emily Halvorson.
23-Sep-2009: Created Twitter account with blog feed.
20-Nov-2009: First Post by Alice Newsam.
21-Apr-2010: First Post by Allan Cahill.
19-May-2010: First Post by Marian Verzosa.
24-May-2010: Blog Migrated to new Platform.
28-May-2010: Created Facebook account with blog feed.
15-Aug-2011: First Post by Development team.
05-Sep-2011: Created http://aka.ms/Dev4DynGP and http://aka.ms/SDT Shortened URLs.
15-Sep-2012: First Post by Aaron Donat.
27-Sep-2012: First Post by Jason Lech.

Social Networking & Syndication

Follow David Musgrave and the blog on:

Developing for Dynamics GP on Twitter

David Musgrave on LinkedIn

Developing for Dynamics GP on Facebook

This blog at Dynamics Communities

Shortened URLs

This Blog: http://aka.ms/Dev4DynGP
SDT Portal: http://aka.ms/SDT
Menus for VST: http://aka.ms/MVST

Disclaimer

This blog is provided "AS IS" with no warranties, and confers no rights.

The links in this blog may lead to third-party Web sites. Microsoft provides third-party resources to help you find customer service and/or technical support resources. Information at these sites may change without notice. Microsoft is not responsible for the content at any third-party Web sites and does not guarantee the accuracy of third-party information.
WorldMaps
WorldMaps Statistics since 24-Feb-2009:



Click for WorldMaps Stumbler
Translator Tool
Links
Contents
Newsgroups & Forums Links
Blog Links
Resources Links
Favourite Posts
Archives
Archives

Identifying Duplicate Transactions

MSDN Blogs > Developing for Dynamics GP > Identifying Duplicate Transactions

Identifying Duplicate Transactions

4 Dec 2008 10:30 PM
  • Comments 15

David MeegoFrom 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: DBMS: 2627, Microsoft Dynamics GP: 0.

Note: 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

-- SOP Duplicates
select SOPTYPE, SOPNUMBE, COUNT(*) as [COUNT] from
(
select SOPTYPE, SOPNUMBE from SOP10100 W
UNION ALL
select SOPTYPE, SOPNUMBE from SOP30200 H
) C
group by SOPTYPE, SOPNUMBE
having COUNT(*) > 1


-- IVC Duplicates
select DOCTYPE, INVCNMBR, COUNT(*) as [COUNT] from
(
select DOCTYPE, INVCNMBR from IVC10100 W
UNION ALL
select DOCTYPE, INVCNMBR from IVC30101 H
) C
group by DOCTYPE, INVCNMBR
having COUNT(*) > 1


-- POP PO Duplicates
select PONUMBER, COUNT(*) as [COUNT] from
(
select PONUMBER from POP10100 W
UNION ALL
select PONUMBER from POP30100 H
) C
group by PONUMBER
having COUNT(*) > 1


-- POP Receivingss Duplicates
select POPRCTNM, COUNT(*) as [COUNT] from
(
select POPRCTNM from POP10300 W
UNION ALL
select POPRCTNM from POP30300 H
) C
group by POPRCTNM
having COUNT(*) > 1


-- RM Duplicates
select RMDTYPAL, DOCNUMBR, COUNT(*) as [COUNT] from
(
select RMDTYPAL, RMDNUMWK as DOCNUMBR from RM10301 W
UNION ALL
select RMDTYPAL, DOCNUMBR from RM10201 W
UNION ALL
select RMDTYPAL, DOCNUMBR from RM20101 O
UNION ALL
select RMDTYPAL, DOCNUMBR from RM30101 H
) C
group by RMDTYPAL, DOCNUMBR
having COUNT(*) > 1


-- PM Duplicates
select DOCTYPE, VCHRNMBR, COUNT(*) as [COUNT] from
(
select DOCTYPE, VCHNUMWK as VCHRNMBR from PM10000 W
UNION ALL
select DOCTYPE, VCHRNMBR from PM10300 P
UNION ALL
select DOCTYPE, VCHRNMBR from PM10400 M
UNION ALL
select DOCTYPE, VCHRNMBR from PM20000 O
UNION ALL
select DOCTYPE, VCHRNMBR from PM30200 H
) C
group by DOCTYPE, VCHRNMBR
having COUNT(*) > 1


-- IV Duplicates
select IVDOCTYP, DOCNUMBR, COUNT(*) as [COUNT] from
(
select IVDOCTYP, IVDOCNBR as DOCNUMBR from IV10000 W
UNION ALL
select IVDOCTYP, DOCNUMBR from IV30200 H
) C
group by IVDOCTYP, DOCNUMBR
having COUNT(*) > 1


-- GL Duplicates
select 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.JRNENTRY
UNION ALL
select JRNENTRY, RCTRXSEQ, SEQNUMBR, ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, OPENYEAR as [YEAR] from GL20000 O
UNION ALL
select JRNENTRY, RCTRXSEQ, SEQNUMBR, ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, HSTYEAR as [YEAR] from GL30000 H
) C
group 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.

David

Attachment: SQL Duplicates Check.zip
, , , ,
Comments
  • 1 Dec 2008 8:59 PM

    Posting from the Dynamics GP Blogster

    http://dynamicsgpblogster.blogspot.com/2008/12/sql-mania-series-at-developing-for.html

  • 5 Dec 2008 6:34 PM

    Posting from Vaidy Mohan's Blog

    http://www.vaidy-dyngp.com/2008/12/davids-sql-series-identifying-duplicate.html

  • DynamicAccounting.net
    7 Dec 2008 2:00 PM

    I just wanted to take a second and highlight again David Musgrave's very useful SQL Scripts Series

  • Leslie Vail
    8 Dec 2008 3:29 AM

    As always an awesome addition to my bag of tricks.  David you rock!

  • Mohammad R. Daoud
    10 Dec 2008 7:54 PM

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

  • Microsoft Dynamics GP US Field Team blog
    10 Dec 2008 9:05 PM

    Based on the success of the "Resolving Security Issues in Dynamics GP" located here, Microsoft

  • Robert Cavill
    14 Jan 2009 10:46 PM

    There is a similar message you can get that refers to createTmpTable rather than createSqlTmpTable when dealing with financial series inquiry windows.

    However, the same applies in that the stored proc mentioned does not actually exist, but is pass thru sql code that is causing the error.

    "The stored procedure createTmpTable returned the following  results" DBMS: 12, Microsoft Dynamics GP 0."

    See KB 852594.

    Error 12 possibly means the table cannot be created or accessed within the tempdb database.

  • 19 Jan 2009 2:39 PM

    One of my users received a somewhat similar message when doing a Payables Transaction Inquiry in Dynamics GP 9 running on SQL Server 2000. The exact error was: "The stored procedure createSQLTmpTable returned the following results: DBMS: 12, Microsoft Dynamics GP: 0".

    Searches of the CustomerSource knowledge base, the Dynamics community forums and Google have been fruitless. I don't know what DBMS error 12 means. I couldn't even find a reference to that error in the Books Online for SQL Server 2000.

    Naively, we restarted Dynamics and tried the inquiry again. This time the inquiry worked. However, I would like to know more about what caused the error and how to permanently fix it or prevent it from happening. We tend to see it a few times a week, for a few users.

  • Developing for Dynamics GP
    19 Jan 2009 11:00 PM

    Many of inquiry windows in Microsoft Dynamics GP have been optimized to leverage SQL Server to increase

  • Developing for Dynamics GP
    20 Jan 2009 12:06 AM

    Many of inquiry windows in Microsoft Dynamics GP have been optimized to leverage SQL Server to increase

  • poWerRex
    3 Nov 2009 5:00 AM

    I found it very useful, specially in fixing those transactions with posting interruption problems.

  • Devesh
    21 Jul 2010 4:04 AM

    This is very useful for us David...Thanx a lot :)

  • Kristen
    11 Jan 2013 12:32 AM

    I also encounter that error now running on GP2010 only one customer cause this problem while the rest all ok. I also tried to run all most all of the sql from internet including the above, unfortunately i couldn't find any result. what i have notice is the error occur while it creating Stored procedures. please help me.

  • 11 Jan 2013 5:53 AM

    Kristen

    Sounds like you might have a related but different issue.

    You might want to ask on the forums or log a support case.

    David

  • 25 Feb 2013 6:29 PM

    Posting by Mark Polino at DynamicAccounting.net

    msdynamicsgp.blogspot.com/.../dynamics-gp-land-finding-duplicate-gl.html

Page 1 of 1 (15 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 7 and 7 and type the answer here:
  • Post