DATA INTEGRATION METHODOLOGY

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:

DATES

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 MM:HH

Where MM = Month

DD = Day

YYYY = Year

MM = Minutes

HH = Hours

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.

NUMBERS

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.

EMAIL DELIVERY

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.

FTP DELIVERY

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.

MANIFEST DATA TRANSFER FORMATS

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

DATA EXTRACT LAYOUTS

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)	AlphaNumeric	Required	Char (20)
3	HouseBillOfLading	Char (12)	AlphaNumeric	Required	Char (12)
4	CarrierCode	Char (4)	AlphaNumeric LOOKUP: Valid CarrierCode in RefSCAC Table.	Required	Char (4)
5	MOT	Char (2)	AlphaNumeric LOOKUP: Valid MOTCode in RefMOT Table.	Required	Char (2)
6	VesselName	Char (23)	AlphaNumeric LOOKUP: Valid VesselName in RefVessel Table.	Required	Char (23)
7	VoyageNumber	Char (5)	AlphaNumeric	Required	Char (10)
8	VesselFlagCountry	Char (2)	AlphaNumeric LOOKUP: Valid CountryCode in RefCountry Table.	Required	Char (2)
9	LloydsCode	Char (7)	Numeric LOOKUP: Valid LloydsCode in RefVessel Table.	Required	Char (7)
10	EntryPortCode	Char (4)	Numeric LOOKUP: Valid EntryPortCode in RefUSDistrictPort Table.	Required	Char (4)
11	PortOfLadingCode	Char (5)	Numeric LOOKUP: Valid ForeignPortCode in RefForeignPort Table.	Required	Char (5)
12	ETAAtEntryPort	Char (16)	MM/DD/YY HH:MM	Required	Date

13	QuantityUnits	Char (3)	AlphaNumeric LOOKUP: Valid UnitCode in RefUnitCode Table.	Required	Char (5)
14	WeightUnits	Char (1)	AlphaNumeric LOOKUP: Valid WeightUnitCode in RefWeightUnitCode Table.	Required	Char (2)
15	Volume	Char (14)	Numeric		Real
16	VolumeUnit	Char (1)	AlphaNumeric LOOKUP: Valid VolumeUnitCode in RefVolumeUnitCode Table.	Required (If Volume is Specified)	Char (2)
17	PlaceOfReceipt	Char (17)	AlphaNumeric		Char (17)
18	SecondNotifyParty2	Char (4)	AlphaNumeric		Char (4)
19	LastForeignPort	Char (5)	Numeric LOOKUP: Valid ForeignPortCode in RefForeignPort Table.	Required	Char (5)
20	LoadingDate	Char (16)	MM/DD/YY HH:MM	Required	Date
21	LoadingDateTimeZone	Char (4)	AlphaNumeric LOOKUP: Valid TimeZoneCode in RefTimeZone Table.	Required	Char (4)
22	AirOcean	Char (1)	AlphaNumeric	Required	Char (1)
23	MBLNumber	Char (16)	AlphaNumeric		Char (16)
24	BookingNumber	Char (30)	AlphaNumeric		Char (30)

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)	AlphaNumeric	Required	Char (20)
3	HouseBillOfLading	Char (12)	AlphaNumeric	Required	Char (12)
4	PartyType	Char (2)	AlphaNumeric SH – Shipper CO – Consignee NP – Notify Party IF – Import Forwarder EF – Export Forwarder	Required Required Required Required Required	Char (2)
5	CompanyCode	Char (10)	AlphaNumeric		Char (10)
6	PartyName	Char (35)	AlphaNumeric	Required	Char (100)
7	CareOfName	Char (35)	AlphaNumeric		Char (50)
8	Address1	Char (35)	AlphaNumeric	Required	Char (50)
9	Address2	Char (35)	AlphaNumeric		Char (50)
10	Address3	Char (35)	AlphaNumeric		Char (50)
11	Country	Char (35)	AlphaNumeric		Char (50)
12	PhoneNumber	Char (25)	AlphaNumeric		Char (25)
13	ReferenceNumber	Char (20)	AlphaNumeric		Char (20)
14	RPReferenceNumber	Char (20)	AlphaNumeric		Char (20)

You may send up to FIVE (5) Party Records for each Manifest. The SH, CO, and NP are required by Customs. The IF and EF are required by SECTRA so that the appropriate Parties to the transaction will be able to access the data within the Web Site.

MANIFEST INBOND

Column