SmartList Shows Duplicate Year End Journal Entry Distributions


David Meego - Click for blog homepageI have had two support cases with this issue recently.  So I thought I would post an explanation of the problem along with a script to fix it. The good news is that the underlying cause of the issue has been fixed in Microsoft Dynamics GP 2010 (v11.00).


The Issue


After running the end of fiscal/financial year process and posting the year end General Ledger journals.  You look at the "Balance Brought Forward" journal in SmartList and it appears to have the same amounts repeated a number of times for a particular account. Also some of the amounts for the same account are missing entirely.


For example:





















Account Number Debit Credit
123456-00 $0.00 $5,000.00
123456-00 $0.00 $5,000.00
123456-00 $0.00 $5,000.00

If using the Journal Entry Inquiry window to look at the same journal you will see the different amounts as expected.


For example:





















Account Number Debit Credit
123456-00 $0.00 $5,000.00
123456-00 $1,000.00 $0.00
123456-00 $500.00 $0.00

What is actually happening is that the amounts from the first line for an account number is being display for each of the distribution lines using the same account number.


Because the inquiry window is correct, it tells us that the actual data is not damaged and the issue is a display problem with SmartList.


The Cause


The issue is caused by the way SmartList works.  When you select a SmartList favorite and select your Search criteria, SmartList runs a SQL query to find all the records that match your criteria.  It then gets the key fields for that record and looks up the data for the fields to be displayed from the necessary table(s). Once it has the data, it adds a line to the right hand pane and then gets the key fields from the next record from the SQL query and looks up the data for the fields and so on.


This usually works fine for General Ledger Journal Entry transactions, except for the "Balance Brought Forward" transactions on a multi-currency system. The reason is that code that creates the BBF transaction was not populating the Sequence Number field.  On a non multi-currency system there is only ever one distribution in the BBF journal for each account number, so each line can be uniquely identified.


On a multi-currency system, you can have multiple distributions for different currencies against the same account number.  As the Sequence Number has not been populated, it will have a value of zero.  Now when SmartList looks up the data, it uses a Sequence Number value of 0 for each of the lines, which always returns the first of the distribution lines for that account number.  Hence we see what appears to be duplicated lines and the other lines are missing.


The Fix


The underlying cause of the issue is not a SmartList bug, but a problem with the Year End Closing code not populating the Sequence Number field for the transaction.  So if we run the script below after changing XXXX to the correct Journal Entry Number, we can renumber the Sequence Number for the Journal and resolve the issue.  We are using the increment of 16384 as that is the default increment value for line item sequence numbers in Dynamics GP.


Transact SQL Script

As mentioned earlier, this issue has been fixed for Dynamics GP 2010.  The "Balance Brought Forward" journal will now have the Sequence Number field incremented to create a unique key for each distribution.


Hope you find this useful.


David


// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

Comments (10)

  1. santoshsurti says:

    As usual excellent findings

  2. Siva says:

    Excellent piece of information David. Thanks a ton.

  3. David Musgrave says:

    Posting from Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing

    msdynamicstips.com/…/accounting-transactions-bbf-issue

  4. Ravishankar Polepalli says:

    Excellent David, This is really useful.

  5. Hi David,

    You say this problem was fixed in GP 2010 but I've got a client with the exact same problem on GP 2010 R2 (which the year end close was done on) but only on the BBF Lines; the P/L lines do have a sequence number (although it starts with 1 and increments by 1) and show on the SmartLsts correctly.

    Is there anything I should be cautious of in running the script in the referenced blog post? I haven't found anything after this post referencing the same issue on 2010.

    Thanks.

    Ian

  6. Sue Chaffee says:

    I am experiencing the same issue with GP2010 SP3.  Will the above mentioned script still work for this version?

  7. David Musgrave says:

    Hi Sue

    If the sequence numbers for the journal in question are missing…

    The script above will recreate them. It is generic and not version specific.

    David

  8. Terry Blaser says:

    This same issue can also occur in non-BBF transactions.  I have discovered an issue in the GL Clearning Entry functionality when MultiCurrency is enabled.  I am currently using GP2010 SP3.  When you clear a GL Account that has multiple currencies posted against it the posting routine creates duplicate values in the Sequence Number field.  This has the same effect on SmartList as the BBF Transactions, it shows duplicate values.  The resolution is also the same.  Find the JENumber and run David's script above.  To find the JENumber you can run the following simple Query:

    select JRNENTRY, SEQNUMBR from GL20000

    group by JRNENTRY, SEQNUMBR

    having COUNT(SEQNUMBR) > 1

    NOTE: This solution may NOT be Analytical Accounting compatible.  AA uses the Sequence Number as a part of its key, if you change the Sequence Number in the GL20000 you need to make sure you update the Sequence Number in the AA tables.

  9. IBEN says:

    Just to let you know Microsoft recently confirmed that this defect has in fact been re-introduced in GP2013. They will fix it for a later release.  In the meantime they provided the scripts below to resolve the issue against the company.

    NOTE: This solution may NOT be Analytical Accounting compatible

    ===========================================================

    To Resolve SmartList display issue for BBF Journal entries

    ===========================================================

    For Open year:

    select OPENYEAR, ACTINDX, JRNENTRY, RCTRXSEQ, SEQNUMBR from GL20000

    group by OPENYEAR,ACTINDX, JRNENTRY, RCTRXSEQ, SEQNUMBR having count(*)>1

    declare @HY smallint, @ACT int, @JRN int, @RTS numeric (19,5), @SQN int, @x int

    declare UpdateSeq cursor for

    select OPENYEAR, ACTINDX, JRNENTRY, RCTRXSEQ, SEQNUMBR from GL20000

    group by OPENYEAR,ACTINDX, JRNENTRY, RCTRXSEQ, SEQNUMBR having count(*)>1

    set nocount on

    open UpdateSeq

    fetch next from UpdateSeq into @HY, @ACT, @JRN, @RTS, @SQN

    while (@@fetch_status <> -1) begin

    set @x=0

    update GL20000 SET SEQNUMBR=@x, @x=@x+500 where JRNENTRY=@JRN and OPENYEAR=@HY and RCTRXSEQ=@RTS

    fetch next from UpdateSeq into @HY, @ACT, @JRN, @RTS, @SQN

    end

    deallocate UpdateSeq

    set nocount off

    For Historical year:

    select HSTYEAR, ACTINDX, JRNENTRY, RCTRXSEQ, SEQNUMBR from GL30000

    group by HSTYEAR,ACTINDX, JRNENTRY, RCTRXSEQ, SEQNUMBR having count(*)>1

    declare @HY smallint, @ACT int, @JRN int, @RTS numeric (19,5), @SQN int, @x

    int

    declare UpdateSeq cursor for

    select HSTYEAR, ACTINDX, JRNENTRY, RCTRXSEQ, SEQNUMBR from GL30000

    group by HSTYEAR,ACTINDX, JRNENTRY, RCTRXSEQ, SEQNUMBR having count(*)>1

    set nocount on

    open UpdateSeq

    fetch next from UpdateSeq into @HY, @ACT, @JRN, @RTS, @SQN

    while (@@fetch_status <> -1) begin

    set @x=0

    update GL30000 SET SEQNUMBR=@x, @x=@x+500 where JRNENTRY=@JRN and

    HSTYEAR=@HY and RCTRXSEQ=@RTS

    fetch next from UpdateSeq into @HY, @ACT, @JRN, @RTS, @SQN

    end

    deallocate UpdateSeq

    set nocount off

Skip to main content