Open topic with navigation

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 http://www.ups.com/bussol?loc=en_US&viewID=productView&contentID=ct1_sol_sol_wship for information about UPS Worldship features.

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 financial 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 responsible 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.

1. Abila will add the SQL login called Worldship and associate the SQL login with the databases with public, netforumadmin, abilaadmin rights.
2. The WorldShip.dll file must be copied to the bin folder of the working directory.

   The following scripts will run:

3. Script named 1_netForum-Worldship Integration SCRIPT - Metadata.sql which creates 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').
4. Script named 2_netForum-WorldShip Integration SCRIPT - Custom Tables, SP and Trigger.sql which creates 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(that is, 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 none of them exist.

Note: The price codes cannot 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.

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.
• 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: In order to fire the stored procedure that creates the shipping records, 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 COE To purchase the product go to COE and add the line items and add another line item called shipping. 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 fulfillment Go to inventory and click fulfillment, then select fulfill orders. Enter the search criteria and click search. 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 do map netFORUM custom shipping table fields with the Worldship fields before importing and exporting the data. In order to does this task, the client need to 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 Application Click 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 drop-down 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 drop-down 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.

  Note: 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.

  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. 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. Select the date for which the pick up is ready and all the shipments could be seen by expanding the tree view.

  Note: Note: - Click on the void button to void the shipment. Otherwise UPS will charge for all ready shipments.

  Click the batch export menu from the Import\Export Data menu.

  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.

  Note: 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 the netFORUM 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 do not 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 database.

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 the UPS database:

1. Go to c:\ups\uows.
2. Copy upsdb.
3. Paste and rename the copy of upsdb to upsdb_current date.
4. Open upsdb
5. Open table calBilling, and 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. Then, click the Delete button.
6. Open table calPackage, and 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. Then, click the Delete button.
7. Open table calShipment, and 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. Then, click the Delete button.
8. Open table calPkgAgent, and sort by column Sm_business_Name.
9. Keep records that start with INS and keep the two records with NATP (National Association of Tax Professionals). Delete the rest.
10. Close Access and open UPS. The database is ready to accept new records.