UPS Worldship

The purpose of this document is to describe how netFORUM can be integrated with UPS Worldship shipping service and the configuration of such process. This integration is offered as a custom solution based on its nature and setup. The current Integration works with Worldship version 10.0. Enhancing the integration to work with a newer version of Worldship may involve charges in addition to the setup fee.

The main objective of the ‘netFORUM WorldShip Integration’ is to allow netFORUM clients to obtain UPS tracking number in netFORUM iWeb from Worldship. The shipping charge to the customers/members will be based on the netFORUM functionality, while Worldship will provide the actual cost for the shipment.

There is no real-time shipping calculation in netFORUM in this integration.

There are limitations and assumptions for the worldship integration, which are listed later in this document.

Worldship Features

Please see https://www.ups.com/content/us/en/resources/techsupport/worldship/integrate.html for information about Worldship integration options.

Client Tasks

Client should have valid user license for netFORUM.
Client should have purchased UPS Worldship with valid user license
Client understands that netFORUM will only store tracking number from UPS Worldship and no fanancial information is being calculated or exchanged between netFORUM and Worldship
Client will need to be on active support agreement with Abila (for clients after

implementation)

Client will be responsible to set up all GL accounts (AR, Revenue, Write, etc) in the price records linked to UPS shipping product
Client will be responsible to install ‘WorldShip’ desktop application
Client will be responsbile in setting up the ODBC connection and mapping for import and export functionality in Worldship
Client needs to be familiar with Worldship functionality.
Client can purchase consulting time from Abila for setup and configuration assistance

Abila Tasks

Abila will run the custom script that adds all the custom shipping tables
Abila will configure database with the necessary triggers and stored procedure
Abila will update the metadata for storing tracking number and system options
Abila will deploy the setup file (dll) on the working directory

Usage and Instructions

This integration will apply to products that are shippable such as Merchandise, Subscription, Publication and Kits. All the products must be setup to be shippable and must have weight in netFORUM.

Run the following Setup Scripts (Abila Responsibility)

Important! Scripts need to be modified per client(business unit, address type, etc.) Read carefully before running the scripts.

Abila will add the SQL login called “Worldship” and associate the SQL login with the databases with public, netforumadmin, avectraadmin rights.

The ‘WorldShip.dll’ file must be copied to the bin folder of the working directory.

Abila will run the two scripts
Script named ‘1_netForum-Worldship Integration SCRIPT - Metadata.sql’ which will create the metadata on the database. Before running the script, Abila needs to modify the script and provide the business unit. (Example SET @atc_code = ‘ABC’).

Script named ‘2_netForum-WorldShip Integration SCRIPT - Custom Tables, SP and Trigger.sql’ which will create Table, Stored Procedure and Triggers on query analyzer. Before running the script, Abila needs to modify the script and update the Residential address type key(i.e. search for “residential” in the script and update accordingly per association)Then the script will:

Insert a product type called ‘shipping’ on table oe_product_type if it doesn’t already exist.
Insert a product called ‘UPS’ on table oe_product if it doesn’t already exist.
Note: - the script will use the first business unit on table ac_company as a business unit. This should be confirmed with client prior to running the script.
Insert a shipping product carrier called ‘UPS’ on table oe_shipping_carrier if there is no such record.
Insert the shipping product called ‘UPS’ on table oe_shipping_product if it doesn’t exist.
Insert the 8 prices under the product UPS on ‘oe_price’ table if non of them doesn’t exist.

Notes:

The price codes can not be changed because they will be used for the integration. Any UPS services that the customer wants that are not in this table will not work with the integration.
The price description could be changed to anything as long as the codes are maintained as in the table below.

Code	US Origin
01	Next Day Air
02	2nd Day Air
03	Ground
12	3-Day Select
13	Next Day Air Saver
14	Next Day Air Early AM
59	2nd Day Air AM
07	Worldwide Express ( For International Shipping)

The final result should look like the picture below if every step goes correctly.

Client Specific Setup in netFORUM (Client Responsibility)

The clients need to setup the GL accounts by editing each price record.

Note: The customer must associate all the prices with the appropriate GL accounts before using the system. This is a required manual step. This script will not associate the prices with the GL accounts.

In order for the UPS integration to work, users need to specify a weight (greater than zero) for all shippable products. It is imperative to have this setup properly because weight is a factor that will affect the pricing for UPS shipping.

Also, only shippable products will be considered in the integration.

netFORUM metadata change (Abila Responsibility)

Insert UPS shipping carrier system option with the name ‘UPSShippingCarrier’. If there is a value in this system option, netFORUM will recognize that this shipping carrier is the UPS shipping carrier and the integration will kick in if there are invoices that have this carrier affiliated to the product applied.
Create two extender columns called ‘ivd_tracking_number_ext’ and ivd_shipping_cost_ext on table ‘ac_invoice_detail_ext’ to hold the tracking number and the estimated cost generated from ups Worldship system.
Modify the ‘SELECT SQL’ for the dynamic child form called ‘Invoice Profile -> Detail’ on the table ‘md_dynamic_form_child’ to entertain the tracking number on the invoice list under individual profile and to create a web link for the tracking number to check the status or the shipment on ups website from the invoice profile page.
Create a custom shipping table ‘client_custom_shipping’ on the database which will be mapped to the UPS Worldship system that will be discussed later. This table gets populated when the store procedure "client_custom_shipping_insert_records” is run after the product fulfillment process.
Create another custom table ‘client_custom_shipping_export’ to hold the records that will hold the exported records from WorldShip.
Create a user defined function ‘getCommaDelimitedString’ to write a description of goods for international shipment.
Create ‘client_custom_shipping_insert_records’ stored procedure to create shipping records into the table *client_custom_shipping* based on the criteria that will be discussed later.
Create ‘client_custom_shipping_delete_records’ trigger on the ‘client_custom_shipping_export’ table to add the tracking number back to the ‘ac_invoice_detail_ext’ table.
Create a link on ‘Inventory Overview’ for the WorldShip as shown below.
Create the dynamic form, dynamic form control and dynamic extension on table md_dynamic_form’, ‘md_dynamic_form_controls’ and ‘md_dynamic_form_extensions’ respectively. Then the following form will show up when the WorldShip link is clicked.
Click the button in to create the shipping records.

Note: To trigger the stored procedure that creates the shipping records, the ‘WorldShip.dll’ file must be copied to the bin folder of the working directory. Otherwise, an error will be generated when the button is clicked

Creating the Shipping Information in netFORUM (Client Responsibility)

Purchasing from COETo purchase the product go to COE and add the line items and add another line item called shipping as can be seen on the picture below.

Worldship integration only works when the shipping is added at the invoice header level, not at the detail line.It is important to set up shipping price on the ups price, the shipping charge to the customer is still based on the setup in the shipping product and does not come from the real time shipping from UPS. The ship to address for every merchandize should be valid. Otherwise the UPS WorldShip will not process it and an exception will be generated.

Order fulfillmentGo to inventory and click fulfillment, then select ‘fulfill orders’. Enter the search criteria and click search. The picture below will be shown.

Click ‘Process Requests’, now the fulfillment record is created.

Create Shipping records on the custom shipping table Run the stored procedure called ‘client_custom_shipping_insert_records’ the stored procedure will create a single record on the custom shipping table for products shipped to the same address. NetForum assumes that all items on the same invoice that are shipped to the same address will be generated as only one shipment record. NetForum populates the shipping record with the invoice's ship-to address from the fulfillment process. The attention in the shipping records (to WorldShip) is populated by the customer affiliated to the ship-to address (cst_name_cp of the cxa record). The company column is the customer name if the ship-to address is a residential address (determined by the address' address type. If the ship-to address is not a residential address, the company column is the customer's organization (cst_org_name_dn) + invoice code. If there is no cst_org_name_dn, the company column will be the customer's name (same as the attention) + invoice code.

Note: A link will be built to the order fulfillment page in order to run the stored procedure. And the stored procedure should be run after the fulfillment process. When the link is run, it will create shipment records into the custom table and ready to be imported into Worldship.

Create Mapping to and from in Worldship (Client Responsibility)

The client should map the NetForum custom shipping table fields with the Worldship fields before importing and exporting the data. In order to does this task, the client must follow the following procedures.

Create a user called WorldShip on the working database (SQL database) who has access only to the two custom shipping tables, this will help to facilitate slightly the speed of import and export process
Create an ODBC system DSN connection. (Go to administrative tools -> Data Source (ODBC) and move accordingly)
Run the ‘WorldShip’ ApplicationClick on the ‘Import/Export Data’, then Create/Edit Map… to create or edit the mapping.
Then on the form below, select the New Map group box and select Import ODBC Database radio button. The shipment must be selected form the dropdown list.
After naming the map click the create button, then you are prompted to select the connection and enter username and password.
Select the custom table from the ODBC Tables and Map every field to the WorldShip Fields dropdown list by selecting a field from left then another on the right side of the list box after that click the connect button and map similarly for every field.

All the import mapping should look like the diagram below. This diagram is presented to avoid confusion.
Similarly create a mapping for export to the export custom table and the mapped connection should look like this
The final export mapping should look like the pictures below

Note: WorldShip fields are not located on the same dropdown list so, it should be noted to find the mapping field from the WorldShip fields dropdown list.

Import and Export Shipments in Worldship (Client Responsibility)

Once the mapping is done it is time to import and export the data to and from the UPS WorldShip.

Importing from custom shipping table to the UPS WorldShip. From the UPS WorldShip application click on the ‘Import/Export Data’ menu, then select the batch import.The following picture will be shown.
Select the importing map then Process shipments automatically after import and Delete existing records before import as necessary, then click next to proceed. The system will assign tracking number and print the UPS shipping labels with barcodes. Do not import the same records from NetForum into WorldShip again. Otherwise, you will end up with duplicate records. The records that have been exported into NetForum will be deleted from the client_custom_shipping table so they will not be imported again in the next Batch Import process. Make sure, export takes place before importing to avoid duplicates. Abila strongly recommends that to process shipments manually rather than automatically.
Exporting from UPS Worldship to the custom shipping export table Before exporting the information back to NetForum, the end of day processing should be done in Worldship. In order to do this click on the End of day button and it will process end of day and all pending shipments will be ready for pickup.
Click on the History in order to see the shipments. Then the following screen will appear:
Select the date for which the pick up is ready and all the shipments could be seen by expanding the tree view.
Click the batch export menu from the ‘Import\Export Data’ menu and the screen below will be shown

Select the date range and export map to export and follow the wizards. Worldship will print pick up summary at the end of the export process. The trigger on the custom table ‘client_custom_shipping_export’ will write the tracking number back to the invoice detail extender table so that the status of the shipment could be tracked.
In the case of multiple packages (the shipment will contain multiple packages in worldship), the export process will not populate the invoice detail extender table with multiple tracking numbers. This should be handled in worldship and outside of the integration.

Notes:

The records on the custom shipping table ‘client_custom_shipping’ will be deleted after the tracking number are written back to the invoice detail extender fields.
If you export the same date range again, duplicate records will be created in ‘client_custom_shipping_export’ table so do not export the records that fall in to the same date range again.

Assumptions and Limitations

Please read the following sections and make sure client agrees and understands the limitation and assumptions of this integration.

Limitations:

This integration is using ODBC connection from the desktop application of Worldship. It can only work for premised based customers and customer on dedicated hosted machines. The worldship application needs to be installed on the dedicated machine at Abila and client needs to use VPN to run the worldship application.
Not all Worldship service codes will be available in this integration. It can only work with 7 service codes and one worldwide express for international provided in this document.
Shipping product can only be applied at the invoice level in COE, not at the detail level.
Each invoice detail line in NetForum can not have more than 1 tracking number.
All shipment will be using the same box type with package code “Package” in Worldship.
The performance of import/export functionality is controlled by Worldship and ODBC. Abila will not be able to speed up this functionality.
For international shipment phone number and Description of Goods are required.
This integration is tested only on Worldship version 10.0 only. It will require additional cost to research on the compatibility with newer versions of Worldship and it may incur additional cost.
Worldship integration only works when clients have a dedicated server here at Abila or premised based. This is not an option for clients that are in shared hosted environment.

Assumptions:

Any functions not mentioned in this document are not part of the integration and may require additional development effort and cost.
All items on the same invoice shipped to the same address will generate only 1 tracking number. Except in case of back order.
The entire quantity of a line item is fulfilled in one shipment. If a product is setup to fulfil partial quantities and the iWeb user processes fulfillment on an order with less than the full quantity ordered, this integration will NOT create shipping records for WorldShip. (Baseline NetForum inventory fulfillment will create fulfillment records for the remaining order, but those records will not be included in the export to WorldShip.) Recommend setting products to not allow partial shipments.
The ship from address will come from the configuration of the Worldship and not coming from the NetForum warehouse.
Address and Primary phone in NetForum needs to be valid otherwise the import into Worldship will create exceptions and no shipment record will be created.
The shipping charge in Worldship is for reference only and will not be carried back into NetForum. Tracking number will be brought back to NetForum and displayed in a child form on the invoice line items.
Client needs to know how to use Worldship software and how Worldship is integrated with UPS online shipping process.
In order for the integration to handle international shipping, the following services and package types should already set for the following countries.
If any UPS software changes that causes netFORUM’s import/export to fail in this document, client will pay Abila to make necessary adjustments to keep the integration function properly
Client should provide Abila with enough permissions on the web server and SQL server in order to perform necessary activities

Note: If the Service and country doesn’t match, Worldship will not process the shipment as desired.

Troubleshooting

A client using the netFORUM WorldShip integration set up a procedure for them to periodically clean up the dBs when the volume of shipping eventually fills up the dbs:

The following procedure is posted here as a guideline for clients to use as an example to developing their own procedure for cleaning their related WorldShip dBs.

To Clean Up UPS Database

Go to c:\ups\uows
Copy upsdb
Paste and rename Copy of upsdb 062007 (use current date)
Open upsdb
Open table calBilling – Leave the first record, but delete all of the rest – to do this, highlight the second record, hold the shift key and highlight the last record – this should highlight all records but the first one – click the Delete button
Open table calPackage - Leave the first record, but delete all of the rest – to do this, highlight the second record, hold the shift key and highlight the last record – this should highlight all records but the first one – click the Delete button
Open table calShipment - Leave the first record, but delete all of the rest – to do this, highlight the second record, hold the shift key and highlight the last record – this should highlight all records but the first one – click the Delete button
Open table calPkgAgent – Sort by column Sm_business_Name.
Keep records that start with INS and keep the two records with NATP (National Association of Tax Professionals) Delete the rest.
Close Access and open UPS and you should be ready to go.