Get Orders Query

To load orders from your ODBC data source into Webship, you will need to compose a Get Orders Query. The Get Orders Query should select all of your orders that are ready to be shipped. The query will need to be written in such a way as to map values in your database to the following field names:

Field Name	Description
orderId	The primary ID used to identify the order in your data source
orderNumber	The order reference number (usually this will appear on the shipping label)
orderDate	The date the order was placed (YYYY-MM-DD)
shipperReference	A custom reference field, which is placed on the label when possible
shipperReference2	A second custom reference field, which is placed on the label when possible
shippingService	The name of the shipping service chosen for the order (e.g. "Expedited" or "Standard")
orderGroup	A string used to categorize orders within Webship
name	The receiver's name to appear on the shipping label
company	The receiver's company name to appear on the shipping label
address1	The receiver's first address line to appear on the shipping label
address2	The receiver's second address line to appear on the shipping label
address3	The receiver's third address line to appear on the shipping label (if supported by carrier)
city	The receiver's city to appear on the shipping label
state	The receiver's state to appear on the shipping label
zip	The receiver's zip/postal code to appear on the shipping label
country	The receiver's country or country code to appear on the shipping label
email	The receiver's email address
phone	The receiver's phone number
weight	The weight of the order (counted as ADDITIONAL weight when itemWeight is also present)
length	The length of the shipping package
width	The width of the shipping package
height	The height of the shipping package
weightUnit	The unit of all weights in this order (one of "lb", "kg", "oz", or "g")
dimUnit	The unit of all lengths in this order (one of "in" or "cm")
itemSku	The sku of this order item
itemTitle	The title of this order item
itemQuantity	The quantity of this order item
itemPrice	The price of this order item
itemWeight	The unit weight of this order item

If you have orders that contain more than one item, your query can output multiple rows per order where each row has a different value for the itemSku, itemName, itemQuantity, itemPrice, and itemWeight fields.

The default weight of a shipment in Webship will be calculated as the itemWeight times the itemQuantity of each item added together plus the value of the weight field.

Example

Imagine that you have an ODBC data source with the following tables and columns:

• SalesOrder
  • ID
  • InvoiceNumber
  • OrderDate
  • ShippingMethod
  • AddressID
  • OrderStatus
• Address
  • ID
  • FirstName
  • LastName
  • Address1
  • Address2
  • Address3
  • Company
  • City
  • StateOrProvince
  • PostalCode
  • CountryCode
• OrderItem
  • ID
  • SalesOrderID
  • SKU
  • Title
  • Quantity
  • UnitPrice
  • UnitWeight

Your could use the following Get Orders Query:

SELECT
  SalesOrder.ID AS orderId,
  SalesOrder.InvoiceNumber AS orderNumber,
  SalesOrder.OrderDate AS orderDate,
  SalesOrder.ShippingMethod AS shippingService,
  CONCAT(Address.FirstName, ' ' , Address.LastName) AS name,
  Address.Company AS company,
  Address.Address1 AS address1,
  Address.Address2 AS address2,
  Address.Address3 AS address3,
  Address.City AS city,
  Address.StateOrProvince AS state,
  Address.PostalCode AS zip,
  Address.CountryCode AS country,
  OrderItem.SKU AS itemSku,
  OrderItem.Title AS itemTitle,
  OrderItem.Quantity AS itemQuantity,
  OrderItem.UnitPrice AS itemPrice,
  OrderItem.UnitWeight AS itemWeight
FROM
  SalesOrder, Address, OrderItem
WHERE
  SalesOrder.OrderStatus = 'Processing'
AND
  Address.ID = SalesOrder.AddressID
AND
  OrderItem.SalesOrderID = SalesOrder.ID