Violation of PRIMARY KEY constraint ‘PKCN04001’. Cannot insert duplicate key in object ‘dbo.CN04001’.

There is a known issue where Professional Services Tools Library (PSTL) isn’t trapping for tables with similar key structures to what the CN04001 table has. The table contains the DSO information processed for individual customers. The calculate DSO process is what populates the CN04001 table. It should be running once per day when logging into Dynamics GP and there isn’t a record for the customer on that day. There are a couple options to resolve the issue.

NOTE: The PSTL customer combiner functionality was incorporated into Dynamics GP. While it is being accessed differently, the code running is still the same. The steps to resolve the issue are not changed.

It is recommended to take a backup of the live company database and test the steps in a test company, so you know what to expect before implementing the steps in the live company database. A link to the Microsoft KB article containing steps to create a test company can be found below.

https://support.microsoft.com/en-us/help/871973/set-up-a-test-company-that-has-a-copy-of-live-company-data-for-microso

Option #1

Delete all data in the CN04001 table using a SQL script.

NOTE: This will remove all the DSO values from Collections Management, and the data can only be recovered using a backup.

1. Login to GP with one user.
2. Run the following script against the affected company database in Microsoft SQL Server Management Studio.

DELETE CN04001

3. Run the customer combiner for the customers encountering the issue.
4. Logout of GP.
5. Login to GP.
6. Verify the DSO values.
   1. Open the Collections Management Main window.
      1. Sales > Transactions > Collection Main
   2. Enter or select a customer ID.
   3. Click the DSO link > Details.

Option #2

Remove the duplicate item(s) using a SQL script.  The entire table doesn’t need to be deleted to bypass the issue.  The records for one or both of the two customers being combined from the CN04001 table could be deleted instead.  The values wouldn’t be accurate for both customers as of a historical date, for they weren’t the same customer in the past.  However, the records for one of the two customers being combined could remain.

1. While all users are logged out of GP, login to GP with one user.
2. Run the following script against the affected company database in Microsoft SQL Server Management Studio for one or both of the affected customers.

DELETE CN04001 WHERE CUSTNMBR = 'ABC123'

1. 1. The ABC123 will need to be changed to the customer ID for one of the affected customers.
   2. The script can be run a second time for the other customer ID if you don’t want to maintain the DSO history for either of the customers being combined.

3. Run the customer combiner for the customers encountering the issue.
4. Logout of GP.
5. Login to GP.
6. Verify the DSO values.
   1. Open the Collections Management Main window.
      1. Sales > Transactions > Collection Main
   2. Enter or select a customer ID.
   3. Click the DSO link > Details.

Prevention:

The issue can be prevented by not having the DSO values calculated per customer.  Then, the combiner wouldn’t find records in the CN04001 table during the process.  However, turning off the DSO calculation would only prevent the automated calculation from processing.  The DSO values could be calculated manually by any user clicking the refresh button in the Collections Management Main window or by calculating the DSO values per customer within the Collections Management Days Sales Outstanding window (Sales > Inquiry > Days Sales Outstanding).