143739
How to Fix Out of Balance Journal Entry After 6.2.0.20 Update
This document will go over the script that needs to be run after a customer has updated to SedonaOffice 6.2.0.20 and is getting an out of balance error when entering a journal entry template.
Overview
Bug scenario: Customer is trying to enter a Journal Entry Template and one or more of the GL Accounts in the template shows a $0.00 amount (Debt or Credit), and the Total of the Entry is balanced, but when they try to save the entry an error message pops up about NOT in balance, as shown below. Then the steps will need to be followed to fix the issue.
Steps:
- Run the following DevOPs60538 Script from SSMS on the SedonaOffice Server:
ALTER PROCEDURE [dbo].[Register_List_Balance_Check]
@register_numbers dbo.TableOfIntegers readonly,
@errorcode int OUTPUT,
@errordescription nvarchar(255) OUTPUT
AS
DECLARE @balance money, @register_id int, @type_cd char(1)
SET @balance = 0
SET @errorcode = 0
SET @errordescription = ''
if not exists (select top 1 1 from @register_numbers)
begin
return
end
select @balance =
sum(case
when r1.Amount is not null and r1.Amount <> 0 then r1.Amount * case when r1.Credit_or_Debit = 'C' then -1 else 1 end
when r2.Amount is not null and r2.Amount <> 0 then r2.Amount * case when r2.Credit_or_Debit = 'C' then -1 else 1 end
else 0
end)
from @register_numbers n
left join GL_Register r1 on r1.Register_Number = n.[Value]
left join GL_Register r2 on r2.Primary_Register_Number = n.[Value] and r2.Primary_Register_Number <> r2.Register_Number
option (table hint(r1, index(fk_register_number) forceseek), table hint(r2, index(fk_primary_register_number) forceseek))
if @balance = 0
begin
return
end
-- Register Entry is out of balance
if abs(@balance) < .1
begin
set @register_id = null
-- Check for small rounding errors due to multi-currency exchange rate rounding
select top 1 @register_id = r.Register_Id
from GL_Register r
join @register_numbers n on n.[Value] = r.Register_Number
where r.Currency_Id > 1
order by r.Register_Id desc
option (table hint(r, index(fk_register_number) forceseek))
if @register_id is null
begin
select top 1 @register_id = r.Register_Id
from GL_Register r
join @register_numbers n on n.[Value] = r.Primary_Register_Number
where r.Currency_Id > 1
order by r.Register_Id desc
option (table hint(r, index(fk_primary_register_number) forceseek))
end
if @register_id is not null
begin
SELECT @type_cd = ISNULL(Credit_Or_Debit, 'X') FROM GL_Register WHERE Register_Id = @register_id
IF @type_cd = 'C'
BEGIN
UPDATE GL_Register
SET Amount = Amount + @balance
WHERE Register_Id = @register_id
END
IF @type_cd = 'D'
BEGIN
UPDATE GL_Register
SET Amount = Amount - @balance
WHERE Register_Id = @register_id
END
RETURN
end
end
SET @errorcode = 1
SET @errordescription = 'The posted GL transaction is NOT in balance!'
RETURN
- Reenter Journal Entry Template to verify script updated as needed
