web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Error Running Reconcil...
Microsoft Dynamics GP (Archived)

Error Running Reconcile to GL: GP 10.0

(0) ShareShare
ReportReport
Posted on by K Day 7,365

I am trying to run a Reconcile to GL on the Receivables Management Subledger.  It pops up an error message  "Violation of Primary Key Constraint 'PK##2152147' Cannot Insert Duplicate Key in Object dbo.##2152147"  I click okay, it continues to run and then the excel sheet comes out wrong.  Everything is unmatched or potentially matched.  Any idea what is going on behind the scenes here?

 

*This post is locked for comments

I have the same question (0)
  • K Day Profile Picture
    K Day 7,365 on at
    Re: Error Running Reconcile to GL: GP 10.0

     I have traced this down by running (no pun intended) a SQL trace.  In the background it is creating temp tables and populating them with data.  They put Constraints on these temp tables when they create them.  Specifically TRXSOURCE, DOCNUMBR, and RMDTYPAL (which they hard code). 

    Transactions that were causing this to fail were where we had multiple RECV distribution lines.  Not sure exactly why they do that here, but there must be some reason.

    In this procedure, it tries to insert into a temp table some transaction from the RM10101 (distributions) table, and here we had one transaction with 3 RECV distributions.  So it tried to insert 

    (RMAPY00009150, 'PMT 06/18/10-1', 1) for the first distribution, then

    (RMAPY00009150, 'PMT 06/18/10-1', 1) for the second RECV distribution

    It hit the Constraint that those 3 fields were the primary keys, and threw the error "Can't insert Duplicates".  This just throws off the rest of the procedure in a way I din't feel like figuring out.  Basically that excel spreadsheet is useless now.

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at
    Re: Re: Error Running Reconcile to GL: GP 10.0

     Hi Kevin,

    I ran into this issue quite a lot of time when working for another company (quite each month end...). The solution lies in the line sequence of the entries, that somehow GP confuses itself when applying receivables against payables.

    Run the SQL script below :

    -- run this part below to find out duplicate sequence numbers

    select RMDTYPAL, DOCNUMBR, SEQNUMBR from RM10101
    group by RMDTYPAL, DOCNUMBR, SEQNUMBR
    having count(*) > 1
    order by DOCNUMBR

    This first part will return a list of document that have duplicate entries in the line sequence.  Copy that list into an Excel sheet or notepad to keep a trace and run the second part against each of the documents.

     -- then run for each document this to locate the detail of each sequence.

    select SEQNUMBR,* from RM10101 where DOCNUMBR = 'R-016728'


    /*
    use Enterprise manager to edit the sequence number and
    Replace wrong sequence with one that does not exist :
    16384
    32768
    49152
    65536
    81920
    98304
    114688
    131072
    147456
    163840
    180224
    196608
    */

     Once the duplicate lines are fixed, you can run the reconcile again and it will run without any error. Let me know if it does the job. Beat

  • K Day Profile Picture
    K Day 7,365 on at
    Re: Re: Re: Error Running Reconcile to GL: GP 10.0

     Thanks for that information.  We actually had the Sequence numbers crossed up in the GL at one point, but the RM tables look good.  I didn't get results back from your query.  It looks to be from what I mentioned above where there are multiple SALES distributions for the same transaction.  I'll probably just end up writing my own report to match the two up.  It always seems like the processes that GP runs to get information is way more complicated than it needs to be and that can cause these types of errors.  However, they need to be able to handle more situations than I would since it has to work across every company and install, so maybe it is necessary...

  • GrapeFruit Profile Picture
    GrapeFruit 1,240 on at
    Re: Re: Re: Error Running Reconcile to GL: GP 10.0

    Hi,

    I am having exactly the same issue here. Have you found out any solution for this?

    Thanks,

  • K Day Profile Picture
    K Day 7,365 on at
    Re: Re: Re: Error Running Reconcile to GL: GP 10.0

    No, I never found a solution.  I feel like it is a bug in the code and that if you have the situation that I had, where you have more than one SALES Distribution with the same account number, you won't be able to do anything about it through GP.  If I remember correctly, the error is a primary key violation on one of the temp tables GP Creates when running this.

    There is a product that I looked into called "The Closer."  They told me that they knew about that error and their product works just fine in these cases.  It apparently does Fixed Asset to GL as well.  Check that out.

  • Community Member Profile Picture
    Community Member on at
    Re: Re: Re: Error Running Reconcile to GL: GP 10.0

    Hi,

    For which service you are getting this error. I think this was an issue in GP 10 which was fixed in service pack.  Have you tried installing latest GP 10 service pack?

    Rubal,

    http://dynamicsgphelp.com/

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans