CUSTOMER DATA TRANSFER INTEGRATION METHODOLOGY

 

STANDARD DATA INTEGRATION DOCUMENT

 

 PURCHASE ORDERS

 OCEAN SHIPMENTS

AIR SHIPMENTS

 

Peter W. Goldberg

 

CREATE DATE

06/16/2001

 

LAST UPDATED

03/17/2003

 

MODIFICATIONS

 

01/03/2002

Added FDAReleaseDate to Ocean House Record Type (OHH)

Added FDAReleaseDate to Air House Record Type (AHH)

 

04/12/2002

Modified Phone Number field within PURCHASE ORDER Layout, Record Type “POH” from 12 characters to 20 characters.

 

07/03/2002

  1. ITNUMBER field changed from 11 characters to 12 characters in:

q       Ocean Shipment IT Details

q       Ocean Shipment IT Dispatch Details

q       Ocean Shipment IT House

q       Air Shipment IT Details

q       Air Shipment IT Dispatch Details

q       Air Shipment IT House.

 

  1. Removed unused fields from Ocean House Details:  Length, Width, Height, Pieces Count, Marks
  2. ContainerSize field changed from 3 to 7 characters in Ocean House Containers.
  3. GrossWeight and Volume added to Ocean House Containers.

 

08/08/2002

Modified the POHeader (POH), PODetail (POD), and POParties (POP) Record Types.  Added CorporateCode and ConsigneeCode to each of the specified Record Types.

 

10/17/2002

Added Record Types CIH (Commercial Invoice), CID (Commercial Invoice Detail), and CIP (Commercial Invoice Parties) for Commercial Invoice Data Upload.

 

Added Record Types OCH (Ocean House Commercial Invoice), OCD (Ocean House Commercial Invoice Detail), and OCP (Ocean House Commercial Invoice Parties) for Ocean Shipments.

 

Added Record Types ACH (Air House Commercial Invoice), ACD (Air House Commercial Invoice Detail), and ACP (Air House Commercial Invoice Parties) for Air Shipments.

 

11/01/2002

Added Record Types OIH (Ocean House Invoice) and OID (Ocean House Invoice Detail) for Ocean Shipments.

 

Added Record Types AIH (Air House Invoice) and AID (Air House Invoice Detail) for Air Shipments.

 

03/17/2003

Added New Fields to the Commercial Invoice Detail Record Type (CID) of the Commercial Invoice Sub System.

 

1.      ColorCode

2.      ColorDescription

3.      SizeCode

4.      SizeDescription

5.      CartonNumber

 

11/12/2002

Modified Record Types to specify OwnerCode instead of ConsigneeCode for the following Record Types: POH, POD, POP, CIH, CID, and CIP.  The concept of OwnerCode is used to identify the Owner of the Purchase Order or Commercial Invoice that is being uploaded into the Purchase Order Subsystem, CommercialInvoice Subsystem, respectively.

 

 

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 Global Tranxit 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 Air and Ocean shipments is to have the customer send a multiple-record type, fixed length formatted 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 record type must have it’s own consistent fixed length layout format.  There may be as many record types as necessary to produce a complete file for upload.

 

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 Air Shipment information, the Master Air Way Bill Number field must exist in all Record Types that are defined for Air Shipments.

 

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 physically empty for the defined number of bytes that make up that field based on the record layout for that particular record type.

 

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.)

 

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. 

 

 

 

 

 

IMPORTS VERSUS EXPORTS (UPLOAD LOGIC)

 

EXPORTS:

                All Export Shipments are uniquely identifiable by the FILE NUMBER.  This implies that there is a one to one correspondence between FILENUMBER and MASTERBILL.

 

                Any given FILENUMBER may contain only ONE MASTERBILL.  Because of this relationship, the MASTERBILL is optional.  If the MASTERBILL is NOT known at the time the Shipment is sent, the Shipment may be uploaded without it.  At the time that the MASTERBILL becomes known, the Shipment may be sent again with the MASTERBILL Number specified.  The Shipment with the given FILENUMBER will then be updated with the MASTERBILL value sent.

 

IMPORTS:

                All Import Shipments are uniquely identifiable by the FILE NUMBER and MASTER BILL NUMBER.  This implies that any given Shipment, identified by FILENUMBER, may have multiple MASTERBILLS associated with it.  This implies a one-to-many relationship between FILENUMBER and MASTERBILL.

 

                Any given FILENUMBER may contain multiple MASTERBILLS.  Because of this relationship, the MASTERBILL is mandatory.  The MASTERBILL NUMBER is required, along with the FILENUMBER in order to create a unique Shipment in the system.

 

 


PO/AIR/OCEAN SHIPMENT DATA TRANSFER FORMATS

The next section of this document describes the exact data formats required in order to send AIR, OCEAN, PURCHASE ORDER, and COMMERCIAL INVOICE Shipment 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.

 

 

PURCHASE ORDER

DATA EXTRACT LAYOUTS

 

 

PURCHASE ORDER

Column

Field Name

Values/File Format

Field in FDPro

 

Field Type & Size

1

Record Type

‘POH’

 

Required

Char (3)

2

CorporateCode

Char (20)

CorporateCode

Required

Char (20)

3

OwnerCode

Char (10)

OwnerCode

Required

Char (10)

4

PONumber

Char (25)

PO Number

Required

Char (25)

5

GrossWeight

Char (14)

GrossWeight

 

Real

6

WeightUnits

‘KGS’ or “LBS’

WeightUnits

 

Char (3)

7

CubicMeters

Char (14)

CubicMeters

 

Float

8

NumberOfPkgs

Char (14)

NumberofPkgs

 

Integer

9

ItemStyle

Char (25)

ItemStyle

Required

Char (25)

10

NumberOfPieces

Char (14)

NumberOfPieces

Required

Integer

11

UnitofMeasure

Char (20)

UnitOfMeasure

 

Char (20)

12

QuoteNumber

Char (20)

QuoteNumber

 

Char (20)

13

EstPOShippingDate

Char (16)

EstPOShippingDate

 

Date

14

ConfirmPOShippedDate

Char (16)

ConfirmPOShippedDate

 

Date

15

ConsigneeReqETADate

Char (16)

ConfirmPOShippedDate

 

Date

16

DeliveryName

Char (35)

DeliveryName

 

Char (35)

17

Address1

Char (25)

Address1

 

Char (25)

18

Address2

Char (25)

Address2

 

Char (25)

19

City

Char (20)

City

 

Char (20)

20

State

Char (2)

State

 

Char (2)

21

ZipCode

Char (10)

ZipCode

 

Char (10)

22

Country

Char (20)

Country