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."
There is a duplicate document someplace in the Receivables tables. Typically it is a duplicate sequence number.
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
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
Article ID: 861084 - Last Review: 15 Jul 2011 - Revision: 1