I develop a lot of custom Microsoft Dynamics GP integrations. A few are simple and straight forward. The majority have some quirks that require a few tricks or workarounds in .NET. And a few, well, they remind me that my imagination definitely has limits when it comes to envisioning complexities with certain businesses processes and related integrations.
I've been working on one particular integration over the course of a few months that has been vastly more challenging and complex than I could have ever possibly imagined. Believe it or not, it's a Purchasing Receipt import.
I know, I can hear it now: "Purchasing Receipt? You kidding? How hard could that possibly be?"
Let's start with the source data file. It's an Excel file, which is fine, but it is formatted as a report, with a report header in the first three rows (merged cells) and column headers on row 5. But there are only 7 column headers for 11 columns of data. Fine, I can read the file without headers, no problem.
And then the fields. There are only 3 fields related to the receipt transaction in GP: PO Number, Item Number, and Quantity.
Okay, so I have to lookup the PO number to find the vendor ID, no biggie. And sure, I can use the vendor ID and item number to lookup the vendor item number. Not so bad.
Except that some of the current vendor item numbers don't match the vendor item number on the PO. Sometimes the PO shows the vendor item number, sometimes it's the item number, and sometimes it's something else entirely--perhaps an older vendor item number. Oh, and sometimes even the item number won't match.
Okay....so I can add some logic that will figure out which PO line is being received by checking the vendor item number, then the item number, and then, believe it or not, the item description.
Whew, all done, right? Not so fast.
For some reason, many of the POs have the same item on multiple lines. Not 2 or 3 lines--we're talking 10 lines or more. So I can't just send the PO number and item number to eConnect--I have to know which PO line I'm receiving against. Oooookay, I now have a routine that can figure that out.
Almost done, right? Nope.
Did I mention that the receipt quantities sometimes exceed the PO quantities, and those excess quantities need to be received too, in some situations? Well, eConnect allows you to receive excess quantities if a PO line is not fully received, but if the line has already been fully received by a prior receipt, eConnect will refuse to receive additional quantities on that PO line. So if you have a PO line with a quantity of 20, and 20 have been received, but you need to receive 4 more, what do you do?
Well, I learned that eConnect will not allow you to adjust the quantity of a PO line that has been received. And it will also apparently not allow you to add a new line to an existing PO. So that just leaves...creating a new PO to receive the excess quantity.
You still with me?
So now that I've created the new PO, I need to pair it up with the receipt line. And then I need to write the new PO number back to the Excel file so that it can be traced or researched if necessary.
Oh, did I mention that there are POs that were created before the inventory item records were setup in GP? So not only do some PO lines have an "incorrect" item type status, but the corresponding vendor item record is not yet setup. So I need to "fix" those PO lines, and create a vendor item record.
And the list actually goes on and on from there--seriously, there really is alot more, but I assume you get the point. What at first seemed like a relatively simple endeavor ended up having requirements that have left me in amazement. Nearly every issue that could exist with the data and receipt process did exist.
I will no longer look at purchasing receipts the same ever again.
Which leads me to the general topic of designing and estimating custom eConnect integrations, which I'll discuss in another post: "The 90/10 Rule: 90 Percent Source Data, 10 Percent eConnect".
Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional in Los Angeles. He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.
http://www.precipioservices.com
This is certainly a nightmare, and I'm sure you're going to spend some nights dreaming about coding your integration!
ReplyDeleteIt seems to me that this isn't a technological problem you're fighting - it's a business process and control problem. A huge one!
Firstly, you haven't mentioned what kind of system are you integrating from or what its whole purpose is in the grand scheme of things. Is this some receivings spreadsheets that the shop guys use? Is it an external requisitioning system? Are you having to deal with inventory, or is it just services?
Your first problem is that the system you're integrating from doesn't have any controls in place (from the sounds of it, ANY controls). Have you asked yourself or your client why you want to circumvent the controls in the PO system to fit their external system? Have you asked why they want this information integrated into Purchase Orders instead of, say, Inventory and Payables separately (if inventory is affected) or just Payables?
Maybe the bigger problem is that they have the wrong system in place to begin with. There are requisitioning systems, for example, that already integrate directly with GP, have all the required controls in place, allow easy web-based entery for staff, and integrate into other, sophisticated systems.
Where is the data coming from that it is this unstructured? Do staff enter this information somewhere? Are the spreadsheets really what drives the whole process? If so, instead of fighting with programatically handling every exception they can throw at you, maybe you need to discuss some process and policies for employees to follow when creating the data in the first place.
I agree with the 90/10 rule, but I think it needs a bit of tweaking:
90% Process, 10% System.
Thanks for the comment TechnologyGuy.
ReplyDeleteYou are correct, the issues exist well before the receipt is imported, but very little is simple or clean cut with this client's environment. My post wasn't an attempt to provide the entire context of the client's environment, but just to highlight how seemingly simple transactions or processes can become very complex in the real world.
In this case, all of the data in GP is coming from multiple external systems. Items come from one system, Purchase Orders come from another system, and receipts come from multiple external partners, which are 3PL warehouses.
Hence the funky Excel report that I have to use for the receipts--it's what the 3PL warehouse is able to provide.
Overall, we're talking about millions of transactions a year that are being integrated into GP, so even if there is a small exception rate, that can mean thousands of transactions--which must be handled in an automated manner if at all possible, since the exception volume is too great to handle manually.
Unfortunately, it's not as simple as any single system or any single set of controls--it's multiple internal divisions, departments, and systems, along with multiple external trading partners. There are "explanations" for many of the issues I highlighted, but in the end, we still need to get the data into GP.
To have Dynamics GP integration requirements dictate how a massive company operates is, to use the trite analogy, like "the tail wagging the dog".
Like most companies, the accounting department does not drive the business. Sales, operations, and fulfillment take precedence, and accounting is left to assemble what is given to them.
In an ideal world, yes, things would be neat and clean, but when it comes to high volume inventory for a global corporation, things are rarely perfect.
Fortunately, between .NET and eConnect, I was able to get the job done--it just took a lot longer to address all of the details.
I am doing an internship right now. I have to do something "kind of" similar to what I am reading. I know this is really not a comment on the article in general but more of a question(as I cannot seem to find where to start researching this).
ReplyDeleteWe use GP2010 and Wennsoft service manager(an integrated add-on). Sales will generate a "request for service" which will contain customer information and service information for a job/services just sold.
This data is in excel format and is currently reentered by the service department for every single service ticket requested. This seems redundant as most of the information required for a service call(other than technicians being assigned and ticket number etc) already exist in electronic(excel) format in this "request for service".
I would like to find a way to have this information create a service call in GP so that a service person can recall the service call created and simply fill in the remainder of the ticket.
Is something like this possible...or where is a good source for researching how to accomplish such a task?