Upon taking up my new post as Director of Technology with L.N. Curtis & sons in March of 2010, one of my mandates was to debug a 2.5 year e-commerce project that had stalled.  This was a vital project as the company did not yet have an e-commerce site and was therefore missing out on a significant revenue stream.

The new site development had been outsourced to a custom .NET development shop in San Jose but only the site design and basic back-end for managing products had been implemented.

The remaining challenges were many and varied:

1. Product synchronization with the Infor SX Enterprise ERP system had yet to be developed and gotten working.
2. Product pricing for web sales had yet to be finalized along with specialized ERP pricing records.
3. Sales order insertion into the ERP for order fulfillment had yet to be developed.
4. Missing Internet merchant bank account.
5. Missing Internet payment gateway.
6. Shopping cart had yet to be implemented.
7. Most static content on the site was missing or not final (e.g. Privacy Policy).
8. No method for determining correct Sales Tax.
9. No method for determining correct shipping costs.
10. No method for automatic email confirmations on ordering and shipping.

As you can see there were many key site elements which were missing and had to be dealt with quickly; and these were handled with the site going live in 8 months just before Thanksgiving 2010.

In the following sections I detail the hurdles to get the site built and launched:

• Infor SX Enterprise ERP Integration
• Product Synchronization
• Shipping Charge Calculation
• Sales Tax Calculation
• Sales Order Insertion
• Shopping Cart
• Internal Order Fulfillment
• Post Launch Report
• Next Steps
• Post Mortem

Infor SX Enterprise ERP Integration

Firstly the Infor SX Enterprise API is not web service based and requires one to load either a .NET or Java API on your web server which you then call.  Couple this with no documentation and expensive Infor professional services, I now had a recipe for frustration.  Given problems with the .NET API it was dropped in favor of the Java version which required a re-write of the interface code.  Later, the interface had to be written into a web service form itself in order to handle unusually long socket open times of the underlying Java API – an astonishing 4-5 seconds.

The web server had been located in a 3rd party data center whereas the ERP server was in-house and this required a hardware dedicated VPN tunnel to provide a secure channel.

Product Synchronization

The ERP system maintains all web product pricing and availability information which needed to be synced with the website DB.  The antiquated Infor API does not provide a simple query mechanism to simply get all web coded products.  Instead we were forced to get every product (over 50,000) and then narrow down the result set to the 2,000 web products and then query again to actually get pricing and other details.  The process takes many hours and is done overnight.  This is certainly not an elegant solution but it works until Infor can provide a more interactive and/or robust API.

All 2,000 products had to be configured in the ERP system with the correct web product code, pricing and weight.  Interestingly, many of the products had no weights and thus there were many trips to the warehouse to check actual weights!

Shipping Charge Calculation

The ERP system did not have the ability to determine estimated shipping costs and these would be needed in the shopping cart.  Given that our items ranged from very light items (like batteries) to very heavy items (50 lb containers), free shipping was not an option, especially since we would be shipping anywhere in the United States.

A shipping system module for the ERP system was located, evaluated and brought online: Logicor GlobalShip.  This system has a seamless interface with the ERP and is accessed by the website via an API for determining shipping rates based on destination zip code, weight of item(s) and class of service.

The GlobalShip product was also rolled out in one warehouse to replace the existing UPS Worldship PC.  This new system provides:

• Warehouse shipping station: PC with GlobalShip software, scale, shipping label printer and bar code scanner.
• Bar scanning of warehouse pick tickets to bring up item for shipment directly from the ERP.
• Weighing package on scale and adjusting shipment details.
• Printing shipment label and updating ERP system with shipment information, including tracking code.

Sales Tax Calculation

The company has a complex sales tax situation in that it has 11 different tax nexus points in the western United States.  This includes Washington and Colorado which have destination sales tax laws (every zip code and even partial zip codes have their own sales tax rate).  Thus a simple tax calculation was out of the question.

The ERP system was being updated manually to provide the correct sales tax rates for regular orders but the API would have needed custom modifications to calculate the right sales tax.

Therefore, the decision was made to adopt the ADP Taxware product which worked seamlessly with the Infor ERP in order to provide sales tax rates based on the destination zip code.  This was a major project to evaluate, install and turn-up.  This product was a great enhancement as it removed the necessity for manual updating of our ERP sales tax tables plus ADP even prepares the sales tax returns and will file on your behalf.  Great product.

Sales Order Insertion

When a user is ready to check-out, a Sales Quote request is sent to the ERP in order to get the correct sales tax and estimated shipping.  Then after the user’s credit card is charged, an actual Sales Order is sent to the ERP and this kicks off the internal order fulfillment process.

Interestingly, a sales quote cannot be converted to a sales order via the API and thus we had to create a job to delete old sales quotes.

Shopping Cart

The first order of business was to get an Internet merchant bank account which is normally a routine process but not if the company is considering changing banks.  This added a delay of some months and then the bank had quite a lengthy approval process – they wanted to see an actual working shopping cart with a secure cert (quite unusual since a secure cert is only needed on a live site) before giving us an account.  Anyway, we finally got an account!

Signing up with Authorize.net as our payment gateway was a snap and was easily integrated with the website.

Internal Order Fulfillment

The company had never had an e-commerce operation and thus I established the order fulfillment and customer service processes in the company which included:

• Setting up a new toll-free number.
• Training the warehouse staff on how to use the new GlobalShip shipping system for shipping web orders.
• Training the inside sales staff on the website and how to provide customer service.
• Setting up the ERP system for alerting the right management on new orders.

Post Launch Report

The site launched with no significant issues and started taking online orders almost immediately.  Internal order fulfillment was also smooth.  Metrics since going live:

• 9,575 visits
• 92,000 page views
• 9.65 pages/visit

Overall, a technically beautiful launch.

Next Steps

The emphasis is now on marketing the site heavily and we have selected DEP Labs as our Internet marketing partner.  We have also adopted Salesforce.com as our new sales and marketing platform and will be used initially for email marketing campaigns.

Post Mortem

Now that the dust has settled, how would I have done this project differently?  I would have taken a closer look at the ERP API sooner in order to determine if it would really fit the bill or not.  Given the difficulties we had with the Infor API, I would have preferred making the website more intelligent about product information and only use the ERP for availability information.

I am a big fan of SaaS and would also have prefered to have built the site on a SaaS e-commerce platform and thus leveraged all that the e-commerce platform could provide (e.g. user contributed content, reviews, accounts, etc.).

If I could have swapped out the ERP system altogether then that would have made my day but even Hercules has his limits.