Dynamics Community Forum Thread Details

Primary Key Violation on PKGL00201

Hi Everyone,

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.ACTINDX
FROM GL00201 A ,
       SY40100 B
WHERE FORIGIN = 1
       AND A.YEAR1 = B.YEAR1
       AND A.PERIODID = B.PERIODID
GROUP BY A.BUDGETID ,
       B.PERIODDT ,
       A.PERIODID ,
       A.ACTINDX
HAVING 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 ?

Quick Links

Leaderboard