This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.
I 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
[code language=”sql”]
DECLARE @x int
SELECT @x=0
UPDATE GL20000 set SEQNUMBR=@x,@x=@x+16384
WHERE JRNENTRY=XXXX — Where XXXX is the Journal Entry
[/code]
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.)
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.
As usual excellent findings
Posting from DynamicAccount.net
http://msdynamicsgp.blogspot.com/2010/03/smartlist-shows-duplicate-year-end.html
Excellent piece of information David. Thanks a ton.
Posting from Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
msdynamicstips.com/…/accounting-transactions-bbf-issue
Excellent David, This is really useful.
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
I am experiencing the same issue with GP2010 SP3. Will the above mentioned script still work for this version?
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
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.
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