Have you ever had to update a table in GP and made a mistake. No! Never right? Then had to restore the database to bring it back before you ran the statement. Dang!! I forgot to make a backup! How many times have you had to restore before getting it right?
Well there is in SQL the Rollback and Commit Statements to help rollback your changes in case of the OOPS!! or in case you forgot to back-up the database. Then you Commit in case you are perfect. For a few seconds more, you can save a time and heartache.
– How to not commit changes in your database until you are sure it works and can roll back the changes without doing a restore of the database.
–Below is an example of an Update Set command that can be rolled back in case of OOPS!!
– Uses a Specific Database
– Step One
Select * RM00101 where ChekBKID=’Main’
–check the number of rows for transactions
– Step Two
Update RM00101 Set ChekBkId=’New’ where ChekBkId=’Main’
– check the number of rows for transactions to make sure it matches your first select statement
– you can run the select statement again by just highlighting it and make sure it is ’0′ rows and no data.
– Step Three or Four
–run this if everything works great
– run this if you did something wrong OOPS
We just received the delightful news that the GP year end updates are available for GP 2010 and GP 2013.
If you’re using GP 10 or an older version, there are no updates available from Microsoft for your versions. See Microsoft Support Lifecycle for Dynamics GP here.
You need to keep your business systems up to date. I talked with someone this week that is using GP 7.0. You’re just asking for future sorrows if you don’t keep current.
So here’s the information from Microsoft:
This update is all inclusive of prior updates for Microsoft Dynamics GP 2010 and GP 2013.
You can start to install and get the MSP via the link below for 2010 now:
Microsoft Dynamics GP 2010
Once you install the update, your version for GP 2010 will be 11.00.2292
(Also changes to Project Accounting, Fixed Assets, Human Resource, Field Service, Payroll Extensions, Advanced HR)
Microsoft Dynamics GP 2013
Once you install the update, your version for GP 2013 will be 12.00.1538
(Also changes to Project Accounting, Fixed Assets, Manufacturing, Human Resource, Field Service, Cashbook, Analytical Accounting,
Payroll Extensions, Advanced HR)
2014 US Payroll Round 1 Tax Update (due to release week of 12/16)
Changes so far: (circular E not final)
FICA Social Security limit, California, Kansas, Maryland, North Carolina
Please Note: The 2013 Year-End Update and 2014 Tax Updates will not be available for Microsoft Dynamics GP 10.0 and prior versions.
The Dynamics GP Job Cost module allows you to easily link various types of Dynamics GP transactions together so that you can track job or project revenues and costs.
This module is very easy to use and will meet the requirements of a lot of users.
There are various Job Statuses that can be used to manage the jobs. Specific Job Statuses dictate what transaction types can be linked to the jobs. The “Released” status allows the most flexibility, so you may want to use that as you gain some experience with the module.
Here is a table that lists the types of transactions/documents that can be linked to a “Released” job:
This video shows this function in action:
Upgrading an ERP system is not a smooth process. Invariably one will run into some snafu that requires unusually high levels of skill, experience, and persistence. It’s generally the most frustrating task an ERP consultant will experience. I personally refuse to do them.
On Friday night one of our newer consultants, Rob, was upgrading a Dynamics GP system to the current version. And guess what? Snafu. The upgrade protocol did not work in real life as it was supposed to.
In many consulting firms, Rob would have been left to wallow in despair, without a lifeline. One can imagine the wild frenzy of Google/Bing search requests being fired off.
But because Rob wisely chose to join Rose Business Solutions, he was not alone on that late Friday night. Within minutes of Rob’s request for assistance, he received advice and encouragement from two fellow consultants.
Here’s the timeline:
10:06 PM – Rob indicates he has a problem with the upgrade.
10:23 PM – Jamie responds with some advice and the wisdom that another day will be starting soon; full of opportunities for success.
11:11 PM – Giles concurs with some additional definition of why the problems may be occurring and restates the case for concluding activities for the day.
So why should you care about this? If you’re responsible for running an ERP system and are working with a Microsoft Dynamics partner, you want to work with one that has skill and experience. Of course. But you also want to work with a partner that supports its consultants and commits to your success. If a group of consultants don’t care that much about their fellow consultants, people whom they see every day, they’re not going to care that much about you.
I’ve been working at Rose Business Solutions for 12 years and this is not an isolated event.
Have you moved your ERP system to the cloud yet? Dynamics hosting frees you from many of the headaches associated with running your system on-premise. There are benefits associated with security, scalability, operating costs, and IT operations.
As it turns out, people generally understand some of these benefits but still have some concerns. To help address these concerns we created an interactive video that lets you explore the benefits and issues associated with Dynamics hosting, on your own terms.
We identified three roles that we see most often. These include the following perspectives:
- IT Director
- Finance & Accounting
- Dynamics Partner
Check out this new video. It may change the way you think of Dynamics hosting. Click on the link below and go to, “View Our Interactive Video” with Linda:
If you want to try out a hosted ERP system for your self you should check out the dynamics hosting trials available for Dynamics AX, NAV, and GP:
“Serial/Lot tracked item’s receipt layer adjusted unit cost does not equal unit cost” when running IV TB reset tool in Dynamics GP
I asked my friends at Microsoft how to resolve the issue and this is what I got back.
Make sure you have a good restorable backup before proceeding.
SET a.ADJUNITCOST = a.UNITCOST
FROM IV10200 a
JOIN IV00101 b ON
a.ITEMNMBR = b.ITEMNMBR
WHERE b.VCTNMTHD in (1,2)
and b.ITMTRKOP <> 1
and a.RCPTSOLD = 0
and a.ADJUNITCOST <> a.UNITCOST
Once this error is resolved, the Open Layer exists error should go away as well.
Increase the value of your Dynamics GP system with just a small investment of your time.
You can get some of the best knowledge about Dynamics GP right here. Our best posts about Dynamics GP distribution and manufacturing are included in these brief ebooklets. You can download them right from this blog post.
Our consultants work with many different organizations and have seen Dynamics GP used in hundreds of different ways. These ebooklets contain their insights on how best to set up and use Dynamics GP.
Get them here.
All we need is your email address to send the link. Try it now.
If you want to try out Dynamics GP free for 30 days, start a trial here:
Accountants have been producing financial reports with FRx for almost twenty years but according to Microsoft the end is near. FRx is not supported with Dyn GP 2013.
Wait a minute, FRx is not supported on a 64-bit operating system as well but we were able to get that working…….hmmm
I wonder. I installed FRx and created an ODBC connection to a GP 2013, SP1 install that was using SQL Server 2012 just to see what happened. I started FRx and darn, I could not connect to the SQL server because of the version of SQL. Hmmm… to my knowledge FRx does not check the version of SQL, it just uses the ODBC driver. I then created an ODBC driver using the SQL Server Native Client 10.0 (also known as the SQL 2008 driver) and low and behold, my trusty old FRx reports actually ran and looked correct!
Now, I haven’t tested every type of report and I am sure that eventually FRx will not work with some service pack or version of GP, but at least for now I have a little more time and so do all those faithful FRx fans.
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 at Rose?) will go cross eyed when they see the title of this post and go on to more exciting things (like who on 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.
To accomplish this I did the following procedure:
- 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.
Recently we had a client who accidentally changed their revision level on their Manufacturing BOM. They did not want to GP to show that they were on their 7th revision of the item because they were really on their 5th and just didn’t know how to use the BOM correctly in GP. They contacted us at Rose and asked us to fix it. Not knowing much about Manufacturing I searched all the usual areas for a way to change the revision level with no success. I don’t believe there is a way to “go back” within GP. I decided to fix on the back end. The two tables I used were BM010415 for BOM Revision and BM 010416 for BOM Revision History. On BM010415 I ran an UPDATE sql script on SSMS to change the REVISIONLEVEL_I column to the revision level I wanted, in my case this was 5. On BM010416 I ran a DELETE sql script to remove the two unwanted lines, again from the REVISIONLEVEL_I column, so in my case I deleted revision level 5 and 6 from the history table. This seemed to fix their issue and they have been happy ever since.
Here are the scripts I ran,
To update current table BM010415:
Select * from BM010415 where ITEMNMBR=’xxx’
Set REVISIONLEVEL_I=’xxx’ where REVISIONLEVEL_I=’xxx’
To update history table BM010416:
Select * from BM010416 where ITEMNMBR=’xxx’
Delete from BM010416 where REVISIONLEVEL_I=’xxx’
This next portion was graciously shared by Brenner Klenzman at Willoware.com
“The key on BM010415 (BOM Header) is:
*BOMCAT_I (which is labeled as BOM Type on the window)
BM010416 (BOM Header Hist) is:
BM010115 (BOM Lines) is:
And in BM010116 (BOM Lines Hist) is:
The script above is missing BM010116. There should also be a delete script as follows:
delete BM010116 where
PPN_I = ‘xxx’ and
BOMCAT_I = 1 and
BOMNAME_I = ”
Also, it should be restricting on BOMCAT_I and BOMNAME_I. For the client in question they probably had only BOM’s using BOMCAT_I = 1 (which is MFG BOM). However, for other people wishing to use the script above, they should consider further restricting it using BOMCAT_I and BOMNAME_I to ensure only the desired BOM is updated.”
Thanks Brenner for the input. We do not want anyone out there wrecking havoc on their data!
And because Brenner was gracious enough to share with the community I wanted to give Willoware a shameless plug. Please read below.
Willoware has module for GP called Stored Queries which allows you (the consultant) to create a query like the one above that can be executed by an end user from inside GP. The user is prompted to enter parameters (such as an Item Number), but they have no ability to edit the actual script. Take a look at Stored Queries in our Consulting Toolkit: http://willoware.com/products/consulting-toolkit/
(Brenner’s full comment is below)