Clipper Interface

Introduction
Clipper is an Internet based Shipment Tracking System that uses e-mail messages for the transmission of information to the central database. E-mail has three big advantages over other means of transmission:-

When Clipper receives an e-mail message containing shipment information it is processed immediately and automatically. For this to work it is necessary for the data to be in some sort of recognisable form and Clipper accepts a number of different formats. The purpose of this document is to specify these formats so that customers can modify their operational systems to automatically create the messages.

Message Overview
Each message must start with the customer's userid and password (plus any special handling identifier) on the first line. All subsequent lines are data lines and it is imperative (particularly for the spreadsheet style) that all of the data intended for the line is on the one line ie no text-wrap occurs. Some e-mail systems may only allow lines up to a certain length. In these cases the data should be MIME encoded to either the 'quoted-printable' or 'base64' standard. Corunna Systems can advise if any difficulties are experienced.

Once completed the message is sent to:- 'clipper@corunna.com'.

Format Overview
One of either of two different standard formats, each using key-words, may be used or, after discussion with Corunna Systems, fixed length reports that contain the required information may also be used. The two standard formats are:-

in-line - where each line consists of a key-word followed by the data item
spreadsheet style - in which the key-words, one per column, are on the first line with each subsequent line containing the data for the complete shipment.

If using the spreadsheet style the columns must be separated by either a comma (comma-delimited files) or a tab (tab-delimited files). Any PC spreadsheet application such as Microsoft Excel can create either of these formats but it is important that, if saving to a comma-delimited format, commas are not used within the text itself. With this style it is recommended that all columns (even numeric ones) should be formatted as text columns. Corunna systems can provide a spreadsheet template if required.

Automating Updates
As far as possible data from the operational system should be used to populate Clipper so as to keep re-keying to a minimum. As information is sent to Clipper by e-mail it is necessary that your software be able to create (and transmit) Internet e-mail messages; without this capability automatic updating is impossible.

There are two ways in which data can be generated for Clipper, periodically and whenever a shipment is changed.

Periodic
This involves a background process periodically trawling through the database of shipments and extracting details of all of those that have changed since the last run. This background process can run as often as required but, typically, for oceanfreight will be once a day and for airfreight four times a day. Once the file of updates has been created it is then e-mailed to Clipper.

On Change
This involves the operational software creating an e-mail message each time certain changes are made to the shipment. The key to this method of update is to identify the tasks within your own application that are to cause an update and to modify those routines. For example most software has the following options:-

  1. Create a new booking
  2. Print a Bill of Lading
  3. Send a pre-alert

Modifying these functions will ensure that Clipper receives the new booking when it is made, flight/vessel sailing details are sent in good time and the aircraft/ship departure date is entered. All other information (arrival and POD details) can be added 'as and when' by an 'ad hoc' e-mail message.

Of course, a combination of these two methods can be used as well.

Key-word Definitions
The following points should be noted:-

  1. Data can be entered in either upper or lower case. Clipper converts everything to upper case.
  2. Some fields (eg rmk) are multi-item fields with each item being separated by a double colon (::). Sub-fields that are optional are enclosed in square brackets ([]).
  3. All dates should be entered in the format ddmmmyy eg 01jan99. Separators such as / or - are accepable. Ie 01jan99 and 01-jan-99 are both valid as is 01-jan99.
  4. Times should be entered in the form hh:mm eg 12:35 (including the colon(:)). They should be separated from the date by a single space.
  5. Weight and volume can be entered as whole numbers or with decimals. Eg wgt 3, wgt 3.00, wgt 3.65 are all equivalent.
  6. The key-word hbl must be the first item of a new shipment, all other key-words are optional and may appear in any order.
  7. In the 'in-line' method the key-word and data must be separated by a single space.
  8. Lines must not wrap around.
  9. Some key-words such as rmk may be repeated many times for a particular shipment. This is only applicable for the 'in-line' method. Exceptionally, multiple references, each separated by a space, may be entered in a single column in the 'spreadsheet style' method.

 Key-word  Size  Description  Remarks
hbl 20 Unique Shipment Reference # (typically the House Bill #)
mbl 20 Consolidation, or Master Bill, Reference #
shp 20 Shipper's Name Account Name
con 20 Consignee Name Individual's Name
dsc 30 Description of Goods
pcs 5 Number of pieces
wgt 8 Weight Size includes decimal point
vol 9 Volume Size includes decimal point
ogn 15 Point of Origin
ptd 15 Port of Lading
pta 15 Port of Discharge
des 15 Final Destination

flt

10
Flight Number For Airfreight
vsl 25 Vessel name & voyage number For Oceanfreight
bkd 7 Date booked in the format ddmmmyy Defaults to date shipment record created
etd 7 [5] Estimated Date [and time] of departure in ddmmmyy format
atd 7 [5] Actual date [and time] of departure
eta 7 [5] Estimated Date [and time] of arrival in ddmmmyy format
ata 7 [5] Actual Date [and time] of arrival
pdd 7 [5] Date [and time] of delivery
pod 20 POD details (eg POD Number)
sig 20 Name of POD signatory
ref [15::]20 [Reference name::]Reference value Repeating
rmk [3::]75 [Status Code::]Remark Repeating
cmt 3::75 Security level::Forwarder comment Repeating
gen 15::40 Additional Field name::Value Repeating
exp 20 Exporting Agent code
imp 20 Importing Agent code

 

Examples
In the following examples we are assuming that the forwarder's userid is 'titan' and his password 'phalanx'. He is also responsible for updating all stages of his shipments, ie there is no overseas agent input. The text in blue is what goes into the e-mail message.


1. - In-line
To create one new shipment (650345) and update an existing one (650332) to a delivered status

titan phalanx
hbl 650345
shp acme computing
ogn bristol, uk
con zyxel inc
des detroit, usa
pcs 200
wgt 7.65
vol 3.82
dsc computer software manuals
ref invoice no::e9685/75
ref order no::396
rmk Should be kept in dry conditions
gen value::2000 gbp
hbl 650332
pdd 14jun99 15:40
pod 7686
sig mr eric einstein

To then update this shipment (650345) with vessel and sailing details:-

titan phalanx
hbl 650345
mbl 61029f
vsl hanjin hamburg
ptd sou
pta det
etd 18-jun-99
eta 28jun99

Then to update the shipment with the actual sailing details:-

titan phalanx
hbl 650345
atd 18jun99 18:25
ref container::hlcu89577956
gen seal::687573
ref lines bill::57435

Then when it arrives in Detroit:-

titan phalanx
hbl 650345
ata 29jun99
rmk Delayed for 1 day due to missed tide

And, finally, when it is delivered to the client:-

titan phalanx
hbl 650345
pdd 02jul99 16:40
sig sam smith

2. - Spreadsheet Style
The spreadsheet should look something like this:-

hbl shp ogn con des pcs wgt vol dsc ref rmk pdd pod sig
650345 acme... bristol zyxel... detro... 200 7.65 3.82 compu... inv.. ord.. Shoul...
650332 14jun99 .. 7686 mr eric...

Once this is saved as a comma delimited file, then the e-mail message will look like this:-

titan phalanx
hbl,shp,ogn,con,des,pcs,wgt,vol,dsc,ref,rmk,pdd,pod,sig
650345,acme...,bristol,zyxel...,detro...,200,3.82,compu...,inv... ord...,Shoul...,,,
650332,,,,,,,,,,,14jun99 15:40,7686,mr eric einstein

Typically, however, when using the spreadsheet style all columns including those to be completed at a later stage will also be included and updated as the shipment progresses. In which case the saved file might look like this:-

titan phalanx
hbl,shp,ogn,con,des,pcs,wgt,vol,dsc,mbl,vsl,pta,ptd,etd,atd,eta,ata,ref,rmk,pdd,pod,sig
650345,acme...,bristol,zyxel...,detro...,200,3.82,compu...,,,,,,,,,inv... ord...,Shoul...,,,
650332,,,,,,,,,,,,,,,,,,,14jun99 15:40,7686,mr eric einstein

which then gets updated when the vessel details are known:-

titan phalanx
hbl,shp,ogn,con,des,pcs,wgt,vol,dsc,mbl,vsl,pta,ptd,etd,atd,eta,ata,ref,rmk,pdd,pod,sig
650345,acme...,bristol,zyxel...,detro...,200,3.82,compu...,61029f,hanji...,sou,det,18jun99,,28jun99,,inv... ord...,Shoul...,,,

and then gets updated again at each change of status, Clipper does not mind that some information is repeated.