FrontLine Shipping Integration Notes

FrontLine Shipping Integration with UPS WorldShip and FedEx Ship Manager

WorldShip and Ship Manager have their own databases and provide no access to these. They do, however, provide some kind of third party integration facility.

Overview

UPS WorldShip provides independent import and export facilities. To make the import and export work together on a shipment-by-shipment basis the setup is:

Import must be set to "Keyed Import" with the import map selected from the menu: Import/Export Data -- Keyed Import -- (select import map name)
Export must be set to automatically export after each shipment, from the menu: Import/Export Data -- Automatic Export After Processing Shipment -- (select export map name)

FedEx Ship Manager provides a FedEx Integration Assistant that can combine import and export in one integration profile. There are equivalent options that must be turned on.

Import: Single Edit after I enter a lookup value.
Export: Each time a shipment is completed / deleted.

What both of these setups do is have WorldShip or Ship Manager open a prompt where the user enters a value provided by FrontLine to retrieve the shipment, then export some information after processing the shipment.

When the user chooses to "Send to WorldShip" or "Send to Ship Manager" from FrontLine shipping, FrontLine creates a shipment record and opens a waiting window that shows the shipment key and provides a button that copies this key to the clipboard, sends input focus to the WorldShip / Ship Manager window, and simulates a user entering the key. Note that input focus and simulation only work if FrontLine and WorldShip / Ship Manager are running on the same desktop.

Where are the steps below performed?

The setup steps within WorldShip and Ship Manager are done within each instance of WorldShip and Ship Manager, on the PC they run on. An ODBC DSN is also created per PC where WorldShip or Ship Manager integrates with FrontLine shipping.

All the creation of tables, views, and users in the FrontLine database can be done from any PC connected to the database. The database vendor's management tools can be used, such as pgAdmin or SQL Server Management Studio.

Database setup

ODBC

Both WorldShip and Ship Manager can import and export from/to ODBC data sources. FrontLine shipping integration is designed to work through the database rather than through text files, so on whichever PCs WorldShip or Ship Manager will run integrated with FrontLine an appropriate ODBC DSN must be created. Let us name this DSN flship and make it a System DSN. If this DSN does not appear in WorldShip then try making a User DSN instead.

Security

The login used by WorldShip or Ship Manager through ODBC should have very limited access to the FrontLine database. It should not have normal FL_Users access. It needs only read (select) access to the appropriate view (SHIP_UPS or SHIP_FDX) and full access to the SHIPLOG table. The implementation of this security is partly dependent on the db server type (described below) and partly implemented in the object creation scripts.

ODBC with MSSQL

Create a DSN using the Microsoft SQL Server driver and appropriate parameters. We do not want the user prompted for credentials every time WorldShip or Ship Manager opens the connection. One solution is to create a special limited-access login and db user and have WorldShip / Ship Manager remember these credentials. Another is to authenticate using Windows network authentication. To implement the latter there are two steps:

In the Windows user accounts (domain or active directory) create a group named something like "Shippers" and make the applicable users members.
In MSSQL Management Studio, a database user must be created in the FrontLine database that is associated with the Windows group rather than an SQL Server login.
Example in SQL Server Management Studio 2005:
Databases, FLFL, Security, Users, New User...
User name = "flshipper" Login name = "domain\Shippers"
Do not make this user a member of FL_Users.

ODBC with PostgreSQL

Download the PostgreSQL ODBC driver from our site here, or get the latest from the official site.
Create a DSN using the PostgreSQL ANSI driver and appropriate parameters.
We probably do not want the user prompted for credentials every time WorldShip or Ship Manager opens the connection.
One solution is to authenticate using a Postgres login created specifically for shipping, and thus assigned the needed permissions.
The login credentials for this limited-access user can be saved in the ODBC DSN. Let us call this special user "flshipper" in scripts below.
You can create "flshipper" in pgAdmin as an ordinary user with access to the FrontLine db.

CREATE ROLE flshipper LOGIN PASSWORD 'shipping';

TABLES

The SHIPMENTS table added to FrontLine in 6.1.00x is required. If it is not in the database, create it from the FrontLine Db Console.

There is a new SHIPLOG table for WorldShip / Ship Manager to export information to:

-- The log table for WorldShip and Ship Manager is based on ShipRef instead of ShipID:

CREATE TABLE SHIPLOG (
--pgSQL LogID int serial,
--MSSQL LogID int identity,
ShipRef varchar(32),
VoidIndicator char(4) DEFAULT 'SHP', -- merge
ShipID varchar(32) NULL,
ServiceType varchar(32) NULL,
ShipDate varchar(20) NULL, -- merge
TotalCharge varchar(20) NULL,
BillTransportationTo char(4) NULL,
Weight varchar(20),
Height varchar(20) NULL,
Length varchar(20) NULL,
Width varchar(20) NULL,
--pgSQL LogDate timestamp
--MSSQL LogDate smalldatetime
DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT PK_SHIPLOG PRIMARY KEY NONCLUSTERED (LogID));

GRANT SELECT,INSERT,UPDATE ON SHIPLOG TO flshipper;
GRANT SELECT,DELETE ON SHIPLOG TO FL_Users;
GRANT SELECT,UPDATE ON SHIPMENTS TO flshipper;

WorldShip and Ship Manager simply add records to the SHIPLOG table. You may notice that all the fields in SHIPLOG are basic text (varchar). This is to avoid export complications and limitations in WorldShip / Ship Manager.

The connection between SHIPLOG and SHIPMENTS is made via database triggers.

-- PostgreSQL Trigger creation

CREATE OR REPLACE FUNCTION SHIPLOG_POST() RETURNS trigger AS
$$ --v1.1
BEGIN
IF NEW.ShipID ~ '^[0-9]{1,9}$' THEN
UPDATE SHIPMENTS SET
   ShipRef=CASE WHEN NEW.VoidIndicator ~ '^[DY]' THEN NEW.ShipRef||' VOIDED' ELSE NEW.ShipRef END,
   ServiceType=NEW.ServiceType,
   ShipDate=COALESCE(NEW.ShipDate::date,ShipDate),
   TrackDate=NEW.LogDate,
   BillTransportationTo=NEW.BillTransportationTo,
   Weight=CASE WHEN NEW.Weight ~ E'^[0-9]+\.?[0-9]*$' THEN NEW.Weight::float ELSE Weight END,
   Height=CASE WHEN NEW.Height ~ E'^[0-9]+\.?[0-9]*$' THEN NEW.Height::float ELSE Height END,
   Length=CASE WHEN NEW.Length ~ E'^[0-9]+\.?[0-9]*$' THEN NEW.Length::float ELSE Length END,
   Width=CASE WHEN NEW.Width ~ E'^[0-9]+\.?[0-9]*$' THEN NEW.Width::float ELSE Width END,
   ShipCharge=CASE WHEN NEW.TotalCharge ~ E'^[0-9]+\.?[0-9]*$' THEN NEW.TotalCharge::float ELSE ShipCharge END
WHERE ShipID=NEW.ShipID::int;
END IF;
RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER TR_SHIPLOG
AFTER INSERT OR UPDATE ON SHIPLOG
FOR EACH ROW EXECUTE PROCEDURE SHIPLOG_POST();

-- MSSQL Trigger creation

CREATE TRIGGER TR_SHIPLOG ON SHIPLOG FOR INSERT,UPDATE
AS --v1.1
UPDATE SHIPMENTS SET
ShipRef=CASE WHEN i.VoidIndicator LIKE 'D%' OR i.VoidIndicator LIKE 'Y%' THEN i.ShipRef+' VOIDED' ELSE i.ShipRef END,
ServiceType=i.ServiceType,
ShipDate=CASE WHEN IsDate(i.ShipDate)=1 THEN i.ShipDate ELSE SHIPMENTS.ShipDate END,
TrackDate=i.LogDate,
BillTransportationTo=i.BillTransportationTo,
Weight=CASE WHEN IsNumeric(i.Weight)=1 THEN i.Weight ELSE SHIPMENTS.Weight END,
Height=CASE WHEN IsNumeric(i.Height)=1 THEN i.Height ELSE SHIPMENTS.Height END,
Length=CASE WHEN IsNumeric(i.Length)=1 THEN i.Length ELSE SHIPMENTS.Length END,
Width=CASE WHEN IsNumeric(i.Width)=1 THEN i.Width ELSE SHIPMENTS.Width END,
ShipCharge=CASE WHEN IsNumeric(i.TotalCharge)=1 THEN i.TotalCharge ELSE SHIPMENTS.ShipCharge END
FROM SHIPMENTS,
(SELECT ShipRef,VoidIndicator,ShipID,ServiceType,BillTransportationTo,
CASE WHEN ShipDate LIKE '%/%' OR ShipDate LIKE '%.%' THEN ShipDate
ELSE STUFF(STUFF(STUFF(STUFF(STUFF(ShipDate,5,0,'.'),8,0,'.'),11,0,' '),14,0,':'),17,0,':') END AS ShipDate,
TotalCharge,Weight,Height,Length,Width,LogDate FROM inserted WHERE IsNumeric(ShipID)=1 ) i
WHERE CAST(i.ShipID AS int)=SHIPMENTS.ShipID

You can see there is some implicit and some explicit data conversion taking place, and some data validation to prevent SQL errors.

When the trigger updates the SHIPMENTS record, FrontLine finds the SHIPMENTS.ShipRef (Tracking Number) and displays it in the waiting window for the user to acknowledge and proceed. When the user chooses to proceed, FrontLine completes its shipping process for the shipment.

VIEWS

The import facilities in WorldShip and Ship Manager do not allow writing custom SQL queries to join tables, so we have to create an appropriate view for each.

-- Create the view for Ship Manager to query.
-- Uncomment the two pgSQL or msSQL lines depending on server type.
-- Uncomment the /*WITH(NOLOCK)*/ for MSSQL, and remove them altogether for PostgreSQL:

CREATE VIEW SHIP_FDX AS SELECT S.ShipID,S.EntityID,S.AddressID,S.ShipVia,S.ShipRef,
S.ServiceType,S.DeliveryDate,S.ShipDate,S.Weight,S.Height,S.Length,S.Width,
A.Name1,A.Name2,A.Address1,A.Address2,A.Address3,
A.City,A.State,A.Zip,A.Country,
CASE WHEN COALESCE(A.NotifyVia,0)&1=0 THEN 'N' ELSE 'Y' END AS ResidentialIndicator,
CASE WHEN
--pgSQL COALESCE(char_length(BCU.ShipAccount),0)<6 OR BCU.ShipAccount ILIKE 'COLLECT'
--msSQL COALESCE(DATALENGTH(BCU.ShipAccount),0)<6 OR BCU.ShipAccount LIKE 'COLLECT'
THEN NULL ELSE BCU.ShipAccount END AS FedExAccount,
CASE WHEN
--pgSQL COALESCE(char_length(BCU.ShipAccount),0)>=6 THEN CASE WHEN BCU.ShipAccount ILIKE 'COLLECT' THEN 'COL' ELSE 'TP' END
--msSQL COALESCE(DATALENGTH(BCU.ShipAccount),0)>=6 THEN CASE WHEN BCU.ShipAccount LIKE 'COLLECT' THEN 'COL' ELSE 'TP' END
ELSE 'SHP' END AS FedExBillTo,
S.BillTransportationTo,
S.OrderID,
E.CustomerNumber,
COALESCE(C.Name,A.Name1)AS ContactName,
COALESCE(C.Phone1,A.Phone1)AS ContactPhone,
COALESCE(C.Phone3,A.Phone3)AS ContactFax,
COALESCE(C.EMail,A.EMail)AS ContactEMail
FROM SHIPMENTS S /*WITH(NOLOCK)*/ LEFT JOIN CUSTOMERS E /*WITH(NOLOCK)*/ ON E.CustomerID=S.EntityID
JOIN ADDRESSES A /*WITH(NOLOCK)*/ ON A.AddressID=S.AddressID
LEFT JOIN CONTACTS C /*WITH(NOLOCK)*/ ON C.ContactID=S.ContactID AND C.AddressID=S.AddressID
LEFT JOIN CUSTOMERS BCU /*WITH(NOLOCK)*/ ON BCU.CustomerID=S.EntityID AND BCU.ShipAccount IS NOT NULL
WHERE S.ServiceType LIKE 'FDX%' AND S.ShipRef IS NULL;

GRANT SELECT ON SHIP_FDX TO flshipper;
GRANT SELECT ON SHIP_FDX TO FL_Users;

-- Create the view for WorldShip to query.
-- Remove the six WITH(NOLOCK) hints altogether for PostgreSQL:

CREATE VIEW SHIP_UPS AS SELECT S.ShipID,S.EntityID,S.AddressID,S.ShipVia,S.ShipRef,
S.ServiceType,S.DeliveryDate,S.ShipDate,S.Weight,S.Height,S.Length,S.Width,
A.Name1,A.Name2,A.Address1,A.Address2,A.Address3,
A.City,A.State,A.Zip,A.Country,
CASE WHEN COALESCE(A.NotifyVia,0)&1=0 THEN 'N' ELSE 'Y' END AS ResidentialIndicator,
SBA.Name1 AS TP_Name1,SBA.Name2 AS TP_Name2,
SBA.Address1 AS TP_Address1,SBA.Address2 AS TP_Address2,SBA.Address3 AS TP_Address3,
SBA.City AS TP_City,SBA.State AS TP_State,SBA.Zip AS TP_Zip,SBA.Country AS TP_Country,
BCU.ShipAccount AS TP_Account,BCU.CustomerNumber AS TP_Customer,
SUBSTRING(S.ServiceType,5,20)AS ServiceName, -- removes the "UPS " prefix
S.BillTransportationTo,
'Y' AS ShipOptionY,
S.OrderID,
E.CustomerNumber,
COALESCE(C.Name,A.Name1)AS ContactName,
COALESCE(C.Phone1,A.Phone1)AS ContactPhone,
COALESCE(C.Phone3,A.Phone3)AS ContactFax,
COALESCE(C.EMail,A.EMail)AS ContactEMail
FROM SHIPMENTS S WITH(NOLOCK) LEFT JOIN CUSTOMERS E WITH(NOLOCK) ON E.CustomerID=S.EntityID
JOIN ADDRESSES A WITH(NOLOCK) ON A.AddressID=S.AddressID
LEFT JOIN CONTACTS C WITH(NOLOCK) ON C.ContactID=S.ContactID AND C.AddressID=S.AddressID
LEFT JOIN CUSTOMERS BCU WITH(NOLOCK) ON BCU.CustomerID=S.EntityID AND BCU.ShipAccount IS NOT NULL
LEFT JOIN ADDRESSES SBA WITH(NOLOCK) ON SBA.AddressID=BCU.BillTo
WHERE S.ServiceType LIKE 'UPS%' AND S.ShipRef IS NULL;

GRANT SELECT ON SHIP_UPS TO flshipper;
GRANT SELECT ON SHIP_UPS TO FL_Users;

Execute the GRANT statements separately from the CREATE VIEW above.

FedEx Integration Assistant - some details on the setup.

Both Import and Export

ODBC Import for Ship Manager:

Do you print labels one at a time? Yes
Do you use a unique name or code to look up each package? Yes
Do you need to edit shipping information before printing the label? Yes

above = Single Edit

How do you want to lookup and import information?
A: After I enter a lookup value

Ship Manager field mapping for "Shipment Data" from the view above:

Recipient - Address:

 Address 1 = Address1
 Address 2 = Address2
 City = City
 Company = Name1
 Contact Name = ContactName
 Country = Country (default US)
 Location Number = CustomerNumber
 Phone = ContactPhone
 Postal Code = Zip
 State/Province = State
 Service Type = ServiceName

Package - Miscellaneous:

 Bill Transportation To = FedExBillTo  [requires conversion]
 Payor Account Number = FedExAccount
 Residential Delivery Flag = ResidentialIndicator
 Service Type = ServiceType  [requires conversion]
 Shipment ID = ShipID  [critical]
 Weight = Weight  [this is not an input yet, but here either a Default may be set or "Read Scale" chosen]

Click the Select Index button below and choose the ShipID field.

Conversion page requires conversion of Service Type between FedEx and FrontLine "Key Code" strings in ShipVia list.
I recommend "Key Code" values starting with FDX for simplicity.
If they do not then FrontLine prefaces SHIPMENTS.ServiceType with FDX followed by the "Key Code"

Conversion - Service Type:

 FrontLine  FedEx
      FDX1  Priority Overnight
      FDX2  Standard Overnight
      FDX3  2 Day
      FDX4  2 Day AM
      FDX5  First Overnight
      FDX6  FedEx Economy (non-US)
      FDX7  Express Saver
      FDXR  Ground
      FDXH  Ground Home Delivery
     FDXF0  First Overnight Freight
     FDXF1  1 Day Freight
     FDXF2  2 Day Freight
     FDXF3  3 Day Freight
     FDXSP  SmartPost
     FDXSB  SmartPost Bound Printed Matter
     FDXSM  SmartPost Media

Conversion - Residential Delivery Flag:

 FrontLine  FedEx
         N  No/False
         Y  Yes/True

Conversion - Bill Transportation To:

 FrontLine  FedEx
       SHP  Bill Sender
       REC  Bill Recipient
        TP  Bill Third Party
       COL  FedEx Ground Collect
       SPP  SmartPost Prepaid

ODBC Export for Ship Manager:

When do you want to export to your data source?
A: Each time a shipment is completed.
Also export each time a package is deleted.

How do you want to update your data source?
A: Insert a new record
Set flag in existing record to indicate deleted shipment.
(FL scripts will work with the default 'DEL' indicator)
"Include manual shipments" Yes, why not, they simply will not update SHIPMENTS.

What types of information do you want to export?
Tracking Numbers: Tracking Number
Charges: Total Customer Charge
Package: Height, Length, Width, Service Type, Ship Date, Shipment ID [critical], Weight

Match FedEx Ship Manager fields with your fields.
All fields map output to SHIPLOG table:

 Tracking Number = ShipRef
 Total Customer Charge = TotalCharge
 Bill Transportation To = BillTransportationTo  [requires conversion]
 Height = Height
 Length = Length
 Width = Width
 Service Type = ServiceType
 Ship Date = ShipDate
 Shipment ID = ShipID  [without this the SHIPMENTS table in FL will not update]
 Weight = Weight
 Delete Indicator = VoidIndicator

Convert FedEx Ship Manager fields with your fields.
Service Type: Enter conversion texts that look just like the FedEx texts.
Bill Transportation To: Enter conversion texts as above for Import.

Save the profile with name "FrontLine" or whatever is desired.

FedEx Ship Manager allows you to backup and restore various kinds of information. One of these is Integration Settings. For your convenience, you can download a backup of the integration profile outlined above from here. It is in a zip file to preserve a folder structure. After unzipping the contents, run this from the Ship Manager menu:

 -- Databases -- File Maintenance -- Restore:
 Restore selected databases: Integration Settings

and choose the top level folder you unzipped.

UPS WorldShip Import/Export Maps - some details on the setup.

Select from the menu: Import/Export Data -- Edit Maps (bottom entry)

Import Map links:

Let us name the Map "FLtoShip", be sure to indicate in the Drop-Down that you are creating a Shipment map. Click the Create button to begin the mapping.

Using SHIP_UPS as the ODBC table input, Define Primary Key on ShipID, and map it to Package - Reference 3
Reference 3 is used because Reference 1 and Reference 2 can be edited by the user in WorldShip.

UPS Package:

 ShipID = Reference 3

UPS Ship To:

 Name1          = Company or Name
 CustomerNumber = CustomerID
 ContactName    = ContactName or Attention
 ContactEMail   = Email Address
 ContactPhone   = Telephone
 ContactFax     = Fax Number
 Address1    = Address 1
 Address2    = Address 2
 Address3    = Address 3
 City        = City
 State       = State
 Zip         = PostalCode
 Country     = Country
 Residential Indicator = ResidentialIndicator

UPS Shipment Information: See UPS Service Codes for the FrontLine ShipVia list.

 ServiceName          = Service Type
 BillTransportationTo = Bill Transportation To

UPS Third Party:

 TP_Name1      = Company or Name
 TP_Name2      = Attention
 TP_Customer   = CustomerID
 TP_Address1   = Address 1
 TP_Address2   = Address 2
 TP_Address3   = Address 3
 TP_City       = City
 TP_State      = State
 TP_Zip        = PostalCode
 TP_Country    = Country
 TP_Account    = UPS Account Number

Export Map links:

Let us name the Map "FLShipped" which is also a Shipment map. All fields map output to the SHIPLOG table. Field numbers are from WorldShip 2011.

UPS Shipment Information:

 1 = VoidIndicator
 2 = ServiceType
 6 Lead Tracking Number = ShipRef
 9 = Weight
 15 = BillTransportationTo
 20 = ShipDate
 172 = TotalCharge
 259 = Length
 260 = Width
 261 = Height

UPS Package:

20: Reference 3 = ShipID

Here is a revised field reference page UPS has put up.