Thread: UPS WorldShip Interfacing

Does anyone know if it is only possible to do a batch export of orders for import into UPS Worldship? What we have used in the past with another shopping cart is what UPS Worldship calls key import. This enables you to export one order at a time instead of in batches. The problem with batches for us is that each item in an order is shipped in a separate box and each box needs its own shipping label. As we understand batch import it would import an order with 3 items and produce one label combining the weights for those three items. This will not work for us.

When we import one order at a time it allows us to manually separate order items and weights.

Any help on this is appreciated,
Lynn
Acadia Wreath Company

From Windows Control Panel on the computer running WorldsShip, setup an ODBC Data Source:

1. Open Windows Control Panel > Administrative Tools > Data Sources (ODBC)
2. From the "System DSN" tab, click Add...
3. Select the "SQL Server" driver.
4. In the Create a New Data Source box, enter Name: "Web_Orders", Description: <blank>, Which SQL Server to connect to: <select your SQL Server>, click Next.
5. Select either Windows NT Auth, or SQL Server Auth (enter Login ID and password), click Next.
6. Check "Change Default database to", and choose AspDotNetStorefront, click Next.
7. Click Finish, then OK.
8. Under the ODBC Data Source Administration window, verify that Web_Orders exists as a System Data Source, then click OK.

From UPS Worldship:

1. Select UPS Online Connect > Create / Edit Map...
2. In the "New Map" section, select Import from ODBC Database
3. In the Name textbox, enter "Orders", then click Create.
4. Under Select an ODBC Source, select "Web_Orders", enter an ID and password for your SQL Server, then click OK.
5. In the Edit Web_Orders import map window, set-up the following mappings using the Connect button:

Code:

Orders::CustomerID <--> Ship To::Customer ID 
Orders::OrderNumber <--> Shipment Information::Reference 1 
Orders::ShippingFirstName,Orders::ShippingLastName <--> Ship To::Company or Name 
Orders::ShippingCompany <--> Ship To::Attention 
Orders::ShippingAddress1 <--> Ship To::Address 1 
Orders::ShippingAddress2 <--> Ship To::Address 2 
Orders::ShippingSuite <--> Ship To::Address 3 
Orders::ShippingCity <--> Ship To::City or Town
Orders::ShippingState <--> Ship To::State/Province/County 
Orders::ShippingZip <--> Ship To::Postal Code  
Orders::ShippingCountry <--> Ship To::Country/Territory 
Orders::ShippingPhone <--> Ship To::Telephone

To enable the import:

1. Select UPS Online Connect > Keyed Import > Orders
2. In the text box that appears, ender an ADNSF Order number. The WorldShip fields will populate with the correct information.
3. Make sure you press F8 to validate the Ship-To address.

Thank you very much. I just want to make sure I am understanding the process. I won't be batch exporting from aspdnsf. This will do a connection directly from worldship to my database. Right?

Can I also have the item numbers ordered in the mapping?

What procedure do I follow to export tracking numbers from worldship to get them back into aspdnsf?

We have a very short shipping timeframe for our fresh christmas wreaths. We ship from Nov. 26th through Dec. 21st. Orders that come in prior to Nov. 26th are imported into worldship and future ship dates are entered so that we can then print out the label and get all the paperwork needed for processing.

Do we immediately import these tracking numbers into aspdnsf or do we wait until they are actually shipped?

Our concern is the tracking number confirmation going out to the client. We can setup worldship to send this shipping notification and delivery notification. I just don't know if when we import the tracking information back into aspdnsf if it automatically generates this email.

Your help is so appreciated,
Thanks,
Lynn

I'm almost 100% sure that WorldShip cannot update a row in a table, I can only insert so it not very usefull as far as updating ASPDNSF.

I spoke to one of the techs and they basically don't want to touch anyones DB for liability purposes I guess.

You can use the QuantumView feature of Worldship to send the emails and tracking info to the customer. This is what we do.

Hope this helps

Cano

Originally Posted by Lynn Jebbia

I won't be batch exporting from aspdnsf. This will do a connection directly from worldship to my database. Right?

Correct. This procedure allows you to import order information one-by-one into WorldShip.

Originally Posted by Lynn Jebbia

Can I also have the item numbers ordered in the mapping?

I don't know.

Originally Posted by Lynn Jebbia

What procedure do I follow to export tracking numbers from worldship to get them back into aspdnsf?

To export tracking numbers from WorldShop into ADNSF, a few steps must be taken:

Create a new table inside your ADNSF database:

Code:

create table dbo.TrackingNumber(OrderNumber varchar(50) null, ShippingTrackingNumber varchar(50) null, ShippedVIA varchar(50) null)

Add an insert trigger to this table:

Code:

create trigger dbo.on_Insert on dbo.TrackingNumber
instead of insert as
update Orders
  set ShippingTrackingNumber = i.ShippingTrackingNumber,
        ShippedVIA = replace(i.ShippedVIA, 'Ground Service', 'UPS Ground')
  from inserted i
  where Orders.OrderNumber = i.OrderNumber

Within WorldShip, create en export map:

1. Select UPS Online Connect > Create / Edit Map...
2. In the "New Map" section, select "Export Data from WorldShip"
3. In the Name textbox, enter "TrackingNumbers", then click Create.
4. Under Select an ODBC Source, select "Web_Orders", enter an ID and password for your SQL Server, then click OK.
5. In the Edit Web_Orders export map window, set-up the following mappings using the Connect button:

Code:

ShipmentInformation::ServiceType <--> TrackingNumber::ShippedVIA
Package::TrackingNumber <--> TrackingNumber::ShippingTrackingNumber
Package::Reference 1 <--> TrackingNumber::OrderNumber

To enable the export:

1. Select UPS Online Connect > Automatic Export After Processing Shipment > TrackingNumbers
2. Now, each time you process a shipment, the tracking number will be exported to ADNSF.
3. You must still send a confirmation email, however. On Admin > Orders, after you capture the credit card funds, you must click "Mark As Shipped", which will dispatch a shipment confirmation email to the customer.

Originally Posted by Lynn Jebbia

Do we immediately import these tracking numbers into aspdnsf or do we wait until they are actually shipped?

The export from WorldShip to ADNSF occurs each time your print a shipping label. One tracking number is exported at a time. However, the export operation doesn't mark the shipment as "Shipped". You must do that from Admin > Orders.

Mr. Jon Wolthuis,

Many thanks for your support on this topic (incredibly helpful). Your instructions led to a successful implementation for keyed entry orders. Now we are interested in automating the process (see below).

Should you have any information/advice in this matter, please contact us at your earliest. Alternatively, if you (or anyone you know) are available to conduct this work for us, please send a quote to admin[at]ecovalence.com. Deadline for completion: 02-19-10.

Current Setup:

- ASPDNSF [ML/DNN] running on dedicated server [Win2008/SQL2008].
- UPS Worldship running on separate server located at fulfillment warehouse.
- Both servers connected via VPN [hamachi] with ODBC per above.
- Single Order [keyed entry] import working flawlessly.
- *auto-export of tracking numbers not yet implemented.

Ideal workflow:
In short; we want each new order to automatically batch to UPS Worldship, label printed, tracking# exported back to ASPDNSF, and order marked as shipped.

At minimum - we need the orders to be 'batch-able' once a day by fulfillment center *with minimal effort.

To achieve either of the above, I imagine some custom tables and triggers must be written to the ASPDNSF database - and/or scheduled routines running on Worldship system. Your thoughts, ideas, and/or suggestions are most welcome.

Advance thanks for your time and consideration.

Regards,
Casey Jeppesen

Let me throw my .02 in here.

I have an app I wrote that has a button [load new orders]. Clicking that loads all ASPDNSF orders that are marked as new into a local db. UPS World Ship reads those orders from local db for keyed input (we scan invoice). When we ship the product in World Ship it writes out a tracking number to a local table. In my app I have a timer thread that reads this local tracking table and writes tracking info to ASPDNSF and marks as shipped.

All of this is done with WSI and takes my warehouse team very little effort to process orders. In addition, the app I have that loads orders also performs quality control. Once orders are scanned, the warehouse team then scans an order number and it loads all the ordered items. They then scan each item and it tell them if their order is correct and complete or if there are issues.

I could not personally do this for anyone as I am swamped but im sure a developer could put this together pretty easily for you using WSI.

Hi Jon, thanks for info, extremely helpful. My setup is slightly different... instead of keyed import one-by-one, we would like to batch import all "ready to ship" orders. I tried to figure out how to tell worldship to ONly import "ready to ship" orders, but i don't get it... any advise?