Appendix_1.8_Detail_Report_Required_Data_Element_Fields.pdf

PWS Appendix 1.8 Solicitation: HTC711-17-R-CC05

1 September 2017

APPENDIX 1.8

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. Reports shall be submitted in Microsoft Excel 2007 or later format (See example of Shipment Information Data File below).

4. Fields in Microsoft Excel spreadsheet reports shall be labeled according to the tables provided below.

5. If there is no data for a particular field then leave the field blank.

6. City and State fields shall contain alpha characters only (no numeric addresses or special characters).

7. State fields should remain blank for non-US locations.

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/country_names_and_code_elements.htm).

9. File name and e-mail subject line 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 (A = CAT A Program Identifier)

e. File Type – identifies type of data contained in file (SI = Shipment Information, AI = Assessorial Information, AC = Accessorial Code, EC = Exception/Delay Codes, DC = Delivery Status Code)

f. Submission date - date file was sent

g. Example: 201105_XXXX_A_SI_20110603.xlsx represents shipment information files for deliveries made in May 2011. The XXXX is a place holder for the prime contractor carrier SCAC.

Example of Shipment Information Data File:

In this example the Prime Contractor Carrier SCAC of the company is SMPL. A stands for “CAT A” program, SI indicates the type of report (Shipment Information), and the file was submitted 8-Jan-2011 for the month of Dec 10.

File Name: 201112_SMPL_A_SI_20110108.xlsx

Field Name Information: File is saved in EXCEL 2007 or later http://www.iso.org/iso/country_codes/iso_3166_code_lists/english_country_names_and_code_elements.htm

Prime Carrier

SCAC

Sub Carrier

SCAC

Theater Name Region

Aircraft Body Type

Air Bill Number

Shipper Account Number

Contract Line

Number

Program Category TCN

SMPL WB 48248921 446781081 3001AZ FM52940001PV02XXX

SMPL WB 48248932 446781081 3001AZ FM52940001PV02XXX

SMPL WB 48248943 446781081 3001BB FM52940001PV02XXX

SMPL AMPE WB 69276083 SA4562870 3003AC W90GVP1260A003LXX

SMPL AMPE WB 69276081 SA4562870 3003AZ W5650U12595005LXX

SMPL AMPE WB 69276073 SA4562870 3003AC W91ZKW12570549LXX

Lead Identifier GBL TAC Shipment

Number Content Description Pieces Actual Weight

Actual Weight Unit

Charged Weight

Charged Weight Unit

245 MEDICAL SUPPLIES 1 72 LBS 72 LBS

246 MEDICAL SUPPLIES 1 1 LBS 5 LBS

247 MEDICAL SUPPLIES 1 835 LBS 1885 LBS

12C3PBAT 49428427 AIRCRAFT PARTS 1 2750 LBS 2750 LBS

12C3P02T 49428436 AIRCRAFT PARTS 2 456 LBS 458 LBS

12C3QRAT 49428478 AIRCRAFT PARTS 2 1074 LBS 1860 LBS

Shipper

DODAAC

Shipper Agency Shipper Unit Name Shipper City Shipper

State

Shipper Country

Code

Recipient DODAAC Recipient Unit Name

DLA CARDINAL HEALTH ONTARIO CA US FM5294 51ST MEDICAL GROUP SGAL MED

DLA CARDINAL HEALTH ONTARIO CA US FM5294 51ST MEDICAL GROUP SGAL MED

DLA CARDINAL HEALTH ONTARIO CA US FM5294 51ST MEDICAL GROUP SGAL MED

SW3222 DLA DLA DIST CENTER CORPUS CHRISTI TX US W91WBY 0003 HQ HQ ARCENT TALLIL

SW3222 DLA DLA DIST CENTER CORPUS CHRISTI TX US W91PFK 0003 HQ HQ JALALABAD MAIN

SW3222 DLA DLA DIST CENTER CORPUS CHRISTI TX US W91ZKW 0003 HQ HQ ARCENT BD DI CT

Recipient City Recipient State

Recipient Country

Code

Delivery Signature Pickup Date

Pickup Date

TmZone Delivery Date

Delivery Date

TmZone

Delivery Status

OSAN KR W. HICOCK 10/8/11 4:00 PM 10/12/11 7:40 AM OT

OSAN KR W. HICOCK 10/8/11 4:00 PM 10/12/11 7:40 AM OT

OSAN KR W. HICOCK 10/7/11 8:30 PM 10/10/11 8:05 AM OT

TALLIL IQ S. RUFFIN 9/21/11 10:00 AM 10/2/11 3:00 PM OT

FOB FENTY AF M. WILCOX 9/21/11 3:00 PM 10/3/11 12:00 PM OT

TAJI IQ B. HULLUM 9/19/11 4:00 PM 10/2/11 12:00 PM OT

Return Date

Return Date

TmZone

Time In Transit

Non Transit time

Exception Codes

Shipment Cost

Accessorial Charges Remarks

73 12 275 50 73 12 75 50 49 15 2242.48 406 138 18 8250 142 26 5768.01 186 17 9513

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 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 N/A =Data “Not Applicable” to this program, 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.

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 carried associated shipment

3 N/A Theater_Name VARCHAR(30) Leave Blank. Not applicable for CAT A shipments 4 N/A Region VARCHAR(3) Leave Blank. Not applicable for CAT A shipments

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

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’s 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 N/A Program_Category CHAR(1) Leave Blank. Not applicable for CAT A shipments

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 17-digit 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.

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 N/A TAC Transportation Account Code (e.g. N194, F8A0) VARCHAR(4) Leave Blank. Not applicable for CAT A shipments

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 & KGS=Kilograms

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 & KGS=Kilograms

21 R 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.

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_City The city from which the package was shipped (e.g. PORTSMOUTH, BOISE) Reference EDI 214 N401

VARCHAR(30) Must be provided as an alpha…no numeric or special characters

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.

29 R Recipient_City The city where the shipment was received.

(e.g. DOHA, THUMRAIT, ROBINS AFB) Reference EDI 214 N401

VARCHAR(30) Must be provided as an alpha…no numeric or special characters

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 pickup by the carrier from the shipper/government representative. (e.g.

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

DATETIME Date and Time Format MM/DD/YYYY HH:MM.

34 N/A Pickup_Date_TmZone VARCHAR(10) Leave Blank. Not applicable for CAT A shipments

SEQ

REQ

CD FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

35 R Delivery_Date

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

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

DATETIME Date and Time Format MM/DD/YYYY HH:MM.

36 N/A Delivery_Date_TmZone VARCHAR(10) Leave Blank. Not applicable for CAT A shipments

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 Format MM/DD/YYYY HH:MM.

39 N/A Return_Date_TmZone VARCHAR(10) Leave Blank. Not applicable for CAT A shipments

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.

DECIMAL(10,2)

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

For NAVICP day of pick-up does not count but weekends and holidays do.

42 W/A Exception_Codes

Code which identifies the reason for the delivery of shipment being late, excusably late or not deliverable. - 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 use exception codes provided in exhibit 5.10.

43 R Shipment_Cost The net charge of the shipment (Gross Freight Charges – Discounts + Accessorial) in USD.

This amount is based on sales not revenue.

CURRENCY/

DECIMAL(10,2)

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

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)

CURRENCY/

DECIMAL(10,2)

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.

File Name Example: 201105_XXXX_A_SI_20090623.xlsx represents Shipment Information for deliveries made in May 11, which was provided on Jun 23, 2011. The XXXX is a place holder for the prime contractor carrier SCAC.

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

CURRENCY/

DECIMAL(10,2)

Individual Charges must equal amount in #21 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.

File Name Example: 201105_XXXX_A_AS_20110623.xlsx represents Assessorial Information for deliveries made in May 11, which was provided on Jun 23, 2011. The XXXX is a place holder for the prime contractor carrier SCAC.

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 Assessorial Information – Line 2.

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: 201110_XXXX_AC_20111005.xlsx represents Assessorial Codes as of Oct 2011, which was provided on Oct 5, 2011. The XXXX is a place holder for the prime contractor carrier SCAC.

Exception/Delay Codes (EC) Reference Data File

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. Must identify which exception codes carrier considers package to be excusably late. (e.g. RNR)

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

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 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: 201110_XXXX_EC_20111005.xlsx represents Exception/Delay Codes as of Oct 2011, which was provided on Oct 5, 2011. The XXXX is a place holder for the

Delivery Status Codes (DC) Reference Data File

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

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: 201110_XXXX_DC_20111005.xlsx represents Delivery Status Codes as of Oct 2011, which was provided on Oct 5, 2011. The XXXX is a place holder for the