GP-Dynamics – Custom Programming a TSQL Collection

I stumbled upon this a new Dynamics GP site tonight, gp-dynamics.com. This site lists nearly one hundred useful SQL scripts to use when working with Microsoft Dynamics GP ERP system. I glanced down the list of scripts and dipped into some of them and it all looked like a good resource.

The site is orientated towards selling a technical manual on Dynamics GP, but this section is free. Of the scripts I looked at, some of them were naive, but not dangerous. Certainly enough to get out of jail free, I certainly would have relished such a resource back in 2001 when I started with GP, as it happens I have strikingly similar resource of scripts I’ve built up myself on my hard drive. A couple of examples from the site to give you the idea follow;

9.  Order Comments
Display all comments for a particular order.. (viewed 1490 times)

10.  How to unlock a Dynamics user
This is useful in solving Dynamics GP error: "User is already logged-in" when you are trying to log .. (viewed 3397 times)

GPDynamicscom

Technorati Tags: ,

Smart Connect for Dynamics GP Webinar

I attended a webinar today hosted by Touchstone’s Dominic Houlbrooke-Bowers, about Smart Connect for Dynamics GP

Summary

There are two ways to summarise this product from this demo (i’ve not yet read more);

Replacement for integration manager using eConnect to enforce the busines rules, providing a richer and much faster experience than integration manager.
OR
A power user graphical user interface for eConnect

It has some very powerful extra features too like  push from internet placed excel or info path forms to web services and into GP.

Product description

More...



UPS World Ship XML Import from Dynamics GP

Problem

The Sales Order Fulfilment (SOP) software I wrote for Microsoft Dynamics GP, deals with multiple Dynamics GP companies. You can choose the company and fulfil any available pick lists for sales orders or invoices from that company. The software implements a least cost parcel routing engine. If it decides to route to UPS or TNT carriers we wish the software to ensure that that carrier invoices the correct originating company, avoiding the necessity for intercompany transactions in the accounting system to apportion costs.

The parcel details are sent to the UPS World Ship software as XML files, generated from an XSLT against the order tables SOP10100 and SOP10200 and several custom tables for pick lists and despatch records.

History

The XML Import has been something we have used for many years, since World Ship 5.0, I think we are on 11 now. However when we enquired about having multiple shipper billing accounts it was not possible without a separate instance of world ship for each carrier account. It was something that was mooted as a feature of the next version.

Last Week

Time has moved on and UPS upgraded us recently to the current version of World Ship, so I revisited the problem.

I investigated the Openshipments.xdr schema file for the new version of World Ship to see what had changed. I found a new element definition as shown below for the shipper UpsAccountNumber, i.e. the place the cost for the shipment should be invoiced to.

<ElementType name = "Shipper" content = "eltOnly" order = "seq" model = "closed">
<element type = "UpsAccountNumber" minOccurs = "0" maxOccurs = "1"/>
</ElementType>

I then changed our XSLT to add a node in our outputted XML, and a parameter to pass into the XSLT so that I may pass the UPS account number to the XSLT.

<Shipper>
<UpsAccountNumber><xsl:value-of select="normalize-space($AccountNo)"/></UpsAccountNumber>
</Shipper>  

After ensuring the accounts were set up and activated in the Shipper Setup of World Ship, we tried it out, thankfully it just worked, the consignment was generated against the company for who the order had been fulfilled.

TNT

We also use the auto import utility with TNT Express Shipper. In this case it is a fixed length text file import, but I applied a similar upgrade for TNT with our technical representative a couple of weeks ago. This results in the account number getting passed and mapped in the TNT software so that again the correct company gets the invoice.

TNT Errors

Another advantage of the new TNT software is that it passes back the consignment number and any errors that have occurred during the import, so it does a full handshake – like UPS have done for a long time. This makes my life easier as I don’t have a job running anymore to pluck the consignment numbers from the TNT SQL database in order to populate the Dynamics GP tracking number tables in Dynamics. As it happens I’ve started using our own consignment numbers now so GP is the source of the consignment number, but having access to errors allows us to correct address records in Dynamics GP that consistently throw exceptions in Express Manager. These exceptions are usually things like town in wrong field or a post code update making a post code invalid that was entered in the past in GP. Mismatches can occur between the versions of post code PAF file on GP and on TNT.

Conclusion

My fulfilment software now passes the account numbers for the carriers through depending on the owner company of the order in GP. This account number is used by TNT or UPS to assign that consignment to the correct company for invoicing, that in turn makes the accounts department happy as the billing gets easier.

Next…

It was also mentioned by TNT that they can now provide electronic invoices, UPS have done for years but this means it is now worth importing the actual cost of carriage back into GP so we can get a better analysis of margin for individual orders.

WaitOne while I introduce multithreading

For some time I've been meaning to do some multi threading in the Dynamics SOP Fulfilment software I wrote. I have always thought that I could make things run a little more slickly. At fulfilment the following processes occur;

  • Credit/debit card debited if one is attached to the order
  • Order lines are fulfilled by quantities on the pick list being fulfilled
  • receipt printed (optional depending on carriage option- customer collect etc)
  • despatch note printed (optional)
  • invoice printed (optional)
  • thermal parcel label(s) printed one for each parcel entered
  • consignment details are exported via XML to UPS / Business Post / TNT depending on carriage routing algorithm
  • UI is constantly updated with progress bar and messages about progress

Spinning the fulfilment thread off on to its own thread using a background worker control was straight forward and is something I did when VS2005 was released. However some of the above processes take quite a few seconds to run, the fulfilment computers are quite recent hardware and thus should be able to perform better.
Thanks to good object orientated design, it turned out to be fairly trivial to get each of these processes to run on their own thread and synchronise them using wait handles. I had to use wait handles as some tasks depend on the successful completion of others. More...