Records are missing from the Detail General Ledger report in Microsoft Dynamics SL

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

 



Potential Causes

1. If the records do not display but the detail is correct, this indicates that the transactions are not marked as Posted and should be. See .

2. If the account with missing detail is YTD Net Income or Retained Earnings, see .
3. The FiscYr field may not be populated in GLTRAN. See .

4. The CuryID field in GLTRAN may not be populated. See .

5. All Detail for a specific Account/Subaccount combination is not displayed on the report. See .

6. There is no CURYACCT record for the Acct, Sub, LedgerID, CpnyID, and FiscYr combination. See .

7. Version 2.06 ONLY - One or more indexes may have become corrupt. See .

8. A GLTRAN record may not exist for the data in question. See .

9. The Subaccount in question is not currently at an Active status. See .

Resolution 1257 - Run the .CRX files from INDEX.EXE using SQL Scope.

Note - This Resolution is for Scalable SQL databases; Solomon IV Release 2.06 and prior.

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

2. Identify the Database Table to be altered. For example, ARDOC, APTRAN, GLTRAN, etc.

3. Use My Computer or Windows Explorer to locate the INDEX.EXE file in either \SOL4 or \SOL4\DB.

4. Double click on INDEX.EXE to expand the .CRX files.

5. Log into the appropriate Application database using SQL Scope.

6. Select File, Open from the menu.

7. Select All Files from the File Type box.

8. Select the \SOL4 or \SOL4\DB directory from Step 3. The expanded files will have the extension .CRX.

9. Select the *.CRX for the appropriate *.DAT file (GLTRAN.CRX, GLTRAN.DAT, etc.)

10. Click OK. The SQL text area will display the statements.

11. Select Run, All from the menu. The Run All Statements Screen is displayed.

12. Enter 'Solomon' in the Output file box.

13. Deselect the Stop On Error box. Click OK to Begin Processing.

14. Exit SQL Scope and sign into Microsoft Dynamics SL or into Microsoft Business Solutions - Solomon.

Resolution 2417 - Set the Posted flag to 'P' in the GLTRAN table using a Query Tool (SQL Scope or Query Analyzer).

Note - In the GLTRAN table there is a flag indicating if a transaction has been posted. This flag should be set to 'P' when a batch has been posted.

Correction Preparation Steps

1. Verify that the batch record has been posted.

A. Turn on Initialize Mode from the Options menu.

B. In GL Journal Transactions (01.010.00), select the Module from which the Batch was entered.

C. Enter the Batch number.

D. Note the value in the Status field, it should be POSTED. If it is any other value DO NOT proceed with the steps below.

2. Locate the GLTRAN records for the Batch using the following statement replacing 999999 with the Batch number and XX with the two letter Module code in which the Batch was entered:

SELECT * from GLTRAN where BatNbr = '999999' and Module = 'XX';
(where 999999 = the batch number in question and XX = the module).

3. If the transactions selected are correct, check the field called Posted. The value should be P. If the value is U (Unposted) for the batch that displayed as Posted in step 1 above, execute the following statement:

UPDATE GLTRAN SET Posted = 'P' where BatNbr = '999999' and Module = 'XX';
(where 999999 = the batch number in question and XX = the module).

4. Re-run the Detail GL report to verify the information is displayed.

Resolution 4593 - Verify that the missing detail is from the YTD net income or retained earnings account.

1. Access GL Account History (01.300.00).

2. Press F3 in the Account field. Locate the account and verify the Type. If it is a Liability type, determine if it is the YTD net income or retained earnings account.

3. Pull up the account from which detail is missing on the report with the correct CuryID, Fiscal Year, and Ledger ID (MSSQL only).

4. Click on the Details button. If no detail displays, the entry was not made directly to this account, but rather to an expense or revenue account which will update the YTD Net income account during the posting process and then the retained earnings account during year-end closing.

Resolution 5658 - Populate the FiscYr field in GLTRAN.

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

2. Access the appropriate Application database using a Query Tool.

3. Run the following statement to verify the FiscYr field is not populated in GLTRAN:

SELECT * from GLTRAN where BatNbr = 'XXXXXX' and Module = 'GL';
('XXXXXX' = the Batch Number that does not display on the report; 'GL' is an example of the Module in which the batch was created).

4. Note the value in the FiscYr field. If it is blank, run the following statement after making a backup of the GLTRAN table:

UPDATE GLTRAN SET FiscYr = 'YYYY' where BatNbr = 'XXXXXX' and Module = 'GL';

5. Access Microsoft Dynamics SL and run the report.

Resolution 6670 - Verify that the detail exists in the GLTRAN table.

1. Access the appropriate application database using a Query Tool and execute the following statement:

SELECT * from GLTRAN where Acct = 'XXXXXX' and PerPost = 'YYYYPP'
(where XXXXXX = the Account Number that is missing detail and YYYYPP = year and period corresponding to the Period to Post.)

This statement will return detail stored in the GLTRAN table for the specified Account and Period to Post.

2. If detail appears to be missing, the table may be corrupt. Verify table integrity using Database Physical Integrity (95.500.00), available from the Utility menu. See .

3. Missing detail could also be a result of records lost during a data repair, restoring an earlier copy of this table, or modifications made with SQL Scope/Query Analyzer.

Resolution 12051 - Confirm the status of the Subaccount is Active in General Ledger Subaccount Maintenance (01.270.00).

1. Access GL Subaccount Maintenance and confirm the status of the subaccount.

2. If it is incorrectly set to Inactive, change the status to Active.

Resolution 17769 - Verify that an ACCTHIST record exists for that Account/Subaccount combination.

1. Access the appropriate Application database and execute the following statement:

SELECT * from ACCTHIST where Acct = 'xxxx' and Sub = 'yyyy' and FiscYr = '####'
(where xxxx = appropriate Account, yyyy = appropriate Subaccount and #### = appropriate Fiscal Year).

2. If no rows are returned, this indicates that there is no data to display on the report.

Resolution 20464 - Correct the CuryID field in the GLTRAN, ACCTHIST, and/or COMPANY tables using a Query Tool.

Note - Do NOT use this Resolution if Currency Manager is installed and more than one Currency is being used.

1. Access General Ledger GL Setup (01.950.00) - Currency Info and note the Currency ID.

2. Using a Query Tool, access the appropriate Application database.

3. Execute the following statement to identify the GLTRAN records where the CuryID is different than the Currency ID in GL Setup:

SELECT CuryID, * from GLTRAN where CuryID <> 'XXX'
(where XXX = the Currency ID from GL Setup).

4. If records are returned, execute the following statement to update the CuryID field in the GLTRAN table:

UPDATE GLTRAN SET CuryID = 'XXX' where CuryID <> 'XXX'
(where XXX = the Currency ID from GL Setup).

5. Execute the following statement to identify the ACCTHIST records where the CuryID is different than the Currency ID in GL Setup:

SELECT CuryID, * from ACCTHIST where CuryID <> 'XXX'
(where XXX = the Currency ID from GL Setup).

6. If records are returned, execute the following statement to update the CuryID field in the ACCTHIST table:

UPDATE ACCTHIST SET CuryID = 'XXX' where CuryID <> 'XXX'
(where XXX = the Currency ID from GL Setup).

7. Using a Query Tool, access the appropriate System database.

8. Execute the following statement to identify the COMPANY records where the BaseCuryID is different than the Currency ID in GL Setup:

SELECT BaseCuryID, * from COMPANY where BaseCuryID <> 'XXX'
(where XXX = the Currency ID from GL Setup).

9. If records are returned, execute the following statement to update the BaseCuryID field in the COMPANY table:

UPDATE COMPANY SET BaseCuryID = 'XXX' where BaseCuryID <> 'XXX'
(where XXX = the Currency ID from GL Setup).

Resolution 21300 - Compare the Account values in the ACCTHIST to the Account values in the CURYACCT table.

WARNING: Do not proceed with this Resolution if Currency Manager is being used. Contact Technical Support for assistance.

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

2. Open a Query Tool, log into the appropriate server, and choose the Application database.

3. Execute the following statements to compare the ACCTHIST and CURYACCT tables:

SELECT * from 'TTTTTT' where Acct = 'XXXX' and Sub = 'YYYYYY' and LedgerID = 'ZZZZZZZZZZ' and FiscYr = 'yyyy' and CpnyID = 'CCCC'
(replace TTTTTT with ACCTHIST or CURYACCT; XXXX = the appropriate Account number; YYYYYY = Subaccount number; ZZZZZZZZZZ = the affected Ledger ID; yyyy = the Fiscal Year; CCCC = the Company ID).

3. If the ACCTHIST record exists but the CURYACCT record does not, update the CURYACCT table to match ACCTHIST. See . Note - Step 2 in Resolution 11636 should be used if the problem is Cash Manager-related.

Resolution 11636 - Update the values stored in the CURYACCT table with the values stored in the ACCTHIST table using SQL Scope or Query Analyzer.

Warning Do not proceed with this resolution if Currency Manager is being used. Contact Technical Support for Microsoft Dynamics for assistance.

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

2. In SQL Scope or Query Analyzer, execute the following statement:

UPDATE C SET c.BegBal = a.BegBal, c.CuryBegBal = a.BegBal, c.CuryPtdBal00 = a.PtdBal00, c.CuryPtdBal01 = a.PtdBal01, c.CuryPtdBal02 = a.PtdBal02, c.CuryPtdBal03 = a.PtdBal03, c.CuryPtdBal04 = a.PtdBal04, c.CuryPtdBal05 = a.PtdBal05, c.CuryPtdBal06 = a.PtdBal06, c.CuryPtdBal07 = a.PtdBal07, c.CuryPtdBal08 = a.PtdBal08, c.CuryPtdBal09 = a.PtdBal09, c.CuryPtdBal10 = a.PtdBal10, c.CuryPtdBal11 = a.PtdBal11, c.CuryPtdBal12 = a.PtdBal12, c.CuryYtdBal00 = a.YtdBal00, c.CuryYtdBal01 = a.YtdBal01, c.CuryYtdBal02 = a.YtdBal02, c.CuryYtdBal03 = a.YtdBal03, c.CuryYtdBal04 = a.YtdBal04, c.CuryYtdBal05 = a.YtdBal05, c.CuryYtdBal06 = a.YtdBal06, c.CuryYtdBal07 = a.YtdBal07, c.CuryYtdBal08 = a.YtdBal08, c.CuryYtdBal09 = a.YtdBal09, c.CuryYtdBal10 = a.YtdBal10, c.CuryYtdBal11 = a.YtdBal11, c.CuryYtdBal12 = a.YtdBal12, c.PtdBal00 = a.PtdBal00, c.PtdBal01 = a.PtdBal01, c.PtdBal02 = a.PtdBal02, c.PtdBal03 = a.PtdBal03, c.PtdBal04 = a.PtdBal04, c.PtdBal05 = a.PtdBal05, c.PtdBal06 = a.PtdBal06, c.PtdBal07 = a.PtdBal07, c.PtdBal08 = a.PtdBal08, c.PtdBal09 = a.PtdBal09, c.PtdBal10 = a.PtdBal10, c.PtdBal11 = a.PtdBal11, c.PtdBal12 = a.PtdBal12, c.YtdBal00 = a.YtdBal00, c.YtdBal01 = a.YtdBal01, c.YtdBal02 = a.YtdBal02, c.YtdBal03 = a.YtdBal03, c.YtdBal04 = a.YtdBal04, c.YtdBal05 = a.YtdBal05, c.YtdBal06 = a.YtdBal06, c.YtdBal07 = a.YtdBal07, c.YtdBal08 = a.YtdBal08, c.YtdBal09 = a.YtdBal09, c.YtdBal10 = a.YtdBal10, c.YtdBal11 = a.YtdBal11, c.YtdBal12 = a.YtdBal12 from CURYACCT c, ACCTHIST a where c.CpnyID = a.CpnyID and c.LedgerID = a.LedgerID and c.Acct = a.Acct and c.Sub = a.Sub and c.FiscYr = a.FiscYr and c.BalanceType = a.BalanceType and c.BaseCuryID = a.CuryID

2. After executing the statement, sign into Microsoft Dynamics SL and access Cash Manager Bank Reconciliation (20.210.00). The GL Balance field should now contain the correct balance.

Properties

Article ID: 875140 - Last Review: July 25, 2011 - Revision: 2.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 KB875140

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