Error "Violation of PRIMARY KEY Constraint PKRM30301 Cannot Insert Duplicate Key" when running Paid Transaction Removal in Receivables Management

Issue

Error - "[Microsoft] [ODBC SQL Server Driver] [SQL Server] Violation of PRIMARY KEY constraint PKRM30301 Cannot Insert duplicate key in object RM30301" occurs when running Paid Transaction Removal in Receivables Management.


Note - When you hit OK on this dialog box, an information dialog box appears stating "The stored procedure in Paid Transaction Removal returned the following results: DBMS:2627, eEnterprise 0."


Potential Cause

There is a duplicate document someplace in the Receivables tables. Typically it is a duplicate sequence number.


Resolution

1. Run the following against the Company database to find the records that are causing the error on the RM30301:


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


2. Using the information returned from the previous query, run the following to view data for a specific Document Number:


select SEQNUMBR as NUMBR, * from RM10101 where DOCNUMBR = 'enter the Document Number from step 1' order by NUMBR


3. Update the SEQNUMBR field so there is not a duplicate. Use the key provided below. If you have 2 records with a SEQNUMBR of 16384, you need to change one of them to 32768, if you have 2 records with 65536 you need to update one to 81920, etc. Each line of a document should have its own unique seqnumbr in multiples of 16384.


update RM10101 set SEQNUMBR = XXX where DEX_ROW_ID = XX


SEQNUMBR

-----------

16384

32768

49152

65536

81920

98304


If the first script does not return results, try these:


select * from RM10101 w, RM30301 h where

w.RMDTYPAL = h.RMDTYPAL and

w.DOCNUMBR = h.DOCNUMBR and

w.SEQNUMBR = h.SEQNUMBR


select a.TRXSORCE,a.RMDTYPAL,a.DOCNUMBR from RM20101 a, RM30301 b where

a.RMDTYPAL = b.RMDTYPAL and

a.DOCNUMBR = b.DOCNUMBR

This article was TechKnowledge Document ID:32359