You’re offline. This is a read only version of the page.
Announcements
902
Client set up fiscal year to end Feb 2014. The fiscal year actually ends Dec 2013.
Transactions have been entered and posted to the incorrect fiscal periods.
I would appreciate input on how to fix this?
*This post is locked for comments
That is a lot of transactions to deal with. Too bad it took 9 months to discover the error. If I were in your position I would strongly consider an action that only addresses the GL implications of the problem and not worry about the other modules (with the possible exception of Cash Manager). Posting period is really just a GL critical item. If the AP, AR, PR, etc transactions have the wrong posting period but you fix the GL side then the only issue is that the various sub-module reports (like AP Transactions, PR Transactions) will still reflect the incorrect posting period but do not affect the GL (that you will have corrected. Other items, such as W2 information and 1099 information are not posting period driven so they will still be accurate.
The fact that you said this is 9 months implies that all the transactions are within the same fiscal year (though be it that fiscal year is 2 months off and has the wrong year id). It would be possible to create a query to "slide" the accthist values back 2 positions and to change all the GLTran posting periods back 2 (and, edit GLSETUP for the correct fiscal year). This would make the GL and financial statement properly reflect the fiscal year activity.
You can take the route you indicated in your last response and use the old database for detail and the new database for going forward. You loose some of the direct drilldown capabilities (without switching databases) but that may not be all that important to the client.
The risk in modifying the GL, and only the GL, is one; getting it done correctly and two; forgetting down the road that the first nine months will off by two months. And, of course, the fact that you would be moving backwards, 2 periods would be doubled up in the sub-modules (not in the GL). This last fact might be the most important factor in deciding what to do.
I mentioned Cash Manager as one sub-module that might have to be corrected because it ties cash activity from the various sub-modules to the GL and that has to balance in order to move on to the next month in Cash Manager. While most of Cash Manager is transaction date driven, it does still have to reconcile to the GL. I would have to think about how my idea would affect Cash Manager.
Hi Rick,
Thank you again for valuable input.
After analysing the volume of transactions we are also thinking that it may be better to redo the transactions in a new DB.
The distinct batches, spread over 9 months are
module Batnbr
AP 615
AR 16
BI 17
CA 60
CM 1
GL 75
PR 41
TE 15
TM 11
We will investigate importing the AP and GL transactions and just recapture the rest.
In order to pull this off, you need an understanding of each module as far as where the posting period lives. You are using some modules that I have not dealt with (such as TM, TE and PA) so I do not know all the places a posting period might exist in the various tables for those modules but Solomon was quite good with the table names and the field names such that making educated guesses as to what to look at would be possible.
Sometimes there are tables that may not be quite so obvious or fields in those tables. For example, in AR one can pretty easily assume that the posting period will be found in ARDoc and ARTran but you would also need to know it is found in ARAdjust as well. Additionally, you would need to know that ARDoc also has a field called PerClosed and that ARadjust has a field called PerAppl that would be affected. The nice thing is that, once you figure the parts out in one module, the next module that does similar things will likely be similar. For example, AP has its APDoc and APTran but also the APAdjust table.
The GL side also has a GLTran table but the main table is going to be AcctHist and you would need to understand its layout in order to take this task on as well.
You indicated that they stated the fiscal year ended in Feb 2014 when it actually ends in Dec 2013 so they are a true calendar fiscal year. What you did not indicate is what fiscal year and period they did their first postings to. I am assuming that this is a recent implementation so they are not that many months into posting. however, that could be a bad assumption. How many unique posting periods have they actually posted to so far?
I am getting concerned that this may be a rather significant undertaking yet it also sounds like there is no choice (short of just starting over). If you were to take on this task it would involve significant down time for the client while all the queries are created and ran and you would definitely want to have a verified backup before starting.
Still, this would be doable. I did this with one of my clients but it was limited to 3 months of postings and only the core financial modules (GL,AR,AP and PR) and even that involved most of one day.
I think I would like to hear back from you on how many period have already been posted to before going much further. I suspect that the Dynamics SL people would be nervous about anyone attempting to do this and that is why Carolyn basically said there is no way to do this but we have to remember that it is just data with some relationships. So, if you can have a firm grip on the data tables involved, it can be done. There is nothing magical about a posting period or a fiscal year. Most tables just reflect the posting period. It is the GL Account history table that is the most sensitive not only because it holds data for the full fiscal year but also because it handles the assets, liability and equity carryover into the next fiscal year and it handles the transfer of income and expense into retained earnings between fiscal years. If this client has not yet truly crossed a fiscal year then the process will be significantly easier.
Hi Rick,
thank you for responding. Answers to your questions below.
1) What version of Dynamics SL are you running? SL 2011 would make this a bit easier.
SL 7 SP3
2) What modules are involved that have posted (not just released but posted batches?
Modules from gltran
AP,AR,BI,CA,CM,GL,PA,PO,PR,TE,TM
3) Is it sufficient to just fix the GL side or do you think you need to fix all of the modules involved as well?
No. Need to fix all
4) Are you comfortable running SQL queries>
100%
While Carolyn is basically correct, I can think of a way to do this but it will require quite a bit of work and some decent SQL knowledge. Before covering how you could pull this off, I have a couple of questions.
1) What version of Dynamics SL are you running? SL 2011 would make this a bit easier.
2) What modules are involved that have posted (not just released but posted batches?
3) Is it sufficient to just fix the GL side or do you think you need to fix all of the modules involved as well?
4) Are you comfortable running SQL queries>
Hi Coen,
There isn't a good way to change it now. Especially since all the data has a period to post of 2014 rather than 2013. We don't have a tool to correct the period to post and fiscal year errors. Let me know how else we can assist.
#1
André Arnaud de Cal...
294,060
Super User 2025 Season 1
#2
Martin Dráb
232,858
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
