2022.02.14 Attachment 5 Reporting Dictionary.xlsx
XLSX spreadsheet 766 KB Posted
- Solicitation number
- HTC711-22-R-C003
About this file
This document contains a data dictionary and reporting requirements for a federal solicitation. The solicitation is for Next Generation Delivery Services (NGDS 2) under number HTC711-22-R-C003 to provide international and domestic commercial express package delivery services for the United States government. Services include door-to-door pick-up and delivery, transportation, in-transit visibility, and customs clearance for shipments weighing up to 300 pounds internationally and 150 pounds domestically within the continental US, and up to 300 pounds between the continental US and Alaska, Hawaii, and Puerto Rico, and within Alaska, Hawaii, and Puerto Rico. The period of performance is one four-year base period from October 2022 through September 2026 with one four-year option period from October 2026 through September 2030 and an additional six-month option period through March 2031. To be eligible for award, offerors must participate in the Civil Reserve Air Fleet program and maintain participation throughout contract performance. The solicitation will result in multiple Indefinite Delivery Indefinite Quantity contracts for international and domestic transportation services, with approximately five contracts awarded for domestic services. The reporting dictionary defines over 40 data elements that must be provided in contractor delivery data feeds to the government.
View the file
Other files
Show 40 more
Latest 50 of 58 listed here. See the rest on GovTribe.
On GovTribe
Work with this file
- Download the original file
- Contacts named in this file
- Similar government files
- Ask GovTribe AI about this file
Text version
1. Instructions
| 22-Jun-21 | |||
| Data Dictionary Structure | |||
| Column Position | Column Name | Instruction | |
| A | Data Feed File Type | Data file type such as "Shipment_Detail", "Shipment_Accessorials", "Shipment_Delays", "Delivery_Exception_Codes_Ref", etc. | |
| B | Data File Column Sequence Number | Provide the name of the Data File that is being detailed in the following columns |
- Only include the Data File Name when describing a new Data File, otherwise leave blank C Data Element Name Steffey, Howard CIV USTRANSCOM J6: https://en.wikipedia.org/wiki/Data_element_name
| Provide the name of the data field provided in the report | |
| D | Data Element Description |
Steffey, Howard CIV USTRANSCOM J6: https://en.wikipedia.org/wiki/Data_element_definition
| The description or definition of a data element. Example data values may also be included. | ||
| E | Data Type | Each data field has a related data type. A data type is an attribute that specifies the type of data values to be stored. Here are several common data types: |
- CHAR(n) for fixed-length character fields where "n" represents the total number of characters to be stored.
- VARCHAR(n) for fields containing variable character lengths where "n" represents that maximum number of characters that can be stored.
- INTEGER for fields containing only whole numbers.
- DECIMAL(p,s) for fields containing decimal numbers. p=precision, which represents the maximum number of decimal digits to be stored. s=scale, which represents the number of decimal digits that are stored to the right of the decimal point. For example: DECIMAL(8,2) is a number that has 6 digits before the decimal and 2 digits after the decimal.
- DATE for fields containing dates
- TIMESTAMP for fields containing year, month, day, hour, minute, second, and fractional second values.
Populate this colum with just the data type only (e.g., CHAR, VARCHAR, DECIMAL, etc.), and then provide the length (i.e., "n", "p,s") in column D.
F Data Element Length Provide the maximum number of characters allowed for this field (e.g. YYYYMMDD for a date field would be reported as "6")
- Define the precision and scale (p,s) for decimal fields. Precision represents the maximum number of decimal digits to be stored and scale represents the number of decimal digits that are stored to the right of the decimal point. For example: DECIMAL(10,2) is a number that has 8 digits before the decimal and 2 digits after the decimal. If Invoice Cost is definfed as DECIMAL(10,2) this would denote a maximum amount of 99999999.99
| G | Data Element Format | Describe the format the data appears in (e.g. YYYY-MM-DD for a date field, cm or inches for a measurement field) |
| H | Mapped Government Business Term | Provide the required corresponding Appendix 6.10 Government Business Term this data element supports and cross-references back to. For example, if a contractor's "Tracking_Number" data element is the same as the Government's required "Contractor Unique Shipment Tracking Number" business term, then this corresponding data value is going to be "Contractor Unique Shipment Tracking Number". |
| I | Sample Data Element Value | A sample data value for each data element. |
HTC711-22-R-C003
Attachment 5 - Reporting Dictionary 8 February 2022
DESCRIPTION:
SOURCES: USTRANSCOM Commercial Services Branch (TCJ4-LC) and Metrics, Analysis & Sustainment Strategy Branch (TCJ4-LM) POC: Mr./Ms. xxxxxxxxxxxxxx, transcom.scott.tcj5j4.mbx.lc@mail.mil, transcom.scott.tcj5j4.mbx.lm@mail.mil (618) xxx-xxxx J5/4 See NGDS PWS Appendix 6.10, Section 6.10.2 for more information.
J5/4 NGDS Attachment #5 Data Feed Dictionary (Version 2021-06-21)
2. Data Dictionary Example
FILE: 2021.06.24 NGDS2 - Attachment 5 Reporting Dictionary.xlsx 22-Jun-21
| Data File Type | Data File Column Sequence Number | Data Element Name | Description | Data Type | Data Element Length | Data Element Format | Mapped Government Business Term | ||
| (from Appendix 6.10, mark N/A if none exists) | Sample Data Element Value | ||||||||
| Shipment_Detail | 1 | Shipper_Account_Nr | Account number for organization booking the shipment for movement. | VARCHAR | 35 | Shipper Account Number | |||
| Shipment_Detail | 2 | Billing_Account_Nr | Customer account number billed for the shipment movement, which can include accessorial charges. | VARCHAR | 35 | Freight Payer Account Number | 0000A365X6 | ||
| Shipment_Detail | 3 | Shipment_Group_ID | Represents a consolidation of multiple “child” shipment units into a single shipment unit. This is the generated shipment number assigned to this newly created consolidated shipment, and is the same in concept of a DoD “Lead Transportation Control Number (TCN)”. | VARCHAR | 9 | Lead Shipment Number | |||
| Shipment_Detail | 4 | Shipment_Reference | DoD Transportation Control Number (TCN), Federal agency-assigned tracking number, or order number | VARCHAR | 100 | Transportation Control Number (TCN) | |||
| Shipment_Detail | 5 | Tracking_Number | Tracking number assigned to a single shipment unit used to support shipment movement tracking. | VARCHAR | 15 | Contractor Unique Shipment Tracking Number | |||
| Shipment_Detail | 6 | Invoice_Number | Unique number assigned to a customer billing invoice. | VARCHAR | 25 | Invoice Identifier | 1XR365X60356656408 | ||
| Shipment_Detail | 7 | Invoice_Date | Greenwich Mean Time (GMT) Date customer invoice was generated. | DATE | 10 | YYYY-MM-DD | Invoice Date | 2021-06-22 | |
| Shipment_Detail | 8 | NGDS_Rate_Profile_Code | Code that identifies the NGDS shipment rate profile. | NGDS Rate Profile | |||||
| Shipment_Detail | 9 | NGDS_Program_Flag | Identifies NGDS shipments and used to filter out non-NGDS charges. Valid values are: "Y" (NGDS) and "N" (non NGDS). | CHAR | 1 | N/A | |||
| Shipment_Detail | 10 | Domestic_International_Flag | Identifies whether a shipment movement is domestic or international | CHAR | 1 | Domestic_International_Flag* | D | ||
| Shipment_Detail | 11 | Service_Level_Code | Contractor service level code associated with shipment movement. Examples of actual service levels include: 1) Expres Next Day First AM, and 2) Domestic Ground | CHAR | 3 | Service Level Code | |||
| Shipment_Detail | 12 | Total_Shipment_Charge | Total amount charged for the shipment to include accessorials | DECIMAL | 12,2 | 9999999999.99 | Total Shipment Charges | 6.87 | |
| Shipment_Detail | 13 | Duties_and_Taxes_Amount | Total duties and taxes assessed to the shipment. | DECIMAL | 12,2 | 9999999999.99 | Total Shipment Duties and Taxes Amount | ||
| Shipment_Detail | 14 | Accessorial_Charges | Accessorial charges are fees added to a freight bill for additional services provided. These are a la carte, or per service, and outside of standard shipping and receiving. | DECIMAL | 12,2 | 9999999999.99 | Total Shipment Accessorials Amount | ||
| Shipment_Detail | 15 | Discount_Amount | total amount discounted for the shipment | DECIMAL | 12,2 | 9999999999.99 | Total Shipment Discount Amount | ||
| Shipment_Detail | 16 | Net Charges | DECIMAL | 12,2 | 9999999999.99 | Total Shipment Net Amount | |||
| Shipment_Detail | 17 | Surcharge_Code | VARCHAR | 10 | Surcharge_Code | ||||
| Shipment_Detail | 18 | Pickup_Date_Time | Greenwich Mean Time (GMT) Date and time the shipment was actually picked up by the carrier. | TIMESTAMP | 19 | YYYY-MM-DD hh:mi:ss | Actual Pickup Timestamp | ||
| Shipment_Detail | 19 | Delivery_Date_Time | Greenwich Mean Time (GMT) Date and time the shipment was actually delivered to the recipient address. | TIMESTAMP | 19 | YYYY-MM-DD hh:mi:ss | Actual Delivery Timestamp | ||
| Shipment_Detail | 20 | Contractual_Delivery_Date_Time | Contractual Greenwich Mean Time (GMT) Date and time the shipment is supposed to be delivered on or before. | TIMESTAMP | 19 | YYYY-MM-DD hh:mi:ss | Contractual Delivery Timestamp | ||
| Shipment_Detail | 21 | Delivery_Commitment_Date_Time | Contractor Committed Greenwich Mean Time (GMT) Date and time the shipment is supposed to be delivered on or before. | TIMESTAMP | 19 | YYYY-MM-DD hh:mi:ss | Delivery Commitment Timestamp | ||
| Shipment_Detail | 22 | Delay_Code | Code identifying the reason for the delivery of a shipment being late. | VARCHAR | 10 | Shipment Delay Code | |||
| Shipment_Detail | 23 | Package_Type | Package type code identifying the shipment package type | VARCHAR | 10 | Package Type Code | |||
| Shipment_Detail | 24 | Package_Quantity | The number of pieces contained in the shipment | INTEGER | 10 | Package Quantity | |||
| Shipment_Detail | 25 | Package_Dim_Length | Package length in inches. | DECIMAL | 10,0 | Measured in inches | Shipment Length Dimension | 12 | |
| Shipment_Detail | 26 | Package_Dim_Width | Package width in inches. | DECIMAL | 10,0 | Measured in inches | Shipment Width Dimension | 12 | |
| Shipment_Detail | 27 | Package_Dim_Height | Package height in inches. | DECIMAL | 10,0 | Measured in inches | Shipment Height Dimension | 12 | |
| Shipment_Detail | 28 | Actual_Weight | Actual shipment weight in pounds. | DECIMAL | 10,0 | Measured in pounds | Shipment Actual Weight | 3 | |
| Shipment_Detail | 29 | Billed_Weight | Actual or dimensional weight in pounds--whichever was used for shipment movement billling. | DECIMAL | 10,0 | Measured in pounds | Shipment Billed Weight | ||
| Shipment_Detail | 30 | Shipper_Company | The agency or company of the shipper | VARCHAR | 100 | Shipper Agency | |||
| Shipment_Detail | 31 | Shipper_Name | Individual’s name who is the shipper’s point of contact (POC) | VARCHAR | 100 | Shipper POC Name | |||
| Shipment_Detail | 32 | Shipper_City | The city where the shipment originated | VARCHAR | 100 | Shipper City | |||
| Shipment_Detail | 33 | Shipper_State | The state where the shipment originated | CHAR | 2 | Shipper State | |||
| Shipment_Detail | 34 | Shipper_Zip_Postal_Code | The postal code where the shipment originated | VARCHAR | 5 | Shipper Postal Code | |||
| Shipment_Detail | 35 | Shipper_Country | Shipper Country Code where the shipment originated | CHAR | 2 | Shipper Country Code | |||
| Shipment_Detail | 36 | Proof_of_Delivery_Recipient | The name of the recipient who signed for the package | VARCHAR | 100 | Proof Of Delivery Recipient Name | |||
| Shipment_Detail | 37 | Recipient_Company | VARCHAR | 100 | Recipient_Company | ||||
| Shipment_Detail | 38 | Recipient_Name | The name of the recipient who signed for the package | VARCHAR | 100 | Recipient POC Name | |||
| Shipment_Detail | 39 | Recipient_City | The city where the shipment was delivered to | VARCHAR | 100 | Recipient City | |||
| Shipment_Detail | 40 | Recipient_State | The state where the shipment was delivered to | CHAR | 2 | Recipient State | |||
| Shipment_Detail | 41 | Recipient_Zip_Postal_Code | The postal code where the shipment was delivered to | VARCHAR | 5 | Recipient Postal Code | |||
| Shipment_Detail | 42 | Recipient_Country | Recipient Country Code where the shipment was delivered to | CHAR | 2 | Recipient Country Code | |||
| Shipment_Detail | 43 | Pickup_Residential_Flag | Used to identify shipments that were picked up at a residential address | CHAR | 1 | Shipment Pickup Residential Indicator | |||
| Shipment_Detail | 44 | Delivery_Residential_Flag | Used to identify shipments that were delivered to a residential address | CHAR | 1 | Shipment Delivery Residential Indicator |
DESCRIPTION: NGDS Contractor-Provided Data Elements Dictionary PARTIAL EXAMPLE
SOURCES: USTRANSCOM Commercial Services Branch (TCJ4-LC) and Metrics, Analysis & Sustainment Strategy Branch (TCJ4-LM) POC: Mr./Ms. xxxxxxxxxxxxxx, transcom.scott.tcj5j4.mbx.lc@mail.mil, transcom.scott.tcj5j4.mbx.lm@mail.mil (618) xxx-xxxx J5/4 Partial Example Dictionary
Business Terms
| Version: 2021-06-22 | |||
| Nr | Business Term | Description | Data Constraints |
| 1 | Contractor Unique Shipment Tracking Number | Contractor generated shipment tracking number assigned to each shipment. Used as a shipment identifier within the contractor-provided data feed and for retrieving shipment information using the contractor’s Web-based shipment tracking application | Alphanumeric. Must be unique over time; or, the contractor shall provide additional data elements needed to relate shipment details with shipment events and charges (e.g., EDI 214A EDI/TPPS). Reference Appendix 6.10, section 6.10.1.3, “Reused Tracking Numbers” for more information. |
| 2 | Lead Shipment Number | Represents a consolidation of multiple “child” shipment units into a single shipment unit. This is the generated shipment number assigned to this newly created consolidated shipment, and is the same in concept of a DoD “Lead Transportation Control Number (TCN)”. | Alphanumeric |
| 3 | Invoice Date | The date the invoice was issued | Date in GMT/Zulu |
| 4 | Invoice Identifier | The number assigned to the invoice by the Contractor | Alphanumeric |
| 5 | |||
| 6 | Freight Payer Account Number | Customer account number billed for the shipment movement, which can include accessorial charges. | Alphanumeric. Must be associated to contractor account number provided in reference information. |
| 7 | Shipper Account Number | Account number for organization booking the shipment for movement. | Alphanumeric |
| 8 | Transportation Control Number (TCN) | A 17-position alphanumeric character set assigned to control a shipment throughout the transportation cycle of the Defense Transportation System., Many carriers store this shipper-provided TCN in one or more customer reference data fields/elements. | Alphanumeric |
| 9 | Rate Profile | Method to determine associated NGDS Profile. | Alphanumeric |
| 10 | Actual Pickup Timestamp | Date/Time when the contractor physically picked up the shipment from the shipper | Date in GMT/Zulu. Must be before/equal to actual delivery date. |
| 11 | Actual Delivery Timestamp | Date/Time when the contractor successfully delivered the shipment to the recipient. | Date/Time in GMT/Zulu |
| 12 | Contractual Delivery Timestamp | Date/Time when the shipment was contractually delivered to the recipient (e.g., first delivery attempt or actual delivery). | Date/Time in GMT/Zulu |
| 13 | Delivery Commitment Timestamp | Ability to determine Date/Time when the package was contractually committed to be delivered. | Date/Time in GMT/Zulu |
| 14 | Proof Of Delivery Recipient Name | The name of the recipient who signed for the package | Alphanumeric |
| 15 | Package Type Code | Contractor package type code identifying the shipment package type. | Alphanumeric. Must be associated to contractor package type code provided in reference information 6.10.4.11 |
| 16 | Package Quantity | The number of pieces contained in the shipment | Numeric. Must be greater than 0. |
| 17 | Shipment Length Dimension | The dimensional length of the shipment in inches. | Numeric. Must be in inches or the contractor needs to provide a methodology for determining this value. |
| 18 | Shipment Height Dimension | The dimensional height of the shipment in inches. | Numeric. Must be in inches or the contractor needs to provide a methodology for determining this value. |
| 19 | Shipment Width Dimension | The dimensional width of the shipment in inches. | Numeric. Must be in inches or the contractor needs to provide a methodology for determining this value. |
| 20 | Shipment Billed Weight | Billed charged weight of the shipment in pounds (Actual or dimensional weight, whichever is greater) | Numeric. Must be in pounds or the contractor needs to provide a methodology for determining this value. |
| 21 | Shipment Actual Weight | Actual weight of the shipment in pounds. | Numeric. Must be in pounds or the contractor needs to provide a methodology for determining this value. |
| 22 | Total Shipment Accessorials Amount | Ability to determine total Amount charged for the shipment accessorials | Numeric. Must be in U.S. Dollars. |
| 23 | Total Shipment Freight Amount | Ability to determine total Amount charged for the shipment (not including discounts, duties/tax or accessorials) | Numeric. Must be in U.S. Dollars. |
| 24 | Total Shipment Discount Amount | Ability to determine total amount discounted for the shipment | Numeric. Must be in U.S. Dollars. |
| 25 | Total Shipment Net Amount | Ability to determine total NET Amount charged for the shipment | Numeric. Must be in U.S. Dollars. |
| 26 | Service Level Code | Contractor service level code associated to shipments | Alphanumeric. Must be associated to contractor service level code provided in reference information. |
| 27 | Shipper Agency | The agency or company of the shipper | Alphanumeric |
| 28 | Shipper Unit/Organization Name | The organization or unit name of the shipper. | Alphanumeric |
| 29 | Shipper POC Name | Individual’s name who is the shipper’s point of contact (POC) | Alphanumeric |
| 30 | Shipper City | The city where the shipment originated | Alphanumeric |
| 31 | |||
| 32 | Shipper State | The state where the shipment originated | Alphanumeric. Required for U.S. shipper addresses. |
| 33 | Shipper Postal Code | The postal code where the shipment originated | Alphanumeric. Required for USA Shipper Addresses. |
| 34 | Shipper Country Code | Contractor Shipper Country Code where the shipment originated | Alphanumeric. Must consistently use one of the following three standard country code sets: 1) ISO 3166, 2) GENC, or 3) FIPS10-4. See section 6.10.4.12, “Country Code Reference Elements” for more information. |
| 35 | Shipment Pickup Residential Indicator | Used to identify shipments that were picked up at a residential address | Alphanumeric |
| 36 | Recipient Agency | The agency or company receiving the shipment | Alphanumeric |
| 37 | Recipient Unit/Organization Name | The Unit/Organization Name the shipment was delivered to | Alphanumeric |
| 38 | Recipient POC Name | Individual’s name who is the recipient’s point of contact (POC) | Alphanumeric |
| 39 | Recipient City | The city where the shipment was delivered to | Alphanumeric |
| 40 | Recipient State | The state where the shipment was delivered to | Alphanumeric. Required for U.S. recipient addresses. |
| 41 | Recipient Postal Code | The postal code where the shipment was delivered to | Alphanumeric. Required for U.S. recipient addresses. |
| 42 | Recipient Country Code | Contractor Recipient Country Code where the shipment was delivered to | Alphanumeric. Must consistently use one of the following three standard country code sets: 1) ISO 3166, 2) GENC, or 3) FIPS10-4. See section 6.10.4.12, “Country Code Reference Elements” for more information. |
| 43 | Shipment Delivery Residential Indicator | Used to identify shipments that were delivered to a residential address | Alphanumeric |
image1.jpeg image2.png image3.jpg
File details come from the government source that posted it. Updated .