AIR MANIFEST SYSTEM
DATA INTEGRATION METHODOLOGY
MANIFEST DATA
(Version 2.0)
Peter W. Goldberg
CREATE DATE
08/15/2003
LAST UPDATED
02/04/2004
WARNING: This is a preliminary specification. Changes will be necessary to accommodate US
Customs Air AMS business requirements.
02/04/2004:
I added the field ManifestDescriptionOfGoods to the ManifestHeader (MAN) Record Type. This field is 15 characters in length and is used in conjunction with the creation of an FHL. This field is a mandatory field within the FHL format and therefore must be included.
This document covers the rules associated with the receipt of external data from any source that is destined to be received, processed, and subsequently loaded into a SECTRA database. These rules are meant to provide a simple, straightforward method of data delivery and subsequent processing by our data integration technology.
In general, the most straightforward method for receiving and processing data is to have the customer send a multiple-record type, fixed length or delimited text file via Email or FTP. Upon receipt of such a file, we will utilize our Data Integration System (DIS), which will receive, process, and load the external data sources into our internal data stores.
File format rules are as follows:
Each file may be in one of three standard formats. The three formats are:
Delimited File
Quote Comma Delimited File
Fixed Length File
Each record type must include all relationships necessary to process the data without using any other record types to determine data ownership. For example, if you are sending a Manifest, you must specify the HouseBillOfLading in all Record Types.
Each field within a record type must exist at all times. If there is no value for a particular field, then that field must be accounted for via a delimiter, or in the case of a Fixed Length file, physically empty for the defined number of bytes that make up that field.
Each field within a record type must be left justified at all times, regardless of its internal or targeted field type (character, integer, float, real, money, etc.). This applies especially to Fixed Length Formatted Files.
The fixed length format does NOT have to be the same for all record types. It is only necessary that a particular record type have the exact same format as all other records that are defined with that particular record type.
Formatting rules for Dates and Numbers:
Fields that are defined as DATE are allotted 16 spaces. The DATE should be left justified within the allotted 16 spaces and should have the following format:
MM/DD/YYYY HH:MM
Where MM = Month
DD = Day
YYYY = Year
HH = Hours
MM = Minutes
Notice that there is a space between the Date value and the Time value.
If the Date is to be sent without a Time Value, then there should be no TIME value specified. Everything to the right of the DATE should be filled with SPACES.
Fields that are defined as NUMBERS (Integer, Real, Money, float) are allotted 14 spaces. The Numeric value should be left justified within the allotted 14 spaces. There should NEVER be a DOLLAR SIGN in any numeric value. There should NEVER be a COMMA in any numeric value. There are never any implied decimal places within the data, therefore, you must specify a decimal point where required.
File Delivery rules are as follows:
Files may be sent in one of two ways.
To send files via Email, we will set up an Email Address specifically dedicated to your data transfer requirements. Once the Email Address has been set up, you will be able to send your data via the Email Address provided. We recommend that you send data as Attachments within the Email.
You may wish to send your files via FTP. If you have an FTP Site at your location, we can log on to your FTP Site and retrieve the files. If you prefer, or if you do not have an FTP Site, then we can set up an FTP Script that will send files from your computer system to our FTP Site. If you choose to send (push) the data files to our site, we recommend that you purchase Launchpad ($30). Launchpad will allow you to schedule the execution of the FTP Script at specified intervals. This automated approach will simplify the data transfer and will not normally require human intervention.
The next section of this document describes the exact data formats required in order to send MANIFEST Information from your system to our system. These formats are required in order for us to properly populate our system with your data. Not all fields are required. The specification provided will give you all of the necessary details to complete a successful data transfer from your system to ours.
MANIFEST
HEADER
|
Column |
Field
Name |
Size |
Validation |
Rules |
Field
Type & Size |
|
1 |
Record
Type |
MAN |
Must
be MAN |
Required |
Char
(3) |
|
2 |
CorporateCode |
Char
(20) |
Alpha
Numeric |
Required |
Char
(20) |
|
3 |
AirlinePrefix |
Char
(3) |
Numeric
(exactly 3 numbers) LOOKUP: Valid
Carrier Prefix in RefCarrier Table. |
Required |
Char
(3) |
|
4 |
AWBSerialNumber |
Char
(8) |
Numeric
(exactly 8 numbers) |
Required |
Char
(8) |
|
5 |
HouseAirWayBill |
Char
(12) |
Alpha
Numeric |
Required |
VarChar
(12) |
|
6 |
PackageTrackingID |
Char
(100) |
Alpha Numeric |
|
VarChar
(100) |
|
7 |
AirportOrigin |
Char
(3) |
Alpha Numeric LOOKUP: Valid AirportCode in ValAirportCode
Table. |
Required |
Char
(3) |
|
8 |
AirportArrival |
Char
(3) |
Alpha Numeric LOOKUP: Valid AirportCode in ValAirportCode
Table. |
Required |
Char
(3) |
|
9 |
AirportPTPDestination |
Char
(3) |
Alpha
Numeric LOOKUP: Valid AirportCode in ValAirportCode Table. |
Optional |
Char
(3) |
|
10 |
AirportArrivalDate |
Char
(16) |
MM/DD/YYYY
HH:MM |
Optional |
Date |
|
11 |
AirportPTPDestinationDate |
Char
(16) |
MM/DD/YYYY
HH:MM |
Optional |
Date |
|
12 |
DescriptionOfGoods |
Char
(15) |
Alpha
Numeric |
Required |
Char
(15) |
|
13 |
WeightCode |
Char
(1) |
Alpha Numeric Default to K if not specified LOOKUP: Valid
WeightCode in RefWeightUnitCode Table. (K
or L) |
Required |
Char
(1) |
|
14 |
Importing
Carrier |
Char
(3) |
Alpha
Numeric (specify
either 2 character Airline Code or 3 character Airline Designator Code) LOOKUP:
(2 characters) Valid AirlineCode in RefAirlineCodes Table. LOOKUP:
(3 characters) Valid AirlineDesignatorCode in RefAirlineCodes Table. |
Required |
VarChar
(3) |
|
15 |
Flight
Number |
Char
(5) |
Alpha
Numeric |
Required |
VarChar
(5) |
|
16 |
AMSParticipationCode |
Char
(7) |
|
Required |
VarChar
(7) |
|
17 |
TransferAirportDestination |
Char
(3) |
Alpha
Numeric LOOKUP: Valid AirportCode in ValAirportCode Table. |
Optional |
Char
(3) |
|
18 |
TransferDomesticInternational |
Char
(1) |
|
Optional |
Char
(1) |
|
19 |
TransferOnwardCarrier |
Char
(4) |
|
Optional |
VarChar
(4) |
|
20 |
Transit
Control Number |
Char
(12) |
|
Optional |
VarChar
(12) |
|
21 |
CountryOfOrigin |
Char
(2) |
|
Required |
Char
(2) |
|
22 |
CurrencyCode |
Char
(3) |
Alpha Numeric LOOKUP: Valid
CurrencyCode in RefCurrencyCode Table. |
Required |
Char
(3) |
|
|
|
|
|
|
|
You may send as many Manifest Header Records as are needed.
MANIFEST PARTIES
|
Column |
Field
Name |
Size |
Validation |
Rules |
Field
Type & Size |
|
1 |
RecordType |
PRT |
|
Required |
Char
(3) |
|
2 |
CorporateCode |
Char
(20) |
Alpha
Numeric |
Required |
Char
(20) |
|
3 |
AirWayBill
Prefix |
Char
(3) |
Numeric
(exactly 3 numbers) |
Required |
Char
(3) |
|
4 |
AirWayBill
Number |
Char
(8) |
Numeric
(exactly 8 numbers) LOOKUP: Valid
Carrier Prefix in RefCarrier Table. |
Required |
Char
(8) |
|
5 |
HouseAirWayBill |
Char
(8) |
Alpha
Numeric |
Required |
Char
(12) |
|
6 |
PartyType |
Char
(2) |
AlphaNumeric IF
Import Forwarder EF
Export Forwarder SH
Shipper CO
Consignee |
Required Required Required Required |
Char
(2) |
|
7 |
CompanyCode |
Char
(10) |
Alpha
Numeric |
|
Char
(10) |
|
8 |
PartyName |
Char
(35) |
Alpha
Numeric |
Required |
Char
(100) |
|
9 |
Address1 |
Char
(35) |
Alpha
Numeric |
Required |
Char
(50) |
|
10 |
City |
Char
(17) |
Alpha
Numeric |
Required |
Char
(17) |
|
11 |
State |
Char
(9) |
Alpha
Numeric |
Optional |
Char
(9) |
|
12 |
Country |
Char
(2) |