Tuesday, June 25, 2013
Changing posting dates on payables and GL transactions for Mexican usetax laws for Dynamics GP
Posted by Steve Chapman
Most of the time when I write a blog post, it’s actually for myself. I figure if I need to do this same procedure in the future, I want some record of what I did so I don’t have to spend the time to develop the whole thing again. I’m guessing the majority of my readership (do I get to say I have a readership if the total number of people that follow me includes myself, my mom, and a few colleagues?) will go cross eyed when they see the title of this post and go on to more exciting things (like who won the Stanley cup last night. If you don’t know what the Stanley cup is your awesomeness just went down a whole 100 spots). The few of you that actually read this blog post from start to finish must wonder if I’ll ever get to changing dates on payables transactions and how it applies to Mexico.
Fine, I’ll start the explanation (and lose most of my readership here. It’s ok. I expect it. This post is really just for me anyways).
According to Mexican tax law, a company is liable or the difference between tax collected and tax paid (goods that are applicable incur a 16% tax). This tax is based on when the transaction was paid (not accrual based), so posting dates, especially on payments, are quite critical. (I’ve regurgitated this tax information from the client that has operations in Mexico. If I’m a little off it’s their fault.) The client uses Smartlist to track this information, and we are developing a report that will make this easier for them.
Here was their issue.
Today, when you were in the settings, I realized that, at some point, our posting had been changed to post to the GL based on posting date and not document date. If possible, I would like to have you do an update statement and update the posting dates to match the document (either invoice or payment). This would allow us to track the taxes properly.
- Run a backup of the company database
- Restore copy to test company
- UPDATE PM30200 SET PSTGDATE = DOCDATE
- UPDATE PM30300 SET GLPOSTDT = DOCDATE
- Update a set a.PSTGDATE = b.DOCDATE From PM30600 a
- inner join pm30200 b on a.vchrnmbr=b.vchrnmbr
- Update PM20000 set PSTGDATE = DOCDATE
- Update a set a.TRXDATE = b.DOCDATE From GL20000 a
- inner join pm30200 b on a.ORCTRNUM = b.vchrnmbr and a.orgntsrc = b.trxsorce
- Update PM10000 set PSTGDATE = DOCDATE
- Update a set a.PSTGDATE = b.DOCDATE From PM10100 a
- inner join pm10000 b on a.vchrnmbr=b.vchrnmbr and a.vendorid = b.vendorid
- Run check links on PM and GL tables
- Run GL Reconcile on the years
- Have user validate test company. Run tax reports (user was using smartlist reports they developed). Try posting. Probably should have had them close out year in test company
- Kick everyone out of live company and backup live company
- Run steps 3 to 12 again on live company
This client is just starting out. The actual amount of payables transactions is only like 300 transactions, and there was no data in the GL for historical years.
This is probably not a recommended procedure by Microsoft. If you attempt this, you are on your own. I won’t even try to help you. Do a backup and be smart in your testing.
Whenever I touch the data on the backend via SQL, it is always accompanied by much prayer and fasting. I recommend the same for you.
Whenever I read a blog post like this, I usually just read the introduction and the conclusion. I assume many of you just did the same. No worries. I don’t hold it against you. I didn’t even like writing out the steps I did, but figure if I ever have another client needing to adjust their payables and GL posting dates to abide by Mexican tax law, I better have somewhere to look.
Steve has over 15 years of experience in business systems consulting, and 20 years of experience in management accounting and finance with small and mid-sized businesses in the construction, distribution, service, and technology industries.