GL Journal batch indicates out of balance by date

(0) Share

Report

Posted on by Frank Hamelly | MVP...

46,215 Super User 2024 Season 2

Am importing a GL journal with about 850 records in it, lines have multiple dates, all in the same period. I've set the Journal name to create one voucher only. The source file total debits=credits and in the resulting journal total debits=credits. No multicurrency involved, all USD importing into a USD company. However, I get these errors on a few of the lines when trying to post. Notice that in the error lines, there is no transaction amount referenced, only accounting & reporting amounts. However, in the information lines, transaction, accounting, and reporting amounts are referenced. I've tried creating a new journal name and using it to no avail. Haven't been able to find anything about this online. Has anyone seen this behavior? Why is it trying to validate on date when the journal contains only one voucher? Is this an erroneous message? Thanks in advance!

Categories:

Financial Management

Data Integration

All responses (9)

Answers (1)

Satish Panwar 14,647 on at

Like (0)

Report

RE: GL Journal batch indicates out of balance by date

Glad that it worked out finally for you.
Frank Hamelly | MVP... 46,215 Super User 2024 Season 2 on at

Like (0)

Report

RE: GL Journal batch indicates out of balance by date

Thank you Santish. I really appreciate your insights. Her's what I did to work around this.

1. This was a test of importing GL historical transactions as opposed to month end balances, so it was important to retain the transaction date on each line.

2. I added a clearing account to each transaction line so it would produce a separate balanced voucher for each line and I wouldn't have to worry about an unbalanced journal.

Thanks again, Frank
Verified answer

Satish Panwar 14,647 on at

Like (1)

Report

RE: GL Journal batch indicates out of balance by date

Thanks Frank for providing further details. As you notice that different dates on journal line (even if somehow posted) result in single date on voucher in GL and this is expected behavior. Single voucher should balance on any given date. Now that we know how system is working, you can do one of following:

1. Either change date on your journal lines to be same.

2. Adjust dates so that they are same on the voucher and don't use single voucher on all records.

Thanks,

Satish Panwar

Please help verify answer(s) if they guide you in right direction so other community members can also benefit from your question with verified answer.
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: GL Journal batch indicates out of balance by date

Hello Frank,

Can you try opening the journal lines in the Excel addin?

If you change something that should not impact the posting directly such as the description in one of the lines and try to publish back the change, do you see an error message?

Best regards,

Ludwig
Frank Hamelly | MVP... 46,215 Super User 2024 Season 2 on at

Like (0)

Report

RE: GL Journal batch indicates out of balance by date

Thanks Satish - I checked the other companies (all same environment) where the journals posted, and oddly enough, I see that even though the transaction dates in the imported journal vary from 5/1 to 5/31, the posted voucher dates are all 5/31 - which I guess is why it posted.

I don't have a rounding issue because I copied and pasted the values from one sheet to another and pasted them as text to avoid any rounding issues. I don't know what setting would cause the vouchers to post with a different date than appears in the journal and why this occurred in two legal entities and not in 3 others. The only difference between the entities is the 2 that posted are CAD accounting currency and the 3 that won't post are USD accounting currency. Odd, for sure.
Suggested answer

Satish Panwar 14,647 on at

Like (0)

Report

RE: GL Journal batch indicates out of balance by date

Thanks Frank but this doesn't seems right to me. Can you see what got posted when you click on "voucher" button (different dates or single date). We can wait for other community experts to weight on this. Posting like this can create reporting issues and they may never match.

Tried manually - doesn't work and can't post.

Tried upload and try to post - getting same error as you if dates won't match. Couldn't find any settings either.
Suggested answer

SouravDam 11,723 on at

Like (0)

Report

RE: GL Journal batch indicates out of balance by date

Hi Frank E. Hamelly,

Have you used the same template to upload the data in other environment where the transactions got posted as compared to the environment where you are facing issues?

Once I faced the same issue where all transactions were uploaded with base currency (no foreign currency was there!) and even though, same issue came up. The solution what I applied as below.

1) Found those vouchers for which this error is recorded.

2) Remove the amount in journal line and replaced it back. For example; there is a Debit amount of BHD 53.215 which was showing this error. I simply deleted this amount and write it back manually in Debit column in journal line. Then validated that journal and ultimately it got posted too.

Though it sounds little creepy but what I identified is, the problem was in the excel template which I used for uploading purpose. In Middle East; all companies follow 3 decimal value and based on that you need to update the same in system from 2 to 3 decimal which was fine. But in the excel template, in the DebitAmount column, I rounded the amount in 2 decimal and uploaded it. And this was causing the problem.

You can check the above step if this could solve for you.

Best regards,
Sourav Dam
Frank Hamelly | MVP... 46,215 Super User 2024 Season 2 on at

Like (1)

Report

RE: GL Journal batch indicates out of balance by date

Thanks for the quick response Satish but I posted the very same type of journal in a different legal entity in the same environment. Note that the voucher line dates vary, that there is one voucher number and this is posted.
Suggested answer

Satish Panwar 14,647 on at

Like (1)

Report

RE: GL Journal batch indicates out of balance by date

Hi Frank,

A voucher should only have single date when posted. Try this out..create a journal manually (provided your journal name is set to single voucher). Enter debit/credit transaction that balance and set date to 7/5/2019. add another transaction that balance in same journal with same voucher, set date to 7/6/2019. Post it, system is only going to retain one date, it will update your journal lines and then voucher will show single date. In your case, there may be an issue because your uploaded these transactions. however even if you post, it's not going to give you what you are expecting by uploading different dates. Here are the options that you can use - Either use single date and then upload again OR if you want to use different dates then don't use single voucher.

Thanks,

Satish Panwar

Please help verify answer(s) if they guide you in right direction so other community members can also benefit from your question with verified answer.