To use the UPS WorldShip export, you will need to copy the SQL query below into a new Saved Export. To find your store's Saved Exports, go to Inventory > Import/Export in your Admin Area. Click the Saved Exports tab, then click Add New Query and enter the information from the table below into the appropriate query fields. Be sure to Save your new query. 🌐

  1. Go to Orders > Process Orders.
  2. For all orders that are ready to be exported for printing, change the order status to Ready To Ship and click Save.
  3. Go to Inventory > Import/Export and click the Saved Exports tab.
  4. From the Saved Queries drop-down, select the WorldShip query you just created.
  5. Click Export.

The above process will export all orders set to Ready To Ship and change them to Pending Shipment. When you complete the shipping process for those orders, you should return to those orders marked as Pending Shipment to fill in the tracking information and change the status to Shipped to complete the process.

Note ☝️

The below query assumes that all those orders marked as Ready To Ship are truly destined for UPS WorldShip. If you commonly mark orders as Ready To Ship that will be shipped via another carrier (e.g., USPS) at the same time as UPS WorldShip orders, you can modify the below query by replacing the following portion of the query:

WHERE OrderStatus = 'Ready to Ship'

with:

WHERE OrderStatus = 'Ready to Ship' AND ShippingMethodID IN
 (Select ShippingMethodID FROM ShippingMethods
 WHERE ShippingMethods.Gateway = 'UPS');

This will restrict the query to only those orders associated with UPS. Please note that any non-live shipping rates within your store (for example, "Free Shipping") that are actually shipped via UPS (and hence need shipping labels, etc.) will still need to have their gateway set to "UPS" for this query to work as expected.

qb_customerID: 0
qb_table: DB_Export
qb_title: Worldship
qb_querytype: Select
qb_export_filetype: CSV
qb_query:

SELECT 
LEFT(CONVERT(VARCHAR(10),
Sum(OrderDetails.ProductWeight*OrderDetails.Quantity)), 4) As
TotalOrderWeight
,Max(Orders.OrderID) As OrderID
,Max(Orders.OrderDate) As OrderDate
,Max(Orders.PaymentAmount) As PaymentAmount
,LEFT(Max(Orders.ShipFirstName + ' ' + Orders.ShipLastName), 35)
As ShipFullName ,LEFT(Max(Customers.EmailAddress), 50)
As EmailAddress ,Max(Orders.ShipCompanyName)
As ShipCompanyName
,LEFT(Max(Orders.ShipAddress1), 35) As ShipAddress1
,LEFT(Max(Orders.ShipAddress2), 35) As ShipAddress2
,LEFT(Max(Orders.ShipCity), 30) As ShipCity
,LEFT(Max(Orders.ShipState), 5) As ShipState
,LEFT(Max(Orders.ShipPostalCode), 9) As ShipPostalCode
,LEFT(Max(Orders.ShipCountry), 3) As ShipCountry
,LEFT(Max(Orders.ShipPhoneNumber), 15) As ShipPhoneNumber
,LEFT(Max(Orders.ShipFaxNumber), 15) As ShipFaxNumber
,Max(Orders.CustomerID) As CustomerID
--k,Max(Orders.TotalShippingCost) As TotalShippingCost
,ShippingMethodName =
CASE Max(ShippingMethods.ServiceCode)
WHEN '11' THEN 'Standard'
WHEN '03' THEN 'Ground'
WHEN '12' THEN '3-Day Select'
WHEN '02' THEN '2nd Day Air'
WHEN '59' THEN '2nd Day Air AM'
WHEN '13' THEN 'Next Day Air Saver'
WHEN '01' THEN 'Next Day Air'
WHEN '14' THEN 'Next Day Air Early AM'
WHEN '07' THEN 'Worldwide Express'
WHEN '54' THEN 'Worldwide Express Plus'
WHEN '08' THEN 'Worldwide Expedited'
WHEN '65' THEN 'Express Saver'
ELSE Max(ShippingMethods.ShippingMethodName)
END
,Max(ISNULL(Orders.ShipResidential, 'N')) As Residential
,'Prepaid' AS BillingOption
,'Package' AS PackageType
,'Y' AS ShipNotification
,'Email' AS NotificationType

FROM OrderDetails INNER JOIN Orders ON OrderDetails.OrderID =
Orders.OrderID INNER JOIN Customers ON Orders.CustomerID =
Customers.CustomerID LEFT OUTER JOIN ShippingMethods ON
Orders.ShippingMethodID = ShippingMethods.ShippingMethodID
WHERE OrderStatus = 'Ready to Ship'
AND ShippingMethods.Gateway = 'UPS'
GROUP BY Orders.OrderID;

UPDATE Orders SET Orders.OrderStatus = 'Pending Shipment'
WHERE OrderStatus = 'Ready to Ship' AND ShippingMethodID IN
 ('701', '702', '703', '704', '705', '706', '707', '708', '709',
 '710', '711', '712', '713', '714', '715', '716');

For more information, see the Volusion API developer documentation on WorldShip!

Did this answer your question?