Appendix_6.3 _Detail_Report_Required_Data_Element_Fields.pdf

APPENDIX 6.3 Page 1 of 10

APPENDIX 6.3

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.

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 (T = TDS Program Identifier)

e. File Type – identifies type of data contained in file (SI = Shipment 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.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 SCAC.

Example of Shipment Information Data File:

In the example below, the Prime Contractor Carrier SCAC of the company is SMPL T represents a report under the TDS 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_T_SI_20100615.xlsx

Handling of Corrections and No Data:

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

APPENDIX 6.3 Page 2 of 10

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 N/A = Data “Not Applicable”, leave the field blank I/P = “If Provided” by the Shipper/Receiver, 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.

APPENDIX 6.3 Page 3 of 10

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 Reference EDI 214 B1003

VARCHAR(4)

2 W/A Sub_Carrier_SCAC Standard Carrier Alpha Code for the sub-contractor carrier Reference EDI 214 MS301

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

3 R Theater_Name VARCHAR(30) Leave Blank 4 R Region VARCHAR(3) Leave Blank 5 W/A Aircraft_Body_Type CHAR(2) Leave Blank.

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 W/A Program_Category Saved for future use CHAR(1) Leave Blank

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.

APPENDIX 6.3 Page 4 of 10

FIELD

SEQ

REQ

CD FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

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)

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.

APPENDIX 6.3 Page 5 of 10

FIELD

SEQ

REQ

CD FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

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_Address The street address of the shipper VARCHAR(40)

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

VARCHAR(30)

26 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

27 R Shipper_Zip_Code The zip code from which the shipments was shipped (e.g., 90210, 11746) VARCHAR(5) Cells should be formatted to capture leading zeros.

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

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

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

31 R Recipient_Address The street address of the recipient VARCHAR(40)

32 R Recipient_City

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

Reference EDI 214 N401

VARCHAR(30)

33 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

34 R Recipient_Zip_Code The zip code where the shipments was received.(e.g. 90210, 11746) VARCHAR(5) Cells should be formatted to capture leading zeros.

APPENDIX 6.3 Page 6 of 10

FIELD

SEQ

REQ

CD FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

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

R

Domestic Only

Service_Category

The type of service requested (examples: Next Day Air by 0800, Next Day Air by 1030, Next Day Air by 1500, Second Day Air, Third Day Air)

VARCHAR(50)

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

38 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 39labeled Pickup_Date_TmZone).

39 W/A Pickup_Date_TmZone

If Zulu/GMT is not possible for Pickup_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 Pickup_Date is not in GMT/Zulu (Field Seq 38)

40 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 41 labeled Delivery_Date_TmZone).

APPENDIX 6.3 Page 7 of 10

FIELD

SEQ

REQ

CD FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

41 W/A Delivery_Date_TmZone

If Zulu/GMT is not possible for Delivery_Date (Column 40) 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 40)

42 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 47 that explains the reason for these status’ and the ultimate disposition of the item being shipped as necessary.

43 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 41 labeled Return_Date_TmZone).

44 W/A Return_Date_TmZone

If Zulu/GMT is not possible for Return_Date (Column 43) 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 43)

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

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

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

47 W/A Exception_Codes

Code which identifies the reason for the delivery of shipment being late, excusably late or not deliverable in #45 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) Authorized Exception Codes are listed in Appendix 6.7

APPENDIX 6.3 Page 8 of 10

FIELD

SEQ

REQ

CD FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

48 R Shipment_Cost

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)

49 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 48 above.

50 W/A Accessorial_Code Code used to identify the accessorial code (e.g. BYD, W, TH) VARCHAR(20)

An entry in this field is required anytime there is data submitted in Field Seq 49. This code must match validated codes in the Accessorial Code Reference Data File. [Primary Key of the Table] If more than one accessorial code is used, separate them by commas

51 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 19th 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.

APPENDIX 6.3 Page 9 of 10

Accessorial Codes (AC) Reference Data File

FIELD

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.

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.