Symptoms
System Message 5 - 'Duplicate key value found in an existing record. The current database update operation is causing a row to contain a duplicate key value based on a unique index.' occurs in Payroll.
Causes
1. The error occurs releasing a Check batch in Payroll Release PR Batches (02.400.00). The PR batch is released in PRTRAN, PRDOC and GLTRAN but the BATCH has a Suspended Status. See Resolution 22627.
2. A deduction previously defined as a Local deduction is now defined as a State deduction beginning with 1/1/2000. Loading the tax tables will change the Deduction Type for this Deduction ID. However, the Closing process creates a new set of EARNDED records based on what existed in the prior year. EARNDED will continue to reflect the type as Local. When the first Payroll calculation is attempted, it will detect a mismatch between the DEDUCTION.DedType and the EARNDED.EarnDedType fields. See resolution 17010.
3. 2000 Payroll Tax tables have been loaded and attempting to calculate first Payroll for 2000. See Resolution 17010.
4. A FastForward conversion from Release 2.06 failed to create PRDOC records but did create PRTRAN records for an existing RefNbr that was also used in a new Payroll Check batch. See Resolution 20511.
Resolution
Resolution 17010 - Edit the Deduction Type in Payroll Deduction (02.290.00).
Notes
1. This should only be done with a complete database backup and with all other users logged out of Dynamics SL.
2. This change should only be made AFTER all year-end reports have been printed. Currently, if changes are made in Deduction (02.290.00), printing W-2s for a prior year will use the deduction information as it is (not how it was for the year being printed). If a deduction was a Local deduction in 1999 and then changed to be a State deduction in 2000, once the deduction has been updated for 2000, printing W-2s for 1999 will show those deduction amounts in the State box instead of the Local box.
Correction Steps
1. Access Payroll Deduction (02.290.00) and select the deduction that is problematic.
2. Change the Deduction Type to something other than what is displayed.
3. Save the changes.
4. Access Deduction and change the Deduction Type back to the correct Deduction Type.
5. Save the changes. This will update the DEDUCTION record and all related EARNDED records.
Resolution 20511 - Delete the converted PRTRAN records that have no existing PRDOC records.
Correction Steps
1. Make a BACKUP of the database that can be restored in case an undesired data loss occurs.
2. Using Query Analyzer, access the appropriate Application database and run the following statement to determine if RefNbr is used in PRTRAN records that were converted from 2.06 as well as in the Payroll Check batch that is experiencing the error. Make note of the BatNbr and CalYR values found.
SELECT BatNbr, RefNbr, CalYR, * from PRTRAN where RefNbr = 'XXXXXX' and TranType = 'CK'
(where XXXXXX = the Check Number generating the error)
3. Execute the following statement to find matching PRDOC records, and compare BatNbr and CalYR with what was noted in Step 1:
SELECT BatNbr, ChkNbr, CalYR, * from PRDOC where ChkNbr = 'XXXXXX' and DocType = 'CK'
(where XXXXXX = the Check Number generating the error)
4. Delete the PRTRAN records that have no associated PRDOC records:
DELETE from PRTRAN where BatNbr = 'BBBBBB' and RefNbr = 'XXXXXX' and CalYR = 'NNNN'
(where BBBBBB = the Batch Number, XXXXXX = the Check Number generating the error, and NNNN = the appropriate Calendar Year)
5. Exit Query Analyzer, and release the Payroll Check batch again.
Resolution 22627 - Correct the Payroll Check Batch Status to Unposted using Query Analyzer.
1. Make a BACKUP of the database that can be restored in case an undesired data loss occurs.
2. Using Query Analyzer, access the appropriate Application database and run the following statements to verify the Rlsed field is True (1) in the PRDOC and PRTRAN records for the Payroll Check batch:
SELECT * from PRDOC where BatNbr = 'XXXXXX' SELECT * from PRTRAN where BatNbr = 'XXXXXX'
(where XXXXXX = the batch number of the PR Check)
3. In Initialize Mode, go to General Ledger Journal Transactions (01.010.00), pull up the PR Check batch and verify the transactions are Balanced.
4. If the above 2 steps are OK, then proceed with the Correction Steps.
5. Using Query Analyzer, run the following statement to change the Batch Status to ' U ':
UPDATE BATCH SET Status = 'U' where Module = 'PR' and BatNbr = 'XXXXXX'
(where XXXXXX = the batch number of the PR Check)
This article was TechKnowledge Document ID:128082
