02.22.11 - WWX-5 Exhibit D.pdf

SOLICITATION: HTC711-11-R-C001

PERFORMANCE WORK STATEMENT– EXHIBIT D

Page 1 of 10 22 February 2011

EXHIBIT D

DETAIL REPORT REQUIRED DATA ELEMENT FIELDS

Standardized File Format and File Naming conventions:

1. The data file submitted by the carrier shall contain records for each shipment DELIVERED in the month being reported, as well as shipments returned as undeliverable.

2. A shipment is identified by a unique shipment identifier and may contain multiple pieces.

3. Microsoft Excel (MS Office 2007 or later) will be used to submit monthly data. (See Data File Table Legend below).

4. Fields shall be sequenced according to the tables provided below.

5. First row shall contain a header row identifying the field names. .

6. If there is no data for a particular field then leave blank and use a delimiter to represent field.

7. If the data type of a field is numeric/date then only place valid data types. (e.g. Pickup Date; do not place “Unavailable”).

8. Country Code fields shall use ISO 3166-1-alpha-2 code country codes (web reference:

http://www.iso.org/iso/country_codes/iso_3166_code_lists/english_country_names_and_code_elements.htm).

9. The File name shall be in the following format with an underscore separating each data element:

a. Year – identifies year of delivery

b. Month – identifies month of delivery

c. Carrier’s SCAC – Associates file to the Carrier

d. Program Type – Identifies Program Type (W = WWX Program Identifier)

e. File Type – identifies type of data contained in file (SI = Shipment Information, AI = Accessorial Information, AC = Accessorial Code, EC =

Exception/Delay Codes, DC = Delivery Status Code)

f. Submission date - date file was sent

g. Example: 201005_XXXX_W_SI_20100615.txt represents shipment information files for deliveries made in May 2010. The XXXX is a place holder for three or four character prime contractor carrier SCAC.

Example of Shipment Information Data File:

In the example below, the Prime Contractor Carrier SCAC of the company is SMPL W represents a report under the WWX program, SI indicates the type of report (Shipment Information), and the file was submitted 15-Jun-2010 for the month of May 2010.

File Name: 201005_SMPL_W_SI_20100615.txt

File Info:

File is saved in ASCII format using a pipe between each field.

Handling of Corrections and No Data:

Page 2 of 10 22 February 2011

1. Provide the original dataset with the corrected records

2. Follow the same naming convention making sure the Submission Date equals the date the correction is being submitted

3. Provide an email stating there was no movement for the month when no movement was made

Data File Table Legend:

Field Seq: Field Sequence, used to show the sequence of the fields.

Req CD: Requirement Code, used to show the contract requirement of each field.

R = “Required” – must provide data for this field W/A = Shall provide information “When Applicable”, otherwise leave this field blank I/P = “If Provided” by the Shipper, otherwise leave this field blank

Field Heading: Field Name, name associated with each field. This name shall be used in each file as the first row.

Description: Field Description, used to explain the field name and provide small sample of data.

Data Type: Field Data Type, used to show the type of data that should be provided.

Special Instructions: Field Special Instructions to assist carrier with additional information about data field.

Page 3 of 10 22 February 2011

Shipment Information (SI) Data File

FIELD

SEQ

REQ

CD FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

1 R Prime_Carrier_SCAC Standard Carrier Alpha Code for the Prime contractor (e.g. FedEx = FDE) Reference EDI 214 B1003

VARCHAR(4)

2 W/A Sub_Carrier_SCAC Standard Carrier Alpha Code for the sub-contractor carrier (e.g. FedEx = FDE) Reference EDI 214 MS301

VARCHAR(4) Field is only required if a subcontractor carrier associated shipment

3 R Theater_Name Contract Theater associated with the destination country (e.g. Southern, European, etc…)

VARCHAR(30) See Exhibit F (WWX Regions) for correct input.

4 R Region Contract Region associated with destination country (A, B, HVR, etc…) VARCHAR(3) See Exhibit F (WWX Regions) for correct input.

5 W/A Aircraft_Body_Type Identifies aircraft body type used to transport cargo (e.g. Wide = WB, Narrow

= NB)

CHAR(2) Leave Blank. (This is a place holder only and is not applicable for WWX.)

6 R Air_Bill_Number

(AWB/HAWB) The number printed on each shipping document that identifies a shipment. This number is used to track each shipment from its origin to destination. (e.g. 22985195) Reference EDI 214 B1005

VARCHAR(25) This number must be unique to each record in the detail report provided. [Primary Key of the Table]

7 R Shipper_Account_Number The shipper account number. (e.g.

384436167) VARCHAR(15)

Customer account number should match back to the individual customer. Carrier will provide customer details (e.g., POC, address, phone number, etc) upon request by Program Management Office (PMO) or Administrative Contracting Officer (ACO)

8 R Contract Line Number Contract Line Item Number (CLIN) or Sub-Line Item Number (SLIN).

Reference EDI 214 L1101, L1102

VARCHAR(25) Corresponds to the Contract or Sub-Line Number.

9 R Program_Category Saved for future use

CHAR(1)

Page 4 of 10 22 February 2011

Shipment Information (SI) Data File

FIELD

SEQ

REQ

CD FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

10 I/P TCN

(Transportation Control Number) Identifies the provided DoD tracking number, when provided.

(e.g. W915127195F009XXX) Reference EDI 214 L1101, L1102, B1002

VARCHAR(100)

Government shipper unique tracking number. Value should not contain dashes or spaces in number. A 17digit alphanumeric designator used by the DoD applied to each package or shipment to identify a shipment. In the event that the carrier must split a shipment tendered under one TCN, the government acknowledges individual tracking numbers may be assigned to the same TCN.

11 I/P Lead_Identifier Lead TCN or Master Air Way Bill Reference EDI 214 L1102, B1002, B1005

VARCHAR(50) Unique number representing a consolidated shipment.

12 I/P GBL

Government Bill of Lading (e.g.

1594C1323041 000) Reference EDI 214 L1102

VARCHAR(25) Document number used to procure commercial transportation services outside the DTS.

13 I/P TAC Transportation Account Code (e.g. N194, F8A0) VARCHAR(4) Code that represents who is paying for the movement.

14 W/A Shipment_Number Identifies unique way to search for shipment on shipper’s website (e.g. 855353492480)

VARCHAR(30) Carrier’s unique tracking number. Only report if different from the Air Bill Number.

15 I/P Content_Description Identifies contents of the package that is being shipped. (e.g. Brake Set, Tires) VARCHAR(100)

Equivalent to Government Air Commodity Code. Short description preferably. Code should show what type of shipment is being moved.

16 R Pieces The number of pieces contained in the shipment. (e.g. 2, 6) Reference EDI 214 AT804

INTEGER

17 R Actual_Weight The total weight of the individual shipment. Reference EDI 214 AT803 DECIMAL(10,1)

18 R Actual_Weight_Unit Indicated unit of measurement for actual weight Reference EDI 214 AT802

VARCHAR(3) LBS=Pounds

19 R Charged_Weight The total charged weight of the shipment Reference EDI 214 AT803 DECIMAL(10,1)

Page 5 of 10 22 February 2011

Shipment Information (SI) Data File

FIELD

SEQ

REQ

CD FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

20 R Charged_Weight_Unit Indicated unit of measurement for charged weight Reference EDI 214 AT802

VARCHAR(3) LBS=Pounds

21 I/P Shipper_DoDAAC Shipper Department Of Defense Activity Address Code. (e.g. W90Y8E, N09281) Reference EDI 214 N103, N104

VARCHAR(6) Six digit code that represents the address of the shipper.

22 R Shipper_Agency

The Government agency or DOD Service that shipped the package. This is an attribute of the Shipper Account Number.

(e.g. DLA, USAF, USN, DOE, etc…) Reference EDI 214 N201, N202

VARCHAR(35) Must be provided as an alpha… not numeric.

23 I/P Shipper_Unit_Name Unit or Organization name. (e.g. TACC, 597th TG, 375th LRS) Reference EDI 214 N201, N202

VARCHAR(50) If not a DOD or government organization, then leave blank.

24 R Shipper_City The city from which the package was shipped (e.g. PORTSMOUTH, BOISE) Reference EDI 214 N401

VARCHAR(30)

25 W/A Shipper_State

The code which identifies the state from where the package was shipped (e.g. ID, VA, TX)

Reference EDI 214 N402

VARCHAR(2) Provide for CONUS shipper locations. Must be a 2 Digit Code. May be left blank for OCONUS locations

26 R Shipper_Country_Code The country code from which the shipment was shipped. (e.g. IQ, JP, US) Reference EDI 214 N404

CHAR(2) Must be a 2 Digit ISO 3166-1-alpha-2 code country codes.

27 I/P Recipient_DoDAAC

Recipient Department Of Defense Activity Address Code. (e.g. FB3029, N69058) Reference EDI 214 N103, N104

VARCHAR(6) Six digit code that represents the address of the recipient.

28 I/P Recipient_Unit_Name Unit or Organization name. (e.g. TACC, 597th TG, 375th LRS) Reference EDI 214 N201, N202

VARCHAR(50) If not a DOD or government organization, then leave blank.

Page 6 of 10 22 February 2011

Shipment Information (SI) Data File

FIELD

SEQ

REQ

CD FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

29 R Recipient_City

The city where the shipment was received. (e.g. DOHA, THUMRAIT, ROBINS AFB)

Reference EDI 214 N401

VARCHAR(30)

30 W/A Recipient_State This code which identifies the state where the shipment was received. (e.g.

TX, WA) Reference EDI 214 N402

CHAR(2) Provide for CONUS Recipient locations. Must be a 2 Digit Code. May be left blank for OCONUS locations

31 R Recipient_Country_Code The country code where the shipment was received (e.g. IQ, JP, US) Reference EDI 214 N404

CHAR(2) Must be a 2 Digit ISO 3166-1-alpha-2 code country codes.

32 I/P Delivery_Signature Signature of the Recipient or his agent VARCHAR(50) Persons name who signed for the item

33 R Pickup_Date

The date/time stamp when the package was picked up by the carrier from the shipper/government representative. (e.g.

06/20/2007 09:23) Reference EDI 214 AT705, AT706

DATETIME

Date and Time of Pickup in Zulu/GMT. Format MM/DD/YYYY HH:MM. If Zulu/GMT is not possible then provide the package Pickup Time Zone in a separate column (In Field Seq 34 labeled Pickup_Date_TmZone).

34 W/A Pickup_Date_TmZone

If Zulu/GMT is not possible for Pickup_Date (Column 33) then provide the package Pickup Time Zone. (e.g.

ACDT, ADT, HADT, etc…) Reference EDI 214 AT707

VARCHAR(10) Field is only required if Pickup_Date is not in GMT/Zulu (Field Seq 33)

35 R Delivery_Date

The date/time stamp when the package was successfully delivered to government customer/representative.

(e.g. 06/21/2007 10:23) Reference EDI 214 AT705, AT706

DATETIME

Date and Time of Delivery in Zulu/GMT. Format MM/DD/YYYY HH:MM. If Zulu/GMT is not possible then provide the package Delivery Time Zone in a separate column (In Field Seq 36 labeled Delivery_Date_TmZone).

36 W/A Delivery_Date_TmZone

If Zulu/GMT is not possible for Delivery_Date (Column 35) then provide the package Pickup Time Zone. (e.g.

ACDT, ADT, HADT, etc…) Reference EDI 214 AT707

VARCHAR(10) Field is only required if Delivery_Date is not in GMT/Zulu (Field Seq 35)

Page 7 of 10 22 February 2011

Shipment Information (SI) Data File

FIELD

SEQ

REQ

CD FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

37 R Delivery_Status

Code which identifies if the delivery was on-time, late, excusably late or not deliverable. (e.g. OT, LT, EL, ND) Reference EDI 214 AT701, AT702, AT703, AT704

VARCHAR(2)

Carrier shall provide reference table for any other codes that will be used. When delivery_status code LT, EL or ND are assigned there must be an exception_code in Field Seq 42 that explains the reason for these status’ and the ultimate disposition of the item being shipped as necessary.

38 W/A Return_Date

The date/time stamp when the package was sent back to the shipper because the item was not deliverable.

Reference EDI 214 AT705, AT706

DATETIME

Date and Time of Return in Zulu/GMT. Format MM/DD/YYYY HH:MM. If Zulu/GMT is not possible then provide the package Delivery Time Zone in a separate column (In Field Seq 39 labeled Return_Date_TmZone).

39 W/A Return_Date_TmZone

If Zulu/GMT is not possible for Return_Date (Column 38) then provide the package Pickup Time Zone. (e.g.

ACDT, ADT, HADT, etc…) Reference EDI 214 AT707

VARCHAR(10) Field is only required if Return_Date is not in GMT/Zulu (Field Seq 38)

40 R Time_in_Transit Calculated difference between Pickup Date and Delivery Date. Represented in Hours.

DECIMAL(10,2)

The time shall be represented in hours with a two place decimal for minutes. The total time in transit will include weekends and holidays.

41 R Non_Transit_Time Calculated hours for pick-up day, weekends, holidays, and authorized delays included in column 40 above.

The time shall be represented in hours with a two place decimal for minutes.

42 W/A Exception_Codes

Code which identifies the reason for the delivery of shipment being late, excusably late or not deliverable in #40 above. Must identify which exception codes carrier considers package to be excusably late. (e.g. RNR = Recipient not ready for delivery) Reference EDI 214

AT701, AT702, AT703, AT704

VARCHAR(10) Carrier shall provide reference table and explanation of the carrier’s exception codes.

43 R Shipment_Cost

(Cost to AMC) The net charge of the shipment (Gross Freight Charges – Discounts + Accessorial) in USD. This amount is based on sales not revenue.

Do not include “$” in data submission. Must be in US Dollars. (e.g. 2,367.86)

Page 8 of 10 22 February 2011

Shipment Information (SI) Data File

FIELD

SEQ

REQ

CD FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

44 W/A Accessorial_Charges Total charge for all accessorial charges associated with the shipment in USD.

(e.g. 209.89)

Do not include “$” in data submission. Must be in US Dollars. This amount must be included in the total for Field Seq 43 above.

45 W/A Remarks Brief Explanation of Issues Reference EDI 214 K101, K102 VARCHAR(255)

Definitely required when Exception_Code is entered to indicate duration of delay, and to record any additional exception codes germane to the movement of the item shipped.

Additional Information: Report is required monthly, no later than the 18th calendar day of each month following the month of delivery. If there were NO deliveries during the reporting period, an email stating “Negative Shipment Information Report for ____ (month/year) is hereby submitted.”

File Name Example: 201005_XXXX_W_SI_20100615.xls (or .xlsx) represents shipment information files for deliveries made in May 2010. The XXXX is a place holder for three or four character prime contractor carrier SCACs.

Accessorial Information (AI) Data File

SEQ REQ FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

1 R Air_Bill_Number

(AWB/HAWB) The number printed on each shipping document that identifies a shipment. This number is used to track each shipment from its origin to destination. (e.g. 22985195)

VARCHAR(25)

This number must be unique across each Accessorial code record in the detail report provided. [Primary Key of the Table]

2 R Accessorial_Code Code used to identify the accessorial code (e.g. BYD, W, TH) VARCHAR(5) This code must be unique across each AWB record in the detail report provided. [Primary Key of the Table]

3 R Accessorial_Charges Total charge for all accessorial charges associated with the shipment and accessorial code in USD. (e.g. 120.12)

Individual Charges must equal amount in #44 from Shipment Information Data file. Do not include “$” in data submission. Must be in US Dollars. Provide reference table and explanation of the carrier’s exception codes.

4 R Prime_Carrier_SCAC Standard Carrier Alpha Code for the carrier (e.g. FDE) VARCHAR(4)

Additional Information Report is required monthly, no later than the 18th calendar day of each month following the month of delivery. If there were NO Accessorials charged during the reporting period, an email stating “Negative Accessorial Report for ____ (month/year) is hereby submitted” will be sent in lieu of a blank report.

File Name Example: 201005_XXXX_W_SI_20100615.xls (or .xlsx) represents Accessorial Information for deliveries made in May 2010. The XXXX is a place holder for three or four character prime contractor carrier SCACs.

Page 9 of 10 22 February 2011

Accessorial Codes (AC) Reference Data File

SEQ REQ FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

1 R Accessorial_Code Code used to identify the accessorial code (e.g. BYD, W, TH) VARCHAR(5)

This code must be unique to each record in the detail report provided. [Primary Key of the Table]. This code relates to the Accessorial Information – Line 44.

2 R Accessorial_Code_Description

The textual description of the contract associated Accessorial Codes. (e.g.

HAZMAT Handling Charges, Signature Service, Address Correction, Holidays, Weekends, Export Declaration, Insurance)

VARCHAR(50)

3 R Prime_Carrier_SCAC Standard Carrier Alpha Code for the carrier (e.g. FDE) VARCHAR(4)

Additional Information: Provided at the beginning of each contract/ option year or when changes are made to the codes and/or their corresponding descriptions.

File Name Example: 201005_XXXX_W_AC_20100615.xls (or .xlsx) represents Accessorial Codes as of May 2010, and the report was provided on Jun 15, 2010.

The XXXX is a place holder for three or four character prime contractor carrier SCACs.

Exception/Delay Codes (EC) Reference Data File

FIELD

SEQ REQ FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

1 R Exception_Code

Code which identifies the reason for the delivery of shipment being late or excusably late in #37 above. Must identify which exception codes carrier considers package to be excusably late.

(e.g. RNR). Codes are located in Exhibit I

VARCHAR(5)

This code must be unique to each record in the detail report provided. [Primary Key of the Table] This code relates to the Shipment Information – Line 42.

2 R Exception_Code_Description The textual description of the associated Exception Code. (e.g.

Recipient not ready for delivery)

VARCHAR(50)

3 R Prime_Carrier_SCAC Standard Carrier Alpha Code for the carrier (e.g. FDE) VARCHAR(4)

4 R Excusable_Indicator To identify if the Exception Code is considered excusable. VARCHAR(1) Y=Yes

N=No

Page 10 of 10 22 February 2011

Additional Information: Provide at the beginning of each contract/option year or when changes are made to the codes and/or their corresponding descriptions.

File Name Example: 201005_XXXX_W_EC_20100615.xls (or .xlsx) represents Exception/Delay Codes as of May 2010, and the report was provided on Jun 15, 2010. The XXXX is a place holder for three or four character prime contractor carrier SCACs.

Delivery Status Codes (DC) Reference Data File

FIELD

SEQ REQ FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

1 R Delivery_Status_Code Code which identifies if the delivery was on-time, late or excusably late.

(e.g. EL)

VARCHAR(20)

This code must be unique to each record in the detail report provided. [Primary Key of the Table] This code relates to the Shipment Information - Line 37.

2 R Delivery_Status_Description The textual description of the associated customer account number.

(e.g. Excusably Late)

VARCHAR(100)

3 R Delivery_Status_Category Category of contract delivery status compliance category (e.g. Authorized Delay)

VARCHAR(20) The values for the column must be On-time, Authorized delay, and Late

4 R Prime_Carrier_SCAC Standard Carrier Alpha Code for the carrier (e.g. FDE) VARCHAR(4)

Additional Information: Provide at the beginning of each contract/option year or when changes are made to the codes and/or their corresponding descriptions.

File Name Example: 201005_XXXX_W_DC_20100615.xls (or .xlsx) represents Delivery Status Codes as of May 2010, and the report was provided on Jun 15, 2010.

The XXXX is a place holder for three or four character prime contractor carrier SCACs.