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.
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:- 'firstname.lastname@example.org'.
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:-
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.
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.
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.
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:-
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.
The following points should be noted:-
|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|
|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 ||Estimated Date [and time] of departure in ddmmmyy format|
|atd||7 ||Actual date [and time] of departure|
|eta||7 ||Estimated Date [and time] of arrival in ddmmmyy format|
|ata||7 ||Actual Date [and time] of arrival|
|pdd||7 ||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|
|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|
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
The spreadsheet should look something like this:-
|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.