- App Store
XML Import: Sales Orders vs. Invoices
I'm not a master at ERP--I'm the web guy. My job is to run the xTuple web sites and the xTuple xChange. But one of the benefits of ERP is the ability to consolidate data from all kinds of sources into a single ERP accounting system. In our case, we export customer and order info from the xChange and import it into xTuple ERP (see our docs on xTuple e-commerce integration for more info). This forced me to learn something about ERP order processing. I thought I'd share my experience with the community to help others avoid my errors.
When we started setting up our import process, I needed to find a record type in xTuple to receive the order data from our online store. I looked through the xTuple API (I use PGAdmin to browse the PostgreSQL database) to find a good candidate and I settled on the Sales Order view. This seemed like a logical analog to the Order record from the store. So I set up the import process to translate Ubercart Orders into xTuple Sales Orders. Boom. Done. Great.
As soon as we did our first import I realized my mistake. xTuple ERP is designed to handle a multi-step ordering process, where a sales team generates Sales Orders, which in turn can generate Work Orders on the manufacturing floor, or else go straight to a shipping department where they generate a Packlist. The Sales Order has to actually get shipped before it is deemed "Complete." At the end of this process it becomes an Invoice, and only then can a payment be applied to it. This is exactly how it SHOULD work, if you have products that need to be built and assembled and shipped.
In our case however, all these steps were artificial. We imported the Sales Order, and then had to escort it through these steps of "shipping" the product, even though the order had already been completed online. We had nothing to ship. It was cumbersome and somewhat frustrating. This is not the way we needed our small business accounting software to work.
So I went back to the API and looked again and this time I noticed the Invoice view. I realized that I could skip past all the unnecessary shipping steps if I just imported the Order as an Invoice rather than as a Sales Order. The Invoice view contains most of the same columns as the Sales Order view, so it was an easy matter to adjust my xml document to use Invoice instead. Now I had it set up to insert an Invoice for the order, and a Credit Memo for the payment. The two are matched up in the Accounts Receivable Workbench in xTuple, and the process is easy and streamlined.
The lesson here is simple: know your business. If you are going to sell online and import your orders into xTuple, the key question is are you shipping products or not. If you are, go with a Sales Order; if not, enter the orders directly as Invoices. It will save you time and avoid frustration.
We are distributing an xTuple e-commerce solution for exporting Ubercart orders for import into xTuple. Right now, this assumes you want the orders to be Sales Orders in xTuple. I'd be interested to hear if that works for most users, or if there is any interest in the newer version that I developed for us, the one that imports into Invoices, as well.
Thu, 11/04/2010 - 16:06#1
BC.... I'm so glad I found this blog post. You should link it from some of the other pages that have info about the uc_xtuple_edi module. You've done a very good job of offering a simple/understandable explanation of the process that isn't so obvious in other places. For my purposes it works as is. I'm shipping products so what you have works for me. I'm sure people selling files online like you guys do might enjoy the option of having a module that does either/or.... or having it as a selection. I do have one question for you as I'm just trying to determine if I'm seeing things wrong. When I import an order from Ubercart everything seems to be working just fine with the exception of the sales tax. I show the items and shipping on the sales order but no sales tax. Is this the way it's supposed to operate? And this will fall into place when the sales order transforms to an invoice?
Tue, 11/30/2010 - 16:29#2
Firstly, I don't know anything about Drupal or the uc_xtuple_edi module but I may know why you're having trouble with sales taxes.
The problem may be due to how xTuple handles taxes in that, from what I've gathered by trail and lots of error, xTuple doesn't store the tax $ in the invchead or invcitem tables. It just stores the tax_id and rates. My guess is that the taxes are calculated on the fly when the invoice is posted and stored in invcheadtax and invcitemtax. This has it's benefits as everything is table driven and just happens 'automagically'. But...
Of course, you're loading into api.salesorder and api.salesline. But basically works the same way.
So in your case you should verify that all of the tax authorities are set up properly for your customers and items and that they are being passed via the interface.
I'm fighting a problem myself where I'm trying to import invoices via perl from a legacy system where I don't get any line item details just invoice totals and so far I can't figure out how to use the api.invoice to populate the tables. I suspect that this just won't work.
I hope this is helpful. Cheers.