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...

Concurrent user log for Dynamics GP

A few months ago we kept hitting our seventy five concurrent user limit on our GP server. Rather than rushing out to buy some more licences it was time to investigate how it was getting used.

In order to do this a SQL job was set up sample the current concurrent users on the system throughout the working day, logging the results into a SQL server table for later analysis.

I was not too interested at this point in the detail of who and where people were logged in, merely how many, thus a few mins later we have created a table in the BI database, UserConcurentCount that has two columns, UserCount and TimeDate.

The sql job created had one step running every 10 mins that did the following:

/* Insert into the monitot table how many users visiting */
Insert into BI.dbo.UserConcurentCount
 (usercount,[datetime]) 
(select Count(*) as UserCount, getdate() as SampleTimestamp from dynamics..activity)

This results in a table that can be used to build a histogram using a bit of TSQL and excel of the number of users in the system through the day. The problem seems to be with the changing patterns of work in a small number of the part time staff in some departments causing a pinch point when change over half way though the day occurrs and both sets of staff are in the system. The problem now seems to be resolved and we hover under the user limit now.

Take care to put a clean up script on a weekly job clean old entries out of this log table, no need to build up excessive records unless it is useful to you.

Dynamics GP used at the Trade Counter

This implementation of Dynamics GP had some new challenges. The company have a trade counter at the front of the warehouse, thus they needed a solution to allow entering of orders, picking and fulfilling them and presenting an invoice to the customer all as the customer waits. As this was trade, customers need invoices to take away with the goods.

Workflow

The workflow has ended up like this;

  • Customer goes to trade counter
  • Trade counter staff work with customer to enter the items they require on to an invoice SOP type document in Dynamics GP. There is no need for a sales order as the items will not be back ordered. If this is required the back ordered items can be pushed through to an order.
  • Send the invoice to the warehouse batch to allow the warehouse pick printing service to print off the pick list and maintain the integrity of the pick by locking access to the document.
  • Staff pick the items for the invoice.
  • Goods arrive at the counter, customer agrees it is all they want - customers have an annoying tendency to change their mind or want to add stuff to the invoice.
    If they add stuff or change stuff on the order, the invoice is recalled from the warehouse batch, changes made and a new edition of the pick list is printed by submitting it to the warehouse batch again.
  • Once everyone is happy, the sales order processing fulfilment module I wrote fulfills the order, recording it is of collect type, defaults the picker, packer and checker names and prints delivery notes, one for the customer to sign the other for the counter records, it also prints invoices as required for the customer to take away.

PDF versions of all the documents invoice, pick lists, despatch notes, as usual are all automatically stored on the document server for later viewing or reprinting or web server serving as required.

Often the telesales team or the website will take orders that are for customer collect, these are dealt with by transferring them to invoice as the customer arrives at the counter, and otherwise the process remains the same.

Order intake statistics

This concept of receiving sales orders in to invoice type documents breaks one of the business rules that our reporting uses for establishing order intake. Previously this analysis was done merely on sales orders. Thus some work on the Business Intelligence was required to work out what constituted a genuine order intake document, indeed line and what was just part of the process document flow. i.e. don't count an invoice created from a sales order as order intake, unless any lines were added to the invoice by editing the invoice after the transfer, in this case they need to be counted. I've avoided having to write this report, frankly that is great as I can get on with some more process improvements.

Quick and smooth picking of goods

This solution seems to be an improvement on the previous ERP system that was in use and I intend to make some more changes to the fulfilment software to improve it further.

Reporting services used to produce invoices via XML web services

I used reporting services, in an attempt to move away from the Crystal that comes with .NET. I had to call the report from a windows service and from the .NET application but as the invoice can be subject to many changes during the early days while things are settling in, I chose to use a reporting services server to print the invoices. This ensures that any changes to the report end up reflected on both the invoices produced automatically from the fulfilment software and those produced in batches for other orders where the invoices are posted. I used the web services API to invoke printing of the report. More about that in the next blog post. The speed of invoice printing is not quite what I would like at the moment by using this method, but we have not looked at all at optomising the server or code yet so I am confident improvements will soon follow.