Appendix_1.5_Reporting_Instructions.pdf

Appendix 1.5 – Monthly Report Instructions

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 Invoiced 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. File delivered should be in a flat file (.txt) using pipe (|) delimited format with headers to submit monthly data. (See Data File Table Legend below).

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

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

6. Itemized Accounting elements (Accessorial, Surcharge, Duty and Tax etc) should be provided in a separate file and in the format of table listed below.

7. Delay Codes shall be provided in a separate file and in the format of the table listed below.

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

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

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

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

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

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

13. All weights should be in U.S. Pounds (Lb)

14. Contract type – Identified within the dataset (GHS for Global Heavy Weight Service)

15. Contract Sub Type – Identified within the dataset (IT for International , DT for Domestic)

16. 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. File Type – identifies type of data contained in file (SI = Shipment Information, AI = Accessorial, DI = Delays)

e. Submission date - date file was sent

f. Example: 201505_XXXX_SI_20150615.xlsx represents shipment information files for deliveries made in May 2015. 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, SI indicates the type of report (Shipment Information), AI indicates (Accounting Information), DI indicates (Delay Information) and the file was submitted 15-Jun-2015 for the month of May 2015. Three (3) separate files will be sent for each file name for each file type; see examples below.

File Name: 201505_SMPL_SI_20150615.xlsx 201505_SMPL_AI_20150615.xlsx 201505_SMPL_DI_20150615.xlsx

This file format allows the carrier to send multiple contract type records in one file. For example SMPL can send CAT A and GHS shipment information in the same file rather than sending a separate file for each.

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

Handling of Corrections, Additions, and No Data:

1. Provide only the records in dataset that had changes from previous month.

2. Accessorial changes are to be made as accounting entries (Example to back out or undo a previous charge a new record should be created with the credit of equal amount )

3. If there are shipments for a specific month that were not on the original report, add them to the following month’s report rather than sending a revised report for the month in which they were delivered.

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

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

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 File

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

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

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.

R Contract_Type Identifies the particular contract type the record belongs to (e.g. CATA, GHS, NGDS etc…)

VARCHAR(7) None

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

DECIMAL(10,1

Weights shall be indicated in pounds.

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

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

VARCHAR(10

0)

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

R Contract_Line_Number Contract Line Item Number (CLIN) or Sub-Line Item Number (SLIN). Reference EDI 214 L1101, L1102

VARCHAR(25) None

W/A Contract_Sub_type Identifies the particular contracts sub type (e.g.

International or Domestic for GHS)

VARCHAR(50) Required for GHS Contracts. Indicates International or Domestic for the shipment

W/A 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

VARCHAR(10) Field is only required if Delivery Time is not in GMT/Zulu

R Delivery_Status Code which identifies if the delivery was on-time, late, excusably late or not deliverable. (The only acceptable codes are: OT, LT, EL, or ND) Reference EDI 214 AT701, AT702, AT703, AT704

VARCHAR(2) When delivery_status code LT, EL or ND is assigned, there must be an Exception/Delay entry that explains the reason for this status and the ultimate disposition of the item being shipped as necessary.

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

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

W/A Duties_and_Taxes_Amount Total charge for all duties and taxes charges associated with the shipment 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.

R Invoice_Date The date the invoice was issued. (e.g. 2018-10- 12)

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]

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.

R National_Account_Parent_Number Account number matching back to the parent agency of the shipper

VARCHAR(15) None

R National_Account_Sub_Group_Num ber

Account number matching back to the sub-agency of the shipper

VARCHAR(15) None

W/A Package_Dim_Height The dimensional height of the shipment in Inches DECIMAL(10,1

None

W/A Package_Dim_Length The dimensional length of the shipment in Inches DECIMAL(10,1

None

W/A Package_Dim_Width The dimensional width of the shipment in Inches DECIMAL(10,1

None

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

letter, package)

VARCHAR(10) None

R Pickup_Timestamp The date/time stamp when the package was picked up by the carrier from the shipper/government representative. (e.g. 2018-07- 04 14:23). Reference EDI 214 AT705, AT706

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

W/A 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

VARCHAR(10) Field is only required if Pickup_Time is not in GMT/Zulu.

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

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

DOHA, THUMRAIT, ROBINS AFB) Reference

EDI 214 N401

VARCHAR(30) Should not contain numeric or special characters.

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

VARCHAR(50) None

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

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

R Recipient_Zip_Postal_Code The zip code where the shipments was received.(e.g. 90210, 11746)

VARCHAR(5) Should be formatted to capture leading zeros.

Required for United States locations. May be left blank for OCONUS locations.

W/A Remarks Brief Explanation of Issues. Reference EDI 214 K101, K102

VARCHAR(25

5)

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

R Service_Category Field denoting the level of service performed (e.g. Express, ground, International etc)

VARCHAR(25) None

W/A Service_Type_Code Field denoting the detailed level of service performed ( e.g. EX2 , 2DM, GR etc)

VARCHAR(5) None

R Shipment_Amount Total amount charged for the shipment (not including discounts, duties/tax or surcharges) 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.

I/P Shipment_Reference Individual shipper assigned identifier; Contains the 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(10

0)

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.

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)

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

VARCHAR(30) Should not contain numeric or special characters.

R Shipper_Company The Company or Organization 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) None

R Shipper_Country_Code The country code from which the shipment was shipped. (e.g. JAP, USA) Reference EDI 214 N404

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

R Shipper_RDD_Timestamp The date/time stamp when the package was committed to be delivered by

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

W/A 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

VARCHAR(10) Required when not Zulu

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

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

R Shipper_Zip_Postal_Code The zip code from which the shipments was shipped (e.g., 90210, 11746)

VARCHAR(5) Should be formatted to capture leading zeros.

Required for United States locations. May be left blank for OCONUS locations.

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

R Accounting_Amount Total charge for all accessorial/surcharges charges associated with the shipment 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.

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.

R Total_Shipment_Amount Total net amount for the shipment (i.e. Shipment Amount + Duties/Taxes + Accessorials – Discounts/Rebates) in USD. (e.g. 209.89)

DECIMAL(10,2

Do not include “$” in data submission. Must be in US Dollars.

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

Delay Table Structure

W/A = Shall provide information “When Applicable”, otherwise leave this field blank

Delay Information (DI) Data File

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

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

855353492480)

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.

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]

R Contract_Type Identifies the particular contract type the record belongs to (e.g. CATA, GHS, NGDS etc…)

VARCHAR(7) None

W/A Contract_Sub_Type Identifies the particular contracts sub type (e.g.

International or Domestic for GHS)

VARCHAR(50) Required for GHS Contracts. Indicates International or Domestic for the shipment

R Delay_Code 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 authorized excusable exception codes listed in the Excusable Delay Codes and Descriptions table listed below. Only one code used per line item.

I/P 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.

GHS Excusable Delay Code and Descriptions

Excusable Delay Code Government Excusable Delay Code Description AOG Acts of God e.g. Earthquake, Flood, Hurricane/Typhoon

CUS

Shipment delivery delayed due to customs clearance issues beyond the contractor's control

DGP Shipment delayed due to improper dangerous goods paperwork from shipper HLD Shipment delayed due to a request from the customer to hold shipment HOL Shipment delayed due to holiday hours at receiving installation ICI Shipment delivery delayed due to incorrect consignee information given by shipper IEX Installation closed due to exercise

PKG

Shipment delayed due to improper packaging by customer, freight had to be repackaged

POC Shipment delivery delayed due to point of contact not available to accept delivery RCE Receiving office closed early, unable to deliver RSC Shipment delivery delayed due to customer request for rescheduling date of deliver RWY Shipment delivery delayed due to runway clearance issues, plane could not land SEC Shipment delivery delayed due to security purpose SRC Delayed due to strikes, trios, civil commotion WEA Shipment delivery delayed due to weather restrictions

Accounting Table Structure

W/A = Shall provide information “When Applicable”, otherwise leave this field blank

Accounting Information (AI) Data File

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

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

855353492480)

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.

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]

R Contract_Type Identifies the particular contract type the record belongs to (e.g. CATA, GHS, NGDS etc…)

VARCHAR(7) None

W/A Contract_Sub_Type Identifies the particular contracts sub type (e.g.

International or Domestic for GHS)

VARCHAR(50) Required for GHS Contracts. Indicates International or Domestic for the shipment

R Accounting_Category Government mappable code that identifies the Accounting Category group . (e.g. Accessorial, Surcharge, Discount, Duty / Tax etc )

VARCHAR(30) None

R Accounting_Type Accessorial/Surcharge/Discount Code identifying the individual accessorial line item

VARCHAR(30) None

R Accounting_Amount Total charge for all accessorial/surcharges charges associated with the shipment 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.