Tuesday, October 19, 2010

Trial Balance Detail report is out of balance in GL

One of the most common problem you come across while using Multicurrency module is the Detailed Trial Balance report in GL being out of balance.Microsoft has already published an automated solution for this issue in the partnersource/customersource.
Here is the excerpt from the KB document to overcome this issue.

Step 1. First, open the Trial Balance Report Options window for the Detailed Trial Balance Report by selecting the report option and click the Modify button.

a. In the Include section, make sure the checkbox for
Unit Accounts is not marked.

b. In the Include section, make sure the checkbox for
Inactive Accounts is marked.

c. Print the report again.


Step 2. If the Detailed Trail Balance Report is still unbalanced, then you will need to identify the GL transaction that is causing the difference. To do this, use one of the methods below:

Method #1: Use SQL scripts

a. Use the appropriate step below to open SQL Server Management Studio:

• If you use
SQL Server 2000, start SQL Query Analyzer. To do this, click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

• If you use
Microsoft SQL Server 2005, start SQL Server Management Studio. To do this, click Start, point to Programs, point to Microsoft SQL Server 2005, and then clickSQL Server Management Studio.

• If you use
Microsoft SQL Server 2008, start SQL Server Management Studio. To do this, click Start, point to Programs, point to Microsoft SQL Server 2008, and then clickSQL Server Management Studio.


b. Copy the below script in to a query window and execute against the company database to identify unbalanced journal entries.

select JRNENTRY, sum(DEBITAMT) - sum(CRDTAMNT) from GL20000, GL00100 where ACCTTYPE = 1 group by JRNENTRY having sum(DEBITAMT) - sum(CRDTAMNT) <> 0 

Method #2: Use the automated solution for "General Ledger Out of Balance" to find the unbalanced journal entries. This solution can be found using the link below to KB 935384.

KB 935384 - Automated Solutions that are available for the Financial series in Microsoft Dynamics GP
https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;EN-US;935384



Step 3. Once the unbalanced journal entry has been identified, you can correct the journal entry by setting up a dummy account in General Ledger to use as your offsetting account to key a journal entry. When finished, you can delete the side of the journal entry booked to the dummy account and then remove the dummy account to leave a one-sided entry to balance the Detailed Trial Balanced Report. To do this, follow the steps in KB 865712 on how to create a one-sided journal entry to balance out the difference.

KB 865712 - The beginning balance for the current year does not balance in the General Ledger in Microsoft Dynamics GP
https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;EN-US;865712



Displaying Hijri dates in Microsoft Dynamics GP

One of the key requirements through out GP users in the Middle East specially Saudi Arabia is to display date in Hijri format.
Just stumbled upon a great post by GP guru Mariano with regards to Hijri conversion customization.Awesome post and sample code.Check it out.

http://dynamicsgpblogster.blogspot.com/2009/02/displaying-hijri-dates-in-microsoft.html

Cheers..
Feradh