Deleting Duplicate Receivables Data

(0) Share

Report

Posted on by Community Member

Hello: I'm going to run scripts that will show duplicate receivables records that are causing the "duplicate key" error, upon running the Historical Aged Trial Balance. According to articles that I have read, such duplicate records should be deleted. But, what if the duplicate data is needed for accounting records? I mean should, say, the duplicate document number simply be "renamed" to a different number? In any case, if the data needs to be deleted, how should this be done and in what tables? I hope that this question makes sense. I'm simply trying to determine if duplicate data should truly be deleted and, more importantly, how. Thank you! John

*This post is locked for comments

I have the same question (0)

All responses (12)

Answers (1)

Verified answer

Mariano Gomez 26,225 on at

Like (0)

Report
RE: Deleting Duplicate Receivables Data

John,

I talk about some of this in my article Resolving SQL Duplicate Key Error Message When Executing RM Paid Transaction Removal. There is no valid reason in my mind, accounting or otherwise, for any such duplicates to be valid. However, once you identify the duplicates, you can effectively change one of the two document numbers should you want to isolate them for further research. If you determine you need to remove the duplicates, the easiest way is to retrieve the DEX_ROW_ID column value for the row to be removed then run the following query:

DELETE FROM RM30101 WHERE DEX_ROW_ID = <SomeValue>

Or if you determine that the open record is incorrect, then:

DELETE FROM RM20101 WHERE DEX_ROW_ID = <SomeValue>

My article explains how you determine which record needs to be removed

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Deleting Duplicate Receivables Data

Thank you, Mariano!

The duplicate document number that I found from running your scripts is in the RM30101 table (history) for one customer and in the RM20101 table (open) for a different customer.

The RM00401 table (keys) shows only one record and that is for the record appearing in the open table.

Otherwise, both records appear legitimate, in the sense that each has been applied to an invoice. And, I see no data corruption with either record.

I hesitate to simply delete a record in either table. So, what is the best way to handle this?

John

Was this reply helpful? Yes No
Mariano Gomez 26,225 on at

Like (0)

Report

RE: Deleting Duplicate Receivables Data

John,

That is the whole idea of a duplicate: they will both seem legit. What you need to do is find out with your customer whether the invoices in question are fully paid or are still open. Regardless, if you remove the one in history, you can always re-run paid transaction removal to get it there.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Deleting Duplicate Receivables Data

How about this?

In the test company in GP, I unapplied the payment from the open duplicated invoice for the purpose of trying to get rid of the document in the open table.

If I void this duplicated document number in GP, would that resolve this issue?

John

Was this reply helpful? Yes No
Suggested answer

Mariano Gomez 26,225 on at

Like (0)

Report

RE: Deleting Duplicate Receivables Data

John,

How about this? Un-apply the payment. Then, remove the record currently in history. Ensure the doc status in the RM Key table is set to 2 (open) and we go from there.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Deleting Duplicate Receivables Data

Hi Mariano:

The record in both tables are from two different customers.

Does that change your answer any?

John

Was this reply helpful? Yes No
Mariano Gomez 26,225 on at

Like (0)

Report

RE: Deleting Duplicate Receivables Data

Then how is that a duplicate document and why didn't you mentioned this from the get go? Your problem then has to be related to something else.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Deleting Duplicate Receivables Data

Hi Mariano:

Actually, I did mention it earlier, as follows:

"The duplicate document number that I found from running your scripts is in the RM30101 table (history) for one customer and in the RM20101 table (open) for a different customer." :)

I'll keep digging! In any case, thank you for your excellent service! You're great!

John

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Deleting Duplicate Receivables Data

Hi:

I want to provide the steps, for everyone's future reference.

First, in SQL, you delete the duplicate document number from the receivables keys table (RM00401), receivables distribution work table (RM10101), the receivables apply open table (RM20201), and the receivables open table (RM20101). Note that this is specifically for the open document – not for the historical document.

Next, run Check Links against the receivables open transaction and receivables historical transaction series of tables.

Continuing, run reconcile for Outstanding Document Amounts and for the open document’s Customer ID.

Although you would normally have the end user re-enter the document that was deleted from the open tables, I noticed when drilling back to this document in Receivables Transaction Inquiry by Customer that the duplicate document shown is actually the historical document! So, the end user obviously needs to be made aware of this.

Somehow, the historical document had gotten tied to both the customer in the historical tables and the customer in the open tables!

Thanks!

John

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

RE: Deleting Duplicate Receivables Data

John,

I still think you need to remove one of the records as Mariano suggests, but when you re-enter it add some kind of suffix to the record so that it doesn't try to create a duplicate.

Leslie

Was this reply helpful? Yes No