Error Creating Fiscal Calendar In Fabrikam Caused by Audit Trail Codes

Microsoft Dynamics GPI’ve been receiving an error in the Fabrikam Sample Company database when trying to create a new Financial Calendar for a while and finally had the time to track it down.

The error appears when you enter a new year and click the Calculate button:

Error when calculating a new year

Microsoft Dynamics GP
[Microsoft][SQL Server Native Client 11.0][SQL Server[Violation of PRIMARY KEY constraint 'PKSY40100'. Cannot insert duplicate key in object 'dbo.ST40100'. The duplicate key value is (0, 2021, 0, 2 ,General Entry ).

Clicking OK produces a second error message:

The problem is down to the data in the Fabrikam sample company database; the Audit Trail Codes window (Administration » Setup » Posting » Audit Trail Codes) shows that there are duplicate entries for a number of the items in Financial (and in Purchasing):

As you can see, General Entry (as well as other entries) is duplicated with a Prefix of both GLTRX and GLTRN; the X rows are for US English (which Fabrikam expects to be) and the N rows are for the installed English (UK) language. There is also a problem with PMCHK and PMCHQ.

The following script can be run against SY0100 to select all US/UK duplicated rows:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
Select UK and US entries from Audit Trail Codes (SY01000) SELECT * FROM SY01000 WHERE TRXSRCPX = 'PMCHK' OR TRXSRCPX = 'PMCHQ' OR TRXSRCPX LIKE '%X' OR TRXSRCPX LIKE '%N' ORDER BY TRXSRCPX

This script can be used to delete the US entries and allow a calendar to be created:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
Delete US entries from Audit Trail Codes (SY01000) DELETE FROM SY01000 WHERE SERIES = 2 AND TRXSRCPX LIKE '%X' GO DELETE FROM SY01000 WHERE SERIES = 4 AND TRXSRCPX = 'PMCHK' OR TRXSRCPX LIKE '%X' GO

This script has only ever been run against the Fabrikam sample company so I would not recommend running it against a production database.

What should we write about next?





Your Name (required) –
Your Email (required) –

Leave a Reply

Your email address will not be published. Required fields are marked *