UPS WorldShip integration

This article details the operation of the UPS WorldShip plugin. The UPS integration we use is to utilise the XML Auto Import feature of the UPS WorldShip application. See the UPS documents attached for more details on this.

The Process

The process works like this;

The plugin is actioned from shipments
This opens the UPSShipping.xml template
The placeholders are replaced using the data from the shipment
The resulting file is written to the UPS monitored folder
UPS Worldship actions this request and produces a .out file
The plugin waits for this file to be written. Opens it and recovers the tracking numbers.
The tracking information is written back to the shipment.

An example XML

<?xml version="1.0" encoding="windows-1252"?>
<OpenShipments xmlns="x-schema:OpenShipments.xdr">
  <OpenShipment ShipmentOption="SC" ProcessStatus="">
    <ShipTo>
      <CompanyOrName>##Shipments.DelName##</CompanyOrName>
      <Attention>##Shipments.DelName##</Attention>
      <Address1>##Shipments.DelAddress.line1##</Address1>
      <Address2>##Shipments.DelAddress.line2##</Address2>
      <CountryTerritory>##Shipments.DelCountryId##</CountryTerritory>
      <PostalCode>##Shipments.DelPostalCode##</PostalCode>
      <CityOrTown>##Shipments.DelCity##</CityOrTown>
      <StateProvinceCounty>##Shipments.DelRegion##</StateProvinceCounty>
      <Telephone>##Shipments.DelPhone##</Telephone>
    </ShipTo>
    <ShipmentInformation>
      <VoidIndicator />
      <ServiceType>##Custom.ServiceType##</ServiceType>
      <PackageType>Package</PackageType>
      <NumberOfPackages>##Shipments.NumberOfBoxes##</NumberOfPackages>
      <ShipmentActualWeight>##Shipments.Weight##</ShipmentActualWeight>
      <DescriptionOfGoods>Regular Shipment</DescriptionOfGoods>
      <Reference1>##Shipments.ShipmentId##</Reference1>
      <Reference2>##Shipments.SalesOrderId##</Reference2>
      <SpecialInstructionForShipment>##Shipments.Notes##</SpecialInstructionForShipment>
      <BillingOption>PP</BillingOption>
    </ShipmentInformation>
  </OpenShipment>
</OpenShipments>

The placeholders are defined by the syntax ##Shipments.DelPostalCode##

The ## indicates a placeholder.
The data is designated by Table.Column. Where table is the data table to get the data from and column is the column to get the data from.
Additionally, a third parameter .line1 will just get the first line of the resulting data. This is useful to deconstruct our address field. .line2 and .line 3 also work.
Also, .count may be used to get the row count of the source table. e.g. ##ShipmentBoxes.Count##

Custom Data

Not all available data may be available from the data tables handed to the plugin. In this instance the config of the plugin allows a bespoke stored procedure to be called.

<add key="CustomDataProcedure" value="bsp_UPSCustomData" />

If this key is present the stored procedure will be called and a data table returned. The stored procedure must follow the signature of the following example.

ALTER PROCEDURE [dbo].[bsp_UPSCustomData]
	@Shipment bigint
AS
BEGIN
	
	SET NOCOUNT ON;

	SELECT 	
		ISNULL(FreightMethods.CarrierIdentifier,'DEFAULT') AS [ServiceType]	
	FROM Shipments
	LEFT JOIN FreightMethods ON FreightMethods.FreightMethod = Shipments.FreightMethod
	WHERE Shipment = @Shipment

END

The resulting data may be used in the same way as the other data placeholder by using the table name ‘Custom’.

For example ##Custom.ServiceType## will retrieve the column ServiceType from the stored procedure data.

Special Macros

There are a few special macros to get to some other data

##USERNAME## - The username of the current user
##USERFULLNAME## - The full name of the current user
##USERFIRSTNAME## - The first name of the current user
##USERLASTNAME## - The last name of the current user

Placeholder Examples
##Shipments.DelAddress.line1##
##Shipments.DelAddress.line2##
##Shipments.DelRegion##
##Custom.ServiceType##
##ShipmentBoxes.Count##

The XML Template

Reference1 - is what appears on the UPS delivery note

BillingOption

PP = PrePaid (Paid by the shipper)
TP =Third Party
CB= Consignee Billed
REC= Receiver
SHP= Shipper or My Account (shipper number)

Config File

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
		<add key="XMLOutputFolder" value="C:\UPS\WSTD\ImpExp\XML Auto Import" />
		<add key="CustomDataProcedure" value="bsp_UPSCustomData" />
	</appSettings>
</configuration>

CustomDataProcedure - A stored procedure to get the custom data
XMLOutputFolder - The monitored folder defined in UPS Worldship

Tracking Data

The resulting tracking data from the .out file is written back to the DeliveryReference column of the Shipments table. If more than one tracking number is created (multiple packages) then the tracking numbers are concatenated and separated by a semicolon.

A Refresh result is returned if the operation is successful.

UPS WorldShip