Our Finance people reported a problem yesterday when they wanted to open a new Fiscal Year (Microsoft Dynamics GP menu >> Tools >> Setup >> Company >> Fiscal Periods ).When you type a new year, you have to push the 'Calculate' button to populate the information for all the periods. While doing this, GP stoped with an error message :[Microsoft][SQL Native Client][SQL Server]Violation of PRIMARY KEY constraint 'PKGL00201'. Cannot insert duplicate key in object 'dbo.GL00201'
I figured that the primary key was a combination of 4 fields in the GL00201, but there is no explaination why this SP fails (UpdateDatesForBudget). I tried to run a DEX trace for the time the calculate process is runing, but no hint either on this side.. I found about 75 entries where the BUDGETID is blank, which is btw not very good, since it is part of the PK constraint.
On a test DB, I tried to remove the entries with the blank Budget ID, ran a check-links of the Budget Master and got no errors, but the problem still persist when trying to calculate the new periods...
Anyone a good suggestion where to look for ? Thanks for your help.
Update on this thread.
I found a posting somewhere else that suggested the PK violation could occur due to budget period entries that are not spanning properly over the FY.
• Try running the statement below, it should tell you the exact rows which cause this issue. On my system these seem to be caused by having a budget which spans financial years as the YEAR1 column in the GL00201 is not always correct. Once I had updated that (directly through SQL) it was fine.
SELECT A.BUDGETID , B.PERIODDT , A.PERIODID , A.ACTINDXFROM GL00201 A , SY40100 BWHERE FORIGIN = 1 AND A.YEAR1 = B.YEAR1 AND A.PERIODID = B.PERIODIDGROUP BY A.BUDGETID , B.PERIODDT , A.PERIODID , A.ACTINDXHAVING COUNT (*) > 1
That revealed a couple of entries that apparently where duplicates, but the real truth seems to rely on GL00201 entries were the PERIODID is not correct versus the PERIODDT (i.e. 0 instead of 1-12). I've found entries for the some month (like 2011-12-31 is associated with period 0, which doesn't look good).
Also the GL00201 table has several entries with the BUDGETID field being blank, which I don't think is good either.
Does anybody else had issues with such errors when importing Excel budgeting files in GP ?