Primary Key Violation on PKGL00201

This question has suggested answer(s)

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.

--
Enjoy the Life
+-+-+-+-+-+-+-+
Beat BUCHER
Dynamics GP 2010R2 Business Analyst
www.fti-ibis.com
+-+-+-+-+-+-+-+

All Replies
  • 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 ?

    --
    Enjoy the Life
    +-+-+-+-+-+-+-+
    Beat BUCHER
    Dynamics GP 2010R2 Business Analyst
    www.fti-ibis.com
    +-+-+-+-+-+-+-+