xTuple.com xTupleU Blog & News Customer Support

Sales order import best practices

Greetings. I’m working on modifying a process stream that imports sales orders and places them as open sales orders along with manually created sales orders in xTuple 5.0. All manual orders and imported orders flow into the same stream and are fulfilled within the standard xTuple order handling processes.

I’ve been using this process for a number of years to seamlessly process orders created on a drupal commerce website and use the same function to process orders entered on the Amazon shopping site and collected using the Amazon api. A simple cron job is used to process both of those streams of orders and once they get into xTuple they are good to go.

My need to modify the process is triggered by the adoption of the Avalara tax integration now available within the xTuple client. This is not an issue with the Amazon orders as Amazon assumes responsibility to collect the sales tax there. However within the Drupal site we are using the Avalara APIs to provide a total with tax calculated by Avalara to ensure the credit card gateway collects the appropriate amount. Again, this is a simple task and we have access to all the data.

My question regards the actual import functions for the Drupal orders. We have been using the two xtuple “views” named api.salesorder and api.salesline to process the incoming records. However it looks like these two views do NOT touch either the taxhead or taxline tables. Whereas a manual order does create tax info for a sales order.

I was just wondering what is the “Best Practice” for importing a sales order with tax info for the line items? Does the standard xTuple code use a trigger to produce the taxhead and taxline tables or should I do it manually? I was also wondering what role the taxdetail records play. If there is any publicly available info on the current flow of tax info within the xtuple system id go there for my answers. Thanks.

Jim Wirt

Hi Jim,
There’s probably others better suited to answer this question, but I have used the following elsewhere to programmatically trigger calculation of tax on a document (in this example the Invoice) from outside the document itself.

EXECUTE format('NOTIFY calculatetax, %L', 'INV,' || _invcid);

This works in version 5.0 for both internal tax calculations or Avalara and something similar should work for other document types.

But I would agree with your comment, that the API views should possibly have this mechanism built into them.

Dave,
Thanks for the feedback. I’ll have to chew on that one for a bit. I’ve never used the NOTIFY function of Posgres and my quick reading of the documentation has not given me much understanding. I’ll have to do a deeper dive to understand what it is doing. Looks like I need to “upgrade” my Postgres skills!

Jim

I should have also mentioned this approach requires the PC running the import to also have an xTuple client running. When you run a (v5.0) xTuple client it also starts a process to LISTEN for signals of type ‘calculatetax’.

So what we are doing above is sending a simple NOTIFY signal saying trigger the calculatetax process for Invoice and invoice ID. The listener then independently goes off and runs the refreshing of the invoice, which also means, if you have Avalara integration, going off and calling their API and getting the correct tax returned.

Given all the complexity of doing Avalara REST API calls, a simple NOTIFY process seems to me to be the best way of updating taxation on the various documents.

The example above is actually part of the Fixed Asset sale process. So a sale Invoice is created for an asset, and all we have to do to correctly get tax though whatever means, is call that NOTIFY.

Thanks for the additional info. The process I’m running is not running from a PC. It is just a cron job running on the linux database server doing a “SELECT processorders();” that pulls orders from a different database server that is hosting the Druapal commerce site. The tables on the Drupal side are mounted as foreign tables so it has access to them without the exposed drupal site having any knowledge of the xTuple site. Would the “NOTIFY” process work if there wasn’t an active listening client running?
I’ve been sluething the native xTuple code and I think maybe I can get the tables I need populated by running the “savetax” function after I create the cohead and coitem records by doing the insert I’m still currently using. Since the drupal side does a call to Avalara to calculate the tax I think I can just store the json returned to the drupal site and have it available to use on the savetax call. That looks like maybe how xtuple is doing it when Avalara integration is enabled.

Jim