Archive for the ‘Dynamics GP’ Category

Rollback your SQL script w/o restoring the database

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.


Example :

  – 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

Begin Tran

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



GP US Payroll Year End Update

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.


Dynamics GP Job Cost

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:

Job Cost


This video shows this function in action:


Why I Work at Rose

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.


“Serial/Lot tracked item’s receipt layer adjusted unit cost does not equal unit cost” when running IV TB reset tool in Dynamics GP

blog pictureI couldn’t find the answer to the above issue published in a tech doc anywhere so I thought I’d post it here so I could find it again.

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.
FROM IV10200 a
JOIN IV00101 b ON
and b.ITMTRKOP <> 1
and a.RCPTSOLD = 0

Once this error is resolved, the Open Layer exists error should go away as well. 

Brilliant Insights on Dynamics GP Distribution and Manufacturing

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.

Dynamics GP Distribution and Manufacturing

Dynamics GP Distribution and Manufacturing

Dynamics GP Distribution and Manufacturing

Dynamics GP Distribution and Manufacturing

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:

Dynamics GP Free Trial

Dynamics Great Plains 2013 finally means the death of FRx….or does it?

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.

Changing posting dates on payables and GL transactions for Mexican use tax laws for Dynamics GP

blog pictureMost 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:

  1. Run a backup of the company database
  2. Restore copy to test company
  5. Update a set a.PSTGDATE = b.DOCDATE From PM30600 a
    inner join pm30200 b on a.vchrnmbr=b.vchrnmbr
  6. Update PM20000 set PSTGDATE = DOCDATE
  7. Update a set a.TRXDATE = b.DOCDATE From GL20000 a
    inner join pm30200 b on a.ORCTRNUM = b.vchrnmbr and a.orgntsrc = b.trxsorce
  8. Update PM10000 set PSTGDATE = DOCDATE
  9. Update a set a.PSTGDATE = b.DOCDATE From PM10100 a
    inner join pm10000 b on a.vchrnmbr=b.vchrnmbr and a.vendorid = b.vendorid
  10. Run check links on PM and GL tables
  11. Run GL Reconcile on the years
  12. 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
  13. Kick everyone out of live company and backup live company
  14. 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.

Changing Revision Levels On Manufacturing BOM in Dynamics GP


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’
Update BM010415

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

“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

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:

(Brenner’s full comment is below)

Thanks again,


Could not e-mail status report to sender. Insufficient Memory in Dynamics GP

blog pictureHad a client receiving the above error while trying to email statements. The client had moved servers so it was working on the old server but the new server kept on saying Insufficient Memory. I finally opened up a case with Microsoft and somehow sneakily passed the issue off to a fellow consultant to follow up with. I actually thought MBS would know how to resolve the issue and I was leaving for the day and I wanted the issue resolved quickly. That was eighteen days ago and forty emails by the client, MBS support engineer, and the consultant I passed this off to (as well as several phone calls related to this case). Yes, I counted as I was curious.


Here is the final thing that resolved the issue:

I did some research on my end and noticed that the email functionality does not work as expected on a 64 bit install. Please install a 32 bit version of outlook and try to send the emails.


After installing the 32 bit version of Outlook the functionality worked as expected.

I’m not sure what the consultant I passed this off to thinks of me at the moment. He was a good sport and copied me on all the emails he sent back and forth to everyone. I’m pretty sure that was an attempt to make me feel guilty he was doing all the work on this case. I suppose I do feel guilty a bit. At least enough to blog about it.

Thanks Rob Kalproth (Rose superman) and Chai Polumati (Microsoft engineer).

Subscription Options:
Subscribe via RSS
Articles Categories