Appendix 1.4 Monthly Reporting Instructions.pdf

HTC711-22-R-C001

PWS Appendix 1.4

1 October 2022

APPENDIX 1.4

GLOBAL HEAVYWEIGHT SERVICE (GHS)

MONTHLY REPORT INSTRUCTIONS

Standardized File Format and File Naming conventions:

1. The data file submitted by the carrier shall contain records for each shipment by Actual Delivery Date in the month being reported, as well as shipments returned as undeliverable. The data file submitted shall be comprised of all invoiced shipments from the reporting month. These shipments do not need to be paid to be included in this report.

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

3. Only shipments delivered during the current Performance Period will be accepted – 1 April to 31 March

4. File delivered should be in a “csv” file (.csv) with headers to submit monthly data. (See Data File Table Legend below).

5. No commas within the data fields shall be used throughout the submitted monthly data file. For example, ‘DLA Distribution New Cumberland’ is acceptable; however, ‘DLA Distribution, New Cumberland’ is unacceptable. No spaces shall be used before or after any number or series of words in the submitted monthly data. For example, ‘DLA Distribution New Cumberland’ is acceptable; however, ‘ DLA Distribution New Cumberland ’ is unacceptable. Both commas and spacing have a negative effect on .csv files.

6. Fields shall be sequenced according to the tables provided below. Changes to the order must be cleared through PMO at least 2 weeks in advanced.

7. Accessorial total charge is to be entered on the shipment record.

8. Itemized Accounting elements (Accessorials) should be provided in a separate file and in the format of table listed below.

9. If there is no data for a particular field, then leave blank unless otherwise instructed.

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

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

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

13. Country Code fields shall use ISO 3166-1-alpha-3 code country codes (web reference: https://www.iso.org/obp/ui#search )

14. All weights should be in U.S. Pounds (lb)

15. 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. Prime Carrier’s SCAC – Associates file to the Prime Carrier

d. File Type – identifies type of data contained in file (SI = Shipment Information, AI = Accessorial)

e. Submission date - date file was sent

16. No spaces shall be used before or after any number or series of words anywhere in the submitted monthly data as it has negative effects on .csv files.

Example: 202205_SMPL_SI_20220615.csv represents shipment information files for deliveries made in May 2022. The SMPL is a place holder for three or four-character prime contractor carrier SCAC.

1 October 2022

Example of Shipment Information Data File:

In the example below, the Prime Contractor Carrier SCAC of the company is SMPL, SI indicates the type of report (Shipment Information), AI indicates (Accounting Information) and the file was submitted 15-Jun-2022 for the month of May 2022. Two (2) separate files will be sent for each file name, one for each file type, only if contains data; see examples below. Accessorials are not to be rolled up under shipping costs; each accessorial(s) are to be individually recorded in the AI report.

File Name: 202205_SMPL_SI_20220615.csv 202205_SMPL_AI_20220615.csv

Submission of files to: TCAQ & TCJ5/4 Mailbox LM ONLY transcom.scott.tcaq.mbx.aq-cl@mail.mil transcom.scott.tcj5j4.mbx.lm@mail.mil

Files submitted to any location other than what is listed here will not be accepted. Files are to be sent to all emails.

Handling of Additions, Corrections & Deletions, and No Data:

Provide any updates, corrections and/or deletions no later than the 18th day of the 2nd reporting month after original reporting time frame. Example: Any corrections, additions and/or deletions for the month of April should be submitted no later than the

18th of June, May – no later than the 18th of July, etc. When submitting an update file for a previously reported month, provide in the email the information regarding the changes made. Only submit information for the current Performance Period

1. Additions - When a shipment has not been previously reported, records should be submitted no later than the 2nd reporting month after the original reporting time frame. These shipments can be sent within the current report being submitted if the delivery dates are within the same Performance Period. March deliveries cannot be submitted in an April file; this is considered 2 different Performance Periods.

Example: Original file name: 202004_SMPL_SI_20200515.csv - April file submitted on the 15th of June missing 2 shipments (not invoiced on time to submit in the April report)

Next Month: 202205_SMPL_SI_20220615.csv - May file submitted on the 15th of June containing May data AND 2 shipments from April that were NOT previously reported (now invoiced but not submitted in the April report - Last available date to report is 18th of July)

2. Corrections and/or Deletions - If a file is sent with record(s) that need to be corrected or removed, resubmit the ORIGINAL file again with the corrections or deletions of records to be reprocessed. By doing this, the system will update or remove any

1 October 2022 unwanted records to avoid duplication. In the email subject line indicate this is a Replacement File - Do not change the original file name in any manner when using this process to update or remove unwanted records. This is applicable to both SI and AI reports.

3. Accessorial Charge Reporting (AI Report) All accessorial charges shall be itemized on the AI report using the codes found in the Accessorial Codes to use for Accounting Type Data Field Table at the end of this Appendix.

4. No Data - No Movement for the month - Send email with Subject line of SCAC - No Shipments for (enter month)

Shipment Information Table Structure

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

N/A = Data "Not Applicable" to this program, leave the field blank

Shipment Information (SI) Data

REQ

CD

FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

R

Prime_Carrier_SCAC

Standard Carrier Alpha Code for the Prime contractor (e.g.

FedEx = FDE) Reference EDI 214 B1003

VARCHAR(4)

Tracking_Number

Number unique to the contractor. Identifies unique way to search for shipment on shipper's website (e.g.

855353492480). Same tracking number as that used to track on carrier’s tracking webpage, and that tracking number used for EDI submissions and in Syncada.

VARCHAR(30)

Shipment (Tender) number shall be sent to EDI on the L11 segment (position 30) where L1101 is the Tender Number and L1102 is

CT.

Bill_of_Lading

A detailed list of a shipment of goods in the form of a receipt given by the carrier to the person consigning the goods. A contract between the shipper and the Transportation Service Provider (TSP) whereby the TSP agrees to furnish transportation services subject to the conditions printed on the bill of lading. Also refers to the data feed (priced-out bill of lading) from the shipper system (Central Web Application) to the Third Party Payment System that contains shipment information. CBL does not equate to the carrier’s

AWB.

VARCHAR(25)

If the CBL is not provided per approval of the CO for DoD shipments, state, “Not provided.”

I/P

Shipment_Reference

Individual shipper assigned identifier; Contains the lead Transportation Control Number (TCN) for DoD Shipments.

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

Shipper_Account_Number

The shipper account number. (e.g. 384436167). If shipper account number does not exist for a DoD Customer, place the GBLOC found on the Bill of Lading in this field.

VARCHAR(15)

Customer account number or GBLOC 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)

Shipper_Company

The Company or Organization that shipped the package, which is found in the SHIPPER block of the bill of lading.. This is an attribute of the Shipper Account Number.

VARCHAR(35)

Origin_City

The city from which the package originated from, which is found in the ORIGIN block of the bill of lading. (e.g. PORTSMOUTH, BOISE) Reference EDI 214 N401

Should not contain numeric or special characters.

W/A

Origin_State

The code which identifies the state/ providence from where the package originated, which is found in the ORIGIN block of the bill of lading. (e.g. ID, VA, TX) Reference EDI 214 N402

CHAR(2)

Provide 2 digit State or Providence Code

R Origin_Country_Code The country code from which the package originated, which is found in the ORIGIN block of the bill of lading.. (e.g. JPN, USA) Reference EDI 214 N404

CHAR(3)

Must be a 3 Digit ISO 3166-1-alpha-3code country codes.

Origin_Zip_Postal_Code

The zip code where the package originated, , which is found in the ORIGIN block of the bill of lading. (e.g. 90210, 11746). Domestic– required, International if applicable

VARCHAR(5)

Should be formatted to capture leading zeroes. Required for all Domestic locations.

R Recipient_Company Company or Organization name. (e.g. TACC, 597th TG, 375th LRS) Reference EDI 214 N201, N202

VARCHAR(50)

Recipient_City

The city where the shipment was received. (e.g.

DOHA, THUMRAIT, ROBINS AFB)

Reference EDI 214 N401

Should not contain numeric or special characters.

Recipient_State

This code which identifies the state/providence where the shipment was received. (e.g. TX, WA) Reference EDI 214 N402

CHAR(2)

Provide 2 digit State or Providence Code

R Recipient_Country_Code The country code where the shipment was received (e.g. JPN, USA) Reference EDI 214

CHAR(3)

Must be a 3 Digit ISO 3166-1-alpha-3 code country codes.

Recipient_Zip_Postal_Code

The zip code where the shipment was received (e.g. 90210, 11746). Domestic–required, Should be formatted to capture leading zeroes. Required for all Domestic locations.

International if applicable

I/P

Content_Desc

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.

Piece_Dim_Length

The dimensional length of the shipment in Inches

DECIMAL(10.1)

For multiple pieces, enter the length of the largest piece (L-W-H Measurements are to be from the same piece)

Piece_Dim_Width

The dimensional width of the shipment in Inches

For multiple pieces, enter the width of the largest piece (L-W-H Measurements are to be from the same piece)

Piece_Dim_Height

The dimensional height of the shipment in Inches

For multiple pieces, enter the height of the largest piece (L-W-H Measurements are to be from the same piece)

R Actual_Weight The total weight of the individual shipment in pounds. Scale weight. Reference EDI 214

AT803

INTEGER

Weights shall be indicated in pounds;

whole pounds rounded (e.g. 360).

Billed_Weight

The Actual or Dimensional weight, whichever is greater, and it is rounded to an integer or whole number. This value represents the pricing and/or weight break category used to calculate the shipping cost. In the case of multiple piece shipments, it is also the value that is multiplied by the appropriate weight break category price per pound. Reference EDI 214 AT803

INTEGER

Weights shall be indicated in pounds; whole pounds rounded (e.g. 360).

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

INTEGER Needs to be a number greater than or equal to 1 piece per shipment.

R Package_Type The type of package utilized by the shipment (e.g. CT, BX, PT)

VARCHAR(10)

Shipper_RDD_Timestamp

The date/time stamp when the package was committed to be delivered by (e.g. 07- 04-2020 14:23)

DATETIME

Date and Time Format MM-DD-YYYY HH:MM (24 Hour Clock). If Zulu/GMT is not possible then provide the Time Zone in the associated TmZone column

Shipper_RDD_TmZone

If Zulu/GMT is not possible for Shipper_TDD_Timestamp then provide the package Shipper RDD / Delivery Commitment Time Zone. (e.g. ACDT, ADT, HADT, etc.) Reference EDI 214 AT707

Required when not Zulu

Pickup_Timestamp

The date/time stamp when the package was picked up by the carrier from the shipper/government representative. (e.g. 07-04-2020 14:23). Reference

EDI 214 AT705, AT706

(24 Hour Clock). If Zulu/GMT is not possible then

Column

Pickup_TmZone

If Zulu/GMT is not possible for Pickup_Time then provide the package Pickup Time Zone (e.g.

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

AT707

Field is only required if Pickup_Time is not in GMT/Zulu.

Service_Type_Code

Field denoting the level of service provided in special instructions.

International service will be coded as D3.

Use the following service level codes:

Next Flight Out = SG Next Day = D1 Second Day = D2 Deferred = D3 International = D3

Delivery_Status

Code which identifies if the delivery was on-time, late, excusably late or not deliverable. (The only acceptable codes are: OT, LT or ND)

VARCHAR(2)

Delivery_Timestamp

The date/time stamp when the package was successfully delivered to government customer/representative. (e.g. 07-04-2020 14:23) Reference EDI 214 AT705, AT706

(24 Hour Clock). If Zulu/GMT is not possible then

Column

Delivery_TmZone

If Zulu/GMT is not possible for Delivery Time, then provide the package Delivery Time Zone.

(e.g. ACDT, ADT, HADT, etc…) Reference EDI

214 AT707

Field is only required if Delivery Time is not in GMT/Zulu

W/A Delay_Code Carrier shall use authorized excusable exception codes listed in the Excusable Delay Codes and Descriptions table listed below.

Only one excusable code shall be used per shipment. Although many codes may be applicable for a shipment, the code cited on the report must reflect the code for the precise reason that caused the shipment to be delayed and not meet the service level for domestic service or the required delivery date for international service.

The use of a code in this column requires the

VARCHAR(10) No commas allowed and no spaces shall be used before or after any number or series of words.

1 October 2022 circumstances surrounding its use to be explained in the remarks column.

R Proof_of_Delivery_Recipient Signature of the Recipient or his agent VARCHAR(50) Persons name who signed for the item

R Invoice_Date The date the invoice was issued. (e.g. 07-04-2020) DATE Date format ‘MM-DD-YYYY’ NOT

NULL/INV_DT

R Invoice_Number The number assigned to the invoice by the Contractor

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

Shipment_Amount

Not including accessorial charges, must be in USD.

(e.g. 209.89)

DECIMAL(10.2)

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

Accounting_Amount

Total charge for all Accessorials associated with the shipment in USD. (e.g. 209.89)

US Dollars. This amount must be included in the total for Total_Shipment_Amount.

Duties_and_Taxes_Amount

Total charge for all duties and taxes charges associated with the shipment in USD. (e.g. 209.89)

US Dollars. This amount must be included in the total for Total_Shipment_Amount.

R Total_Shipment_Amount Total net amount for shipment (i.e. Shipment Amount + Accessorial) in USD. (e.g. 209.89)

DECIMAL(10.2)

US Dollars.

Remarks

Brief Explanation of Issues. Reference EDI 214 K101, K102

VARCHAR(255)

Used to record any additional information germane to the movement of the item shipped.

This section will also be used to provide the circumstances surrounding the use of any excusable delay code.

GHS Excusable Delay Code and Descriptions

Excusable

Delay Code Government Excusable Delay Code Description

AOG Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to Acts of God (e.g. Earthquake, Flood, Hurricane/Typhoon, Tsunami, etc.)

PAN Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to pandemics, epidemics, quarantine restrictions, or other major health crises

CUS Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to customs clearance issues beyond the contractor's control and not covered by CUC or CUD

CUC Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to changes in the destination country’s customs clearance process, which occurred while shipment was in-transit

CUD Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to customs clearance issues caused by incorrect or missing documentation from shipper

DGP Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to improper dangerous goods paperwork from shipper

HLD Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to a request from the customer to hold shipment

HOL Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to holiday hours at receiving installation

ICI Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to incorrect consignee information given by shipper

IEX Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to installation being closed due to exercise PKG Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to improper packaging by customer, freight had to be repackaged POC Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to point of contact not available to accept delivery RCE Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to receiving office closing early, unable to deliver RSC Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to customer request for rescheduling date of deliver RWY Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to commercial network runway clearance issues, plane could not land SEC Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to security purpose

SRC Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to strikes, trios, civil commotion

WEA Shipment delayed beyond RDD (international) or service level guarantee (domestic) due to severe weather restrictions

Accounting Table Structure

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

N/A = Data "Not Applicable" to this program, leave the field blank

Accounting Information (AI) Data File

REQ CD FIELD HEADING DESCRIPTION DATA TYPE SPECIAL INSTRUCTIONS

Prime_Carrier_SCAC

Standard Carrier Alpha Code for the Prime contractor (e.g. FedEx = FDE) Reference

EDI 214 B1003

VARCHAR(4)

Tracking_Number

Number unique to the contractor. Identifies unique way to search for shipment on shipper's website (e.g. 855353492480)

Shipment (Tender) number shall be sent to EDI on the L11 segment (position 30) where L1101 is the Tender Number and L1102 is CT.

R Invoice_Number The number assigned to the invoice by the Contractor

VARCHAR(25)

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

Delivery_Timestamp

The date/time stamp when the package was successfully delivered to government customer/representative. (e.g. 07-04-2020 14:23) Reference EDI 214 AT705, AT706.

This information is the same as what is used in Shipping Information Report (SI)

Date and Time Format MM-DD-YYYY HH:MM (24 Hour Clock). If Zulu/GMT is not possible then provide the Time Zone in the associated TmZone column

Accounting_Category

Government map able code that identifies the Accounting Category group. (e.g.

Accessorial)

Enter the code description found in the Accounting Type Data Field tables. Descriptions on the carrier report must exactly match those outlined in this Appendix or they will be rejected.

R Accounting_Type Accessorials Code identifying the individual accessorial line item.

VARCHAR(30) 3-4 letter code outlined in the Accessorial Codes to use for Accounting Type Data Field tables found on the following pages. The codes reflect those found in Attachments 2a & 2c. Codes on the carrier report must exactly match those outlined in this Appendix or they will be rejected.

Accounting_Amount

Charge amount for each individual Accessorials associated with the shipment in USD. (e.g. 209.89) Each accessorial must be itemized and not rolled up into the transportation cost.

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

Accessorial Codes to use for Accounting Type Data Field

International Accessorial

Code International Accessorial Code Description

003 Account Number Correction Charge

015 Bahrain Certificate of Origin Fee

020 Address Correction

520 Oversized Freight

535C Pickup Beyond Service Area Charge CONUS

535O Pickup Beyond Service Area Charge OCONUS

593 Re-bill Charge

665 Saturday Delivery

670 Saturday Pickup

675 Signature Service

AAT Djibouti Harmonized System Code Fee

ADI Attempted Delivery International

API Attempted Pickup International

CIS Constant Surveillance and Custody Service

DOC Djibouti Documentation Fee

ECR Escorts and Couriers (NOTE Do not confuse with CIS)

EVC Excess Valuation purchased in $100 increments

HAZ Hazardous Material Handling

HMA Accessible Hazardous Materials

HMI Inaccessible Hazardous Materials

LDS Life and Death Emergency On-board Courier Service

PDYC Delivery Beyond Service Area Charge CONUS

PDYO Delivery Beyond Service Area Charge OCONUS

RID Dry Ice Re-icing

RIE Wet Ice Re-icing

RWI Re-weigh and/or Re-measure International

Z11 Cold Chain/Refrigeration Service (Temperature Maintained at 32 degrees Fahrenheit and below)

Z12 Cold Chain/Refrigeration Svc (Temp Maintained at or above 33 degrees F and at or below 49 degrees F)

Z13 Cold Chain/Refrigeration Svc (Temp Maintained at or above 50 degrees F and at or below 67 degrees F)

Z14 Cold Chain/Refrigeration Svc (Temp Maintained at or above 68 degrees F and at or below 86 degrees F)

1 October 2022 Domestic Accessorial Code Domestic Accessorial Code Description 520 Oversized Freight

675 675 Signature and Tally Record

ADD Attempted Delivery Domestic

APD Attempted Pickup Domestic

CIS Constant Surveillance and Custody Service

DEL Overnight delivery service before 1200 pm

ECR Escorts/Couriers

EVC Excess Valuation

HAZ Hazardous

HOL Holiday Pickup/Delivery

PUD Pickup/Delivery Before/After Normal Operating Hours

LDS Life and Death Emergency On-board Courier Service

RIE Re-icing required for wet ice

RID Re-icing required for dry ice

RWD Re-weight and/or Re-measure Domestic

SAT Saturday Pickup/Delivery

RCC Reconsignment or Diversion

RCL Redelivery

Z11 Cold Chain/Refrigeration Service (Temperature Maintained at 32 degrees Fahrenheit and below)

Z12 Cold Chain/Refrigeration Svc (Temp Maintained at or above 33 degrees F and at or below 49 degrees F)

Z13 Cold Chain/Refrigeration Svc (Temp Maintained at or above 50 degrees F and at or below 67 degrees F)

Z14 Cold Chain/Refrigeration Svc (Temp Maintained at or above 68 degrees F and at or below 86 degrees F)

Additional Task Order Accessorial Code

Additional Task Order Accessorial Code Description

DRY Dry Cargo Over-packing

CHL Chill and Produce Over-packing

FRZ Frozen Goods Over-packing

BGS Bagged Salad Over-packing

EXP Expedited Service

Code for Other Approved Charges

Other Approved Charges Code Description

EBL Charges assessed to shipment via a government approved eBill