Showing posts from November, 2009

SQL - Retrieving the most recent receipt info for an item

It's been quite a while since I have posted a SQL script, and it's funny, because this is what I had in mind when I started out my blog. Shaun Childers posted a question on the Microsoft Dynamics GP public newsgroup, as follows:
"We are trying to create a script that will pull together our most recent
purchasing information only. The results should give all inventory items
with a qty on hand > 0, the current cost (we are average perpetual), the most
recent receipt number, the unit cost for that receipted item, the receipt date,
and the vendor name. I have tried to put this together, but have been
At first, this query may not seem to complex, but when you start to analyze the information being requested, it becomes apparent that using a standard set based query is not going to be as simple. Luckily enough, we can take advatage of the latest T-SQL enhancements to use ranks and partitions on sets to deliver the requested query, as follows:

-- Created by: Maria…

Happy Thanksgiving!

Folks, is Thanksgiving Day here in the United States and it is always an occasion to share with friends and loved ones. In particular, I would like to thank each and everyone of you for your readership and support shown over the last two years or so that my blog has been in circulation. The Dynamics GP Blogster is a success because of you! You certainly motivate me to continue writing and delivering the content you have come to rely on to get your job done or to find that unique aspect about the Microsoft Dynamics GP product that otherwise won't be available elsewhere.

This year I have heard comments like "I no longer go to CustomerSource/PartnerSource, I search your blog first!" and I really appreacite them from the bottom of my heart. In fact, keep your comments comming! For now, wherever you are in the world, Happy Thanksgiving!

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

The Technology Corner: Houston Neil weighs in on Microsoft Dynamics products

Fellow blogger Houston Neil addresses the proverbial question of what's the difference between the Microsoft Dynamics products. The truth is, to understand the functional and technological differences, you have to go back a few years in history to look at the Microsoft Dynamics products acquisition process, the failed Microsoft Project Green, and perhaps, even look at Oracle's attempt over the last 7 years to consolidate their products after their acquisition of PeopleSoft to understand that the ERP world is a very complex one with the customer base driving product decisions across the board.

I am a big Microsoft Dynamics GP proponent and defender, and in fact being an MVP allows me to carry out a lot of the product evangelism to customers and partners. Despite the product directions and strategies I have been witnessing over the past couple of years, I will be the first to say that there will be GP for years and years to come (see my article on Microsoft Dynamics GP Roadmap U…

IM - Integration Manager skipping records despite proper query relationships

Just when you thought there wasn't anything more to learn about Integration Manager something else comes along to demistify that theory. I have been involved in a JD Edwards on DB2 and AS/400 systems migration for almost 4 months now and last month I blogged about how Integration Manager can be a powerful tool in multiplatform systems integrations.

Part of disengaging JD Edwards is to write a series of integrations to existing systems running on the AS/400 platform. In the process, the client needed a simple to use tool which required limited programming and maintenance, hence the choice of IM. After unit testing the integrations, everything was A-Ok to begin with the week long systems testing, which would exhause the integrations while allowing the customer to get the overall "feel" for GP and how it would address the existing business processes.

One particular business process -- Expense Reimbursements -- required an integration to Payables Management. How difficult coul…

Dyn - Why is my inventory-related transaction posting so slow?

It is not uncommon for Microsoft Dynamics GP v9 and v10 users to report system sluggishness when posting inventory related transactions in Sales Order Processing, Purchase Order Processing or Inventory, especially if using the Average Perpetual valuation method.

The main reason why you may experience slow posting of inventory-related transactions is because Microsoft Dynamics GP uses an "inventory ripple" effect approach to recalculate the average cost of each inventory receipt layer due to inflow and outflow transactions in SOP, IV or POP. This is normal because in GP9.0 and GP10.0, the system is already observing the so-called "moving average" calculation. In addition, the system now "ripples" all item transactions. Hence, if transactions are backdated, each transaction would have to "ripple" through approximately n lines in the Inventory Purchase Receipts Work table (dbo.IV10200).

This new costing functionality in GP9 and GP10 also includes a n…

Microsoft Dynamics GP Technical Conference 2009 - Time to say goodbye

The Conference ended with David and I dragging feet, with little to no sleep, but with the satisfaction that we delivered to the best of our abilities. I woke up the following morning after spending part of the previous night packing my suitcase, only to come to the realization that I left my pair of Louis Vuitton glasses somewhere along (ouch!).

After having breakfast downstairs, we (David & I) hopped a ride to the Microsoft campus with Mark Rockwell (Rockton Software) and Jim Peliksa (Rockton Software). It was time to go get some goodies for the trip back home. After the campus store opened, we went downstairs and I got a remote controlled Warthog (Halo 3), a cool MS presenter's mouse, and an ultrasleek MS mouse for my wife. I also managed to pick up an embroided Bing hooddie sweater for my daughter.

We went back to the Oak room. David went off to a team meeting and came back after 1 hour. We then went to Scott Stephenson's office to get our goodbye picture taken. I have…

Microsoft Dynamics GP Technical Conference 2009 - Day 2

The good results of Day 1 and the hard work we put into preparing for the double whammy Troubleshooting session ahead of us had began to take their toll on Dave, Leslie, and I. We had a good breakfast at the hotel before being shuttled back to the Commons building. Upon arrival, Dave, Leslie and I went back to the Oak room to perform one last pass on the presentation, print out the cheat sheets and try to relax a bit.

We were on at 10:30 AM and the presentation went really well with minor unnoticeable glitches. It felt good, but not good enough. At the end of the first session of our Troubleshooting topic, and despite being extremely tired, Dave, Leslie and I returned to the round table to analyze the things that went wrong in our first session. After a few touch ups here and there, Dave and I went off to the table topics. I joined Nick Hoban who runs the Microsoft Dynamics Community website to discuss some improvements to the site.

We were back on stage at 3:30 PM for our second sess…

Microsoft Dynamics GP Technical Conference 2009 - Day 1

D-day, literally! If I had to qualify scary moments throughout my entire life, this certainly had to be at the top of the list. After completing the prep work for the Customizing session the day before, David and I attended the opening session the following morning which offered this cool Spinal Tap video attempting to explain why GP "11":


While at the opening session, reality began to set in and I had my first flash of panic -- 130 people will be attending our presentation! The next hour leading up to the presentation was absolutely worst than Dante's Inferno as I felt I was going through more than the 3 realms of death myself, with no hopes of being rescued by Virgil.

After the opening of our presentation, things began flowing and growing progressively smooth with time being our only enemy. We had a good number of questions throughout and received positive feedback by the end of the session. However, we went over the sche…

Microsoft Dynamics GP Technical Conference 2009 - Final prep work done!

Yesterday, after an afternoon catching up with Leslie and David, I was finally able to link up to the rest of the team staying at the Ramada, namely Mark Rockwell, Richard Whaley, and Darren Hunt from IBG. After weighing the possibility of leaving the hotel for dinner vs. eating at the restaurant by the lobby, we decided for the hotel's restaurant. This is the crew that went to dinner:

Left to Right: Mariano Gomez, Mark Rockwell, MVP Leslie Vail, Richard Whaley, David Musgrave, Darren Hunt

Today, the morning started with a breakfast at the hotel. The hotel shuttled us to the Microsoft campus. I have to admit, the new Vision building is amazing! The Horizon building also underwent some upgrades getting some new war rooms and training areas. Here are some pictures from the Vision building:

Panoramic view of the Vision (left) and Horizon (right) buildings

Vision building

A few in our group were attending eOne's eXtender classes, while David and I went to catch up with the Tools &…

Microsoft Dynamics GP Technical Conference 2009 - A star filled afternoon

Following my Journey to the Microsoft Dynamics GP Technical Conference 2009, I arrived in Minneapolis without a hitch and 20 minutes before the original schedule of 10:10 AM CST - hat tip goes out to Delta! We waited 2 minutes at a staging area on the runway before ground control authorized the pilots to taxi to the arrival gate. My arrival gate at Minneapolis International was F13 and I had to walk over to gate C2, approximately a good mile, adding to the walk I did earlier in the morning from the parking lot at Hartsfield-Jackson International. The following is the Lindbergh terminal layout at Minneapolis International:

I had a relatively short layover as my flight to Fargo was departing at 11:25 AM CST, but could not make the rest of the day without a coffee. So I went over to Starbucks and picked up my favorite: a double-tall mocha with wip. Upon boarding, I sat next to United States Air Force Lt. Sarah Reich who happened to be an Officer and Communications specialist en route to …

Microsoft Dynamics GP Technical Conference 2009 - The journey begins!

This morning I got up (with a bump on the side from my wife) at 5:00, a bit too early for my scheduled trip to the Microsoft Dynamics GP Technical Conference 2009, which will take me through the beautiful city of Minneapolis, where I will be transferring planes to get to Fargo. Given that I live in Atlanta, a major hub in the United States, it's just about right that you give yourself enough time to depart.

By 5:30 I was ready and decided to drive my car and park at the airport for the days I will be out... I was at the airport by 6:15 after what seemed like a very uneventful morning in the Atlanta traffic. In fact, there was nothing, no one on the road. Surprising? Very much so considering Atlanta's traffic is ranked 3rd in the US for congestion. the one thing I did not count on was the fact that the check in counter was located a good 600 meters away from the parking lot. May not seem as much, but try that when you have to stroll around a big bag and a carry on (laptop).


Lunching with Developing for Dynamics GP Team

The moment of truth has arrived... next week David Musgrave and I will be facing those of you attending the Microsoft Dynamics GP Technical Conference 2009 in Fargo, ND., to deliver some really exciting sessions on developing and troubleshooting Microsoft Dynamics GP.

To make matters even more interesting, Patrick Roth has announced that he will be hosting a lunch topic session to obtain your input on the Developing for Dynamics GP blog site. You may not know what you will get, but maybe having a chance to sit with Pat, David, and I should be enough motivation to get you started. We would definately love to steal more chairs and join a few more tables to accomodate all of you who decide to sit with us. I know I have a few comments for the site and a list of topics I would like to see them address more often. Why not come and introduce yourself, say what you like (or don't) from the site, and have some food in the process. You will sure get their attention by doing so and voice your…

"Unhandled Script Exception: Set Precision.." when selecting item in Item Maintenance window


The user was attempting to select an item in the Item Maintenance window and received the error:

Unhandled script exception:
set precision passed bad precision value -1.


The error only happened for a handful of items in a catalog of a few thousands.


Dynamics will attempt to set the decimal precision for the currency and quantity fields on the Item Maintenance window based on the number of decimal places for the functional currency (this is the default) or based on the number of decimal places used to define the item list price under the Item Currency Maintenance window; and in the case of the quantities, the number of decimal places for the quantities. To fix the issue, you will want to look at the columns DECPLQTY and DECPLCUR for any values at or below zero which would sure render an incorrect precision value when Dynamics GP attempts to set the value of the drop-down list on the screen. In this case, a zero val…

The Technology Corner - Microsoft SharePoint Server 2010

Folks, as you may (or may not) know Microsoft SharePoint 2010 is feature-complete with the beta release being shipped in the next coming days. Microsoft Office 2010 beta should be out almost simultaneously -- for my previous posts on Microsoft Office 2010 click here.

In addition, you may want to clap for this one: SharePoint 2010 will run on Windows 7 and Windows Vista SP2, at least the developer's version, eliminating the need for tedius virtual machine or virtual PC images for those of us who are developers or consultants trying to demonstrate things like Microsoft Business Portal and Workflow.

While most of us don't have beta code yet, it is safe to say you can get a head start on setting up a SharePoint 2010 development environment by reading this MSDN entry. Make sure you observe all the system requirements.

Here is what SharePoint Senior Director, Tom Rizzo has to say about the product.

You may also visit the SharePoint 2010 …