The General Ledger or Trial Balance is out of balance in Microsoft Dynamics SL

Article ID: 883358 - View products that this article applies to.
Expand all | Collapse all

 

Please use the following troubleshooting steps to identify the specific details of your out of balance situation.

Troubleshooting Steps

1. Confirm that the latest updates to the Detail General Ledger report have been applied.

2. Compare both formats of the Trial Balance report. The best way to compare these two formats is to review the Debit and Credit columns for discrepancies. To aid in this review, you can export the data in these reports to an Excel spreadsheet. The formats of the Trial Balance report pull data from Microsoft Dynamics SL as follows:

a. The Debit and Credit Totals format pulls the beginning and ending balances from the ACCTHIST table. The debit and credit columns sum the detail records in GLTRAN. If the net of the beginning balance, debits, and credits does not equal the printed ending balance, the detail in GLTRAN does not equal the ACCTHIST e
ending balance.

b. The Combined Totals format pulls all data from ACCTHIST. If the net of the beginning balance, debits, and credits does not equal the printed ending balance, t
then the data in ACCTHIST is inconsistent.

3. If both formats foot properly and agree to each other, compare either format to the Detail GL report to attempt to narrow down the cause of the out of balance situation.

4. Verify that the beginning balance for the current year equals the ending balance from the prior year for all accounts.

5. Verify that the net amount of the Income and Expense debits and credits equals the amount posted to the Net Income account.

6. Verify that the above Microsoft Dynamics SL reports agree to financial reports generated by FRx.

Potential Causes

1. GLTRAN records do not balance. See resolution 1325.

2. GLTRAN records are missing required fields or data is inconsistent (FiscYr, CuryID, CpnyID). See resolution 6980.

3. If the out of balance situation can be narrowed down to one batch or a series of batches, see resolution 6980.

4. Journal entries were made directly to the Net Income or Retained Earnings accounts. See resolution 1325.

5. The beginning balance for the current year does not equal the ending balance from the prior year. See resolution 538.

6. During posting the PtdBal (period to date balance) was updated but not the YtdBal (Year to date balance). See resolution 538.

7. The ACCTHIST record has a blank LastClosePerNbr field. See resolution 1151.

8. Records exist in the ACCTHIST table containing an invalid Currency ID value. See resolution 1507.

9. Beginning credit balances for Liability accounts were incorrectly entered as negative numbers. See resolution 4266.

10. The FRx or FRW financial report may be missing account records or contain invalid calculations. See resolution 1435 for FRx. See resolution 4516 for FRW.

Resolution 538 - Use Initialize Mode to correct the balances as necessary to match the detail in GL Account History (01.300).

Notes

1. The net results of the reports should be the same except in cases where account balances are entered or changed using Initialize Mode. When amounts are initialized, no GLTRANs are created.

2. The Print Debit and Credit totals report (01610B) selects from GLTRAN and WRKACCTHIST and would not contain initialized entries since Initialization does not create a GLTRAN record.

3. The Print Combined Totals report (01610A) reports on the balances stored in ACCTHIST as seen in the Account History screen and does not consider the detail in GLTRAN.

Correction Steps

1. Run all four selections of the General Ledger Integrity Check (01.990) process to determine the accounts/subaccounts for which the detail does not match the ACCTHIST balances. This will create an event log.

2. Enable Initialize Mode by selecting it in the Options menu. In GL Account History (01.300), enter the account, subaccount and fiscal year values of the account to be corrected.

3. Enter the appropriate Beginning Balance, PTD Balance, and/or YTD Balance as indicated by the event log created in Step 1.

Notes

1. It is necessary to initialize the account balance to equal the detail in GLTRAN, even if this amount is incorrect.

2. If the initialized balance is incorrect, adjusting entries can be made in General Ledger Journal Transactions (01.010) to adjust the balance. This approach preserves the audit trail and will keep reporting consistent.

3. Save the changes and exit Account History. Turn off Initialize Mode by selecting it again in the Options menu.

4. Make any adjusting entries, if needed, to the account balances in Journal Transactions.

Resolution 1151 - Complete the General Ledger Closing ( 01.560) process.

Correction Steps

1. Finish the closing process in GL Closing (01.560) for the General Ledger module.

2. If the GL Setup record was initialized incorrectly, see sub-resolution 1233.

Resolution 1325 - Enter a one-sided adjusting journal entry to correct the out of balance condition.

Correction Steps

1. On the Option menu, CLICK on Initialize Mode

2. Enter a batch in General Ledger Journal Transactions (01.010).

3. Select 'Adjustment' for the Batch Type.

4. On the Option menu, CLICK on Initialize Mode to disable it.

Resolution 1435 - Correct the accounts or formulas in the FRx Row or Column Format of the report.

Correction Steps

1. Access the FRx Row Format screen and select the Row Format that is being used for the report.

2. In the Link to General Ledger column, specify the account numbers that correspond to the database.

3. Verify the formulas and line numbers used in calculations.

4. If multiple databases are used and each has a different Charts of Accounts, refer to sub-resolution 1394 for information on copying Row and Column definitions.

Resolution 1507 - Delete duplicate ACCTHIST records.

Verification Steps

1. Sign into a query tool using the database that was accessed to print the report.

2. Run the following statement:

SELECT * from ACCTHIST where Acct = 'XXXXX' and Sub = 'SSSS' and FiscYr = 'YYYY' and CpnyID = ‘CCCCCC’ and LedgerID = ‘DDDDDD’;
(where XXXXXX = Account number, SSSS = Subaccount number, YYYY = the Fiscal Year, CCCCCC = Company ID, and DDDDDD = Ledger ID)

3. If two records display, this is the problem. Frequently, the CuryID field will be different between the two records.

(Note - One ACCTHIST record is usually for the correct Currency ID value. The second ACCTHIST record contains a blank CuryID value. The ACCTHIST record containing the correct Currency ID value should be the only ACCTHIST record found.)

Correction Steps

1. Make a BACKUP of the database that can be restored in case an undesired data loss occurs.

2. Note the dollar values in the PTD and YTD fields. If the record with the invalid Currency ID has a value that needs to be added into the account balance, use Initialize Mode and GL Account History (01.300) to adjust any balances.

3. Delete the invalid ACCTHIST record so that only the correct Currency ID record exists. Enter:

DELETE from ACCTHIST where CuryID < '0' and Acct = ' XXXXXX' and Sub = 'SSSS' and FiscYr = 'YYYY'

4. Sign into Microsoft Dynamics SL and run the Trial Balance and/or GL Detail reports.

Resolution 4266 - Confirm that the General Ledger Account Type determines the normal balance of an account.

Notes

1. The system determines what the normal balance is for an account based on the account type.

2. Account balances should be entered as positive amounts if the account has a normal balance. For example, if a liability account has a Credit balance of $200.00, then 200.00 will display as the balance in Account History. If the account has a Debit balance then -200.00 would display.

3. Journal transactions should always be entered as positive numbers in the appropriate debit or credit column.

4. When a negative number is encountered, the system assumes that the account balance is opposite of normal. For example, an asset account with a credit balance would display as negative in General Ledger Account History (01.300).

Resolution 4516 - Correct the row and column definitions in Financial Report Writer.

Correction Steps

1. Access FRW Row Definition Maintenance (22.020) and change the Row Definition to include any missing accounts or subaccounts. If necessary, remove any invalid characters.

2. Access FRW Column Definition Maintenance (22.030) and change the Column Definition to include any missing accounts or subaccounts. If necessary, remove any invalid characters.

3. Reprint the financial report from Financial Reports (22.010) to verify it is accurate.

Resolution 6980 - Verify that the GLTRAN.PerPost field is populated for the Batch.

Correction Steps

1. Make a BACKUP of the database that can be restored in case an undesired data loss occurs.

2. Run the following statement using a query tool:

SELECT * from GLTRAN where BatNbr = 'XXXXXX'
(where XXXXXX = the Batch number in question)

3. Verify the PerPost field is populated. If it is populated, do not continue. If the PerPost field is not populated, proceed to step 4.

4. ENSURE there is a backup of the GLTRAN table. If a backup does not exist, make one. If a backup cannot be made, DO NOT PROCEED TO STEP 5.

5. Update the PerPost field to the correct value using the following statement:

UPDATE GLTRAN SET PerPost = YYYYPP where BatNbr = 'XXXXXX' AND PerPost = ' '
(where YYYYPP equals the correct period to post for the Batch, and XXXXXX equals the Batch number in question. The period to post should be in YYYYPP format. For example, 11-2000 would be 200011.)

6. Run the report that previously showed the out of balance condition. The report should now be correct.

Sub-Resolution 1233 - Set GLSETUP.LastClosePerNbr to NULL and verify ACCTHIST.LastClosePerNbr matches.

Note - 1. If the GL Setup record was initialized incorrectly and closing has never been run in this database, set the GLSETUP.LastClosePerNbr to null.

Verification Steps

1. Log into the appropriate Application database using a query tool.

2. Execute the following statements:

SELECT LastClosePerNbr, PerNbr from GLSETUP SELECT LastClosePerNbr from ACCTHIST

3. Review the values in LastClosePerNbr. If incorrect, proceed to the Correction Steps.

Correction Steps

1. Make a BACKUP of the database that can be restored in case an undesired data loss occurs.

2. Execute the following statement if GLSETUP requires updating:

UPDATE GLSETUP SET LastClosePerNbr = NULL

3. Execute the following statement if ACCTHIST requires updating:

UPDATE ACCTHIST SET LastClosePerNbr = 'xxxxxx'
(where xxxxxx = matches the value in GLSETUP.LastClosePerNbr)

Sub-Resolution 1394 - Open an existing FRx Row or Column definition and use Save As to duplicate it.

Note - The same can be done with a report; this will create a new report that uses the same Row and Column definitions as the original.

Correction Steps

1. Select a Row or Column Definition from the list of values in Row Definition or Column Layouts.

2. With the definition open on the screen, select the File option, then Save As.

Standard Edition:

a. The default Specification Set is listed and cannot be changed.
b. Assign a new name and/or description to the definition.
c. Click on OK.
d. The new Row or Column definition can now be accessed, modified, and attached to a different report.
Advanced Edition:

a. The current Specification Set is listed and can be changed. (New Specification Sets can be added using the menu option Company then selecting Specification Sets.)
b. Assign a new name and/or description to the definition.
c. Click on OK.
d. The new Row or Column definition can now be accessed, modified, and attached to a different report.

Properties

Article ID: 883358 - Last Review: July 25, 2011 - Revision: 3.0
APPLIES TO
  • Microsoft Dynamics SL 2011
  • Microsoft Dynamics SL 7.0
  • Microsoft Dynamics SL 6.5, when used with:
    • General Ledger
Keywords: 
kbmbsmigrate kbsurveynew KB883358

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com