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.
- 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)
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.
- 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.