Order Export

Requested page is not in the provider filter.

Order export functionality is used to export Sana Commerce orders from the SQL database to the MS Dynamics NAV database.

 

Order export functionality exports only orders which have status 'Paid' in the 'PaymentStatus' column of the 'Orders' table.

Each order has the 'ExportState' field, which indicates whether the order has been already exported, marked to export or not exported. There are three order states available:

After a new order is created in the SQL database, it receives the state 'Not exported'.
The export state of all orders that are going to be exported is changed to 'Marked' at the beginning of the order export process.
If the order export was successful the state in the 'ExportState' field is changed to 'Exported'.

If any problem occurred with the order during the export process, the state of the order will remain 'Marked'. The process will skip this order and continue to perform the order export. Next time the order export process will select the problematic order along with other not exported orders. Each order in the SQL database has the field 'ExportDate' (located in the Fields xml), which stores the date and time of the moment the order was successfully exported.

 

During the order export at first the customer information is saved in the 'Customer' table in NAV and after that the order information is saved in 'Sales Header' and 'Sales Line' tables.

 

The 'Location Code' field doesn't exist in the Customer template in NAV database.   If this field is empty, the stock amount information will be incorrect.  The product import is retrieving inventory information based on the 'Location Code' field of the B2C customer. Therefore it is also used in the order export for creating the customer to so that the inventory is taken from the same 'Location Code' . Next time the stock import runs the inventory in the website is updated. The 'Location Code' field must be specified for B2C Customer template.

This new Dynamics NAV customer is created based on the country code of the billing address of the customer, because the country code is used in NAV to determine the customer template. This relation can be found in the 'Country/Region' table (ID=9) where the country code is mapped to the 'Code' field and the customer template is fetched from the 'Customer Template Code' field.  The customer template is used to add VAT posting groups, discount groups and other NAV-specific information (e.g. Currency Code) for the created customer.

The Customer template must be determined for the 'Country/Region' table for successful order export.

 

Payment costs and shipping costs of an order are imported into NAV according to the settings which are specified in the 'Webshop Card' (the 'Import/Export' tab) window of the 'Webshop' menu item. Both costs will be added as a salesline to the order based on the settings of the 'Webshop Card' window.
The VAT calculated for the payment cost and shipping cost will depend on the selected customer template (based on the country) and the Posting settings of the selected sales line type (G/L Account, Item, Resource, and so on). The price for these sales lines however, will be set by the order import and does not depend on any setting in Dynamics NAV.

 

The created customer and order in the NAV database will get an autogenerated Id based on the No. series. The original 'Id' fields from the 'UserProfile' and 'Order' tables in the SQL database are also saved for each exported order. The user 'Id' is saved in field 'Sana Customer No.' in the 'Customer' table in NAV, while 'Id' of the order is saved in the 'Sana Order No.' field of the 'Sales Header' table. Both fields are available on the 'E-Commerce' tab of the 'Sales Order' window.

 

'Sana Order No.' and 'Sana Customer No.' Fields

 

You can read about field mapping between the NAV and SQL databases during the order export process here.