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
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,
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.
Hi,
I am having exactly the same issue here. Have you found out any solution for this?
Thanks,
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...
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
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (