Update Shipment Status Query

To save shipment information back to your ODBC data source, you will need to compose an Update Shipment Status Query. The Update Shipment Status Query should be an update or insert statement that will save tracking numbers back to your database. In this query, you can use the following parameter substitutions for the values being returned by Webship:

Substitution	Description
@carrierCode	A code indicating which carrier was used
@carrier	The name of the carrier that was used
@serviceCode	A code indicating which shipping service was used
@orderId	The orderId loaded in your Get Orders Query
@orderNumber	The order number assigned to the order
@trackingNumber	The tracking number associated with the shipment
@shipperReference	The custom reference from the original order
@shipperReference2	The second custom reference from the original order
@totalShippingCost	The quoted cost of the shipment
@shipmentDate	The date of the shipment
@weight	The total shipping weight of the shipment
@insureAmount	The dollar amount that was insured
@insuranceCharge	The charge for the insurance
@baseCharge	The base charge for the shipment before any extra charges are added
@toName	Receiver name
@toAddress1	Receiver address line 1
@toAddress2	Receiver address line 2
@toCity	Receiver city
@toState	Receiver state
@toCountryCode	Receiver country code
@toZipCode	Receiver zip code
@toEmail	Receiver email
@toPhoneNumber	Receiver phone number

Example

Imagine that your ODBC data source has a table for saving shipment information with the following structure:

• Shipment
  • SalesOrderID
  • Carrier
  • Service
  • TrackingNumber
  • Cost
  • ShippingDate
  • ShippingWeight

You could use the following Update Shipment Status Query:

INSERT INTO Shipment
  (SalesOrderID, Carrier, Service, TrackingNumber, Cost, ShippingDate, ShippingWeight)
VALUES
  (@orderId, @carrier, @serviceCode, @trackingNumber, @totalShippingCost, @shipmentDate, @weight)