Archive for August, 2012
I had the opportunity to work on a case where the Year End Close report was being printed with the Debits not matching the Credits. No its not the JE created after a GL close, but the report that lists all P&L accounts and the retained earnings account.
Customer showed me how “before the upgrade” all YEC reports matched and “after the upgrade” they were not in balance.
I inherited this case from my friend Doug Pitcher and my friend Mike Feori.
The oddness of the case made it challenging to resolve, however I try as much as possible to adhere to the “Microsoft Dynamics 10 Rules” rule number 6 says: “Search for a workaround when final solution can’t be provided or is delayed”.
Here is the information I used to recreate the scenario and its improved with comments from Microsoft CTS.
The issue has been written up as Bug # 64711 “Divisional Account Segment information not updated in GL40000 table if you disable in the General Ledger Setup window”.
Here are the steps that can be taken to recreate the problem:
1. Go to the General Ledger Setup window (Financial Area Page under Setup).
2. Select the check box for ‘Close to Divisional Account Segments’ and select segment 1.
3. Verify that the DIVACTSG field has a value of 1 in the GL40000 table.
4. Go back to the General Ledger Setup window and uncheck the box for ‘Close to Divisional Account Segments’.
The DIVACTSG field would be set to a 0 in the GL40000 table.
The DIVACTSG field will still contain the segment selected when you originally did the setup.
Run an update statement to set the DIVACTSG field to 0.
Basically, if you select the check box to close to Divisional Account Segments in the General Ledger Setup window and you specify a segment the DIVACTSG field in the GL40000 table gets updated.
The issue is that when you unselect the option in the General Ledger Setup window, the DIVACTSG field does not get updated with a 0. It holds the value that was initially selected, the window greys out but the value is still a 1.
This is a problem when generating the Year End Closing report for companies that are not using Multicurrency. There are two different reports generated for the year end close-one if you are registered and using Multicurrency and the other is when you are not registered or using Multicurrency. The problem is that during the year end close process, the code breaks the profit and loss accounts into multiple retained earnings lines. Only the first RE line is written to the temp table because the rest are rejected due to key errors. The key on the table is composed of the ACTINDX, Ledger_ID and CURNCYID fields. The report pulls from this temp table and so it is only getting the first line that is inserted into the table. The tables are correct, but the report is wrong.
Remember I was able to reproduce all the steps many times while testing and trying to identify the issue because I had a pre close backup and a test environment to perform the close. Lucky me to always backup otherwise I would have been doomed by the following phrase “I have done millions of GL close procedures I don’t know what to do now….”
Have a great day!!!
Even customers are truly surprised when I show them how Integration Manager can pull information from one company or even better from another SQL server into Dynamics GP as a source.
Yes I know you are surprised too that I am telling you that your source can be a SQL Query Script…….
We have many ways of sorting data in integration manager, we even have filters and sort fields on the source definition, but nothing beats the customer frustration when they have a sorted file and Integration Manager scrambles the file, then open up the distributions for example on a JE import and see that the order is not even close to what the source file have.
One thing important is that there is a setting in the IntegrationManager.ini file. This setting is called UseOptimizedFilter normally I see this option with the value True.
When this option is enabled, Integration Manager uses a series of ninja techniques to optimize the data upload but that has an impact on the sort order.
If you want to have the distributions ordered or your transactions in the order you have them in your source file, close your Integration Manager, go to this file located on the Integration Manager code folder and set to false this setting.
Try your integration once again and voila!!!! Its just like magic, everything is the way you had it on your source file.
I am hoping to save techs from headaches here, and if this tip helps you, let us know, we want to know if our work can actually influence a better Dynamics community.