2021.07.27 NGDS2 - Appendix 6.10 Monthly Transaction Data Feed Requirements.docx

HTC711-22-R-C003

PWS Appendix 6.3 1 October 2022

Appendix 6.10 Monthly Transaction Data Feed Requirements

6.10.1. Data Feed Processing Business Rules. The contractor shall provide the following sets of business rules that address how to correctly processprocess, and query data records being provided as part of the data feed:

6.10.1.1. Accounting Methodology. Addresses how to properly process a shipment’s total charges when multiple records are involved such as a line item detail record and associated records containing accessorial charges. The business rules need to identify processing methodology such as aggregating charges across multiple related records in order to obtain the accurate total charge for each shipment, and how records containing negative charge amounts (e.g., credits or offsets) should be processed.

6.10.1.2. Record Modification Processing. Methodology for processing record updates, deletes, and canceled transactions (i.e., shipments that were booked and then movement was canceled).

6.10.1.3. Reused Tracking Numbers. Shipment tracking numbers are often used as or part of a primary key for identifying shipment record relationships across data sets such as associating a shipment details data record with those corresponding records contained in a shipment movement events database table (a one-to-many relationship). Reused tracking numbers (i.e., those reassigned to a different shipment in the future) could easily corrupt the use of a tracking number as a relationship key because a shipment assigned a reused tracking number could then be incorrectly associated to other shipments’ movement events producing inaccurate reporting results. This problem is often resolved by using two or more data elements as a relationship key that accurately identify each unique shipment. For example, if no two unrelated shipments never have the same combination of a tracking number, origin country code, and destination country code assigned--and these three data elements exist in all applicable data sets—then the combination of these three data elements could be used to accurately link related records such as a shipment’s details (e.g., customer, origin, destination, weight, shipment charges, etc.) with shipment events (e.g., carrier pickup date, arrived at terminal, etc.). If the contractor reuses shipment tracking numbers, then the contractor needs to provide business rules for combining data feed record data elements that would make up a valid primary key for identifying associated records among the applicable data sets being provided by the data feed.

6.10.1.4. Identifying Shipments Returned to Shipper. Business rules or a record identifier or type of service code (see attachment 5) that can be used to easily identify shipments returned back to the shipper/origin, and the reason why.

6.10.1.5. Identifying Non-NGDS Data Records. Non-NGDS shipments are those not meeting the criteria outlined in section 2.15.3 of the NGDS PWS. Examples of non-NGDS data records include shipment packaging supply charges, and charges for shipments that don’t meet NGDS movement criteria.

6.10.1.6. Identifying Letter Shipments. Provide criteria for identifying letter shipments such as packaging type, dimension and weight limits used specifically for letters.

6.10.1.7. Identifying Residential Shipment Pickups and/or Deliveries. Shipment details for packages being picked up and/or delivered within residential areas could potentially contain personally identifiable information (PII), so the Government needs the ability to quickly and easily identify these types of shipmentsidentify these types of shipments quickly and easily. This is whyTherefore section 6.10.3.8, Shipment Detail Data Elements, contains both a “Shipment Pickup Residential Indicator” and a “Shipment Delivery Residential Indicator”.

6.10.2. Data Feed Data Dictionary (i.e., Attachment #5). The contractor shall provide the government a data feed data dictionary containing comprehensive data feed metadata (i.e., “data about data”) such as the record layout for each data file/report outlined in section 6.10.3. Each record layout needs to contain a list of the data elements, definition for each data element and data type (e.g., VARCHAR(55), DECIMAL(18,4), etc.) The dictionary also needs to include an entity relationship diagram that identifies primary/foreign keys used to define relationships among the transaction and reference data sets. The contractor shall provide to the Government no later than 30 days before the contract begins or an updated data dictionary 30 days prior to any changes being implemented within the transaction and reference data feeds to include changes to record layouts, data type definitions, data processing business rules modifications, and data set relationships.

6.10.3. Monthly Transaction Data Feed.

6.10.3.1. Frequency. The Contractor shall submit shipment transaction level data to the Government no later than the 20th of each month for the previous month’s activity (shipments invoiced during the previous month). If there are no invoices during the reporting period, an email stating “Negative Shipment Information Report for month/year is hereby submitted” shall be provided to the Program Management Office (PMO). Negative shipment report will be sent to transcom.scott.tcj5j4.mbx.lm@mail.mil

6.10.3.2. Method of Delivery. The Contractor shall work with the PMO to develop the exact mechanism to be used (e.g. SFTP - Secure File Transfer Protocol) and the frequency of which to transmit data in a way that complies with Government security requirements. All other transmission methods must be approved on a per case basis by the PMO.

6.10.3.3. File Format. The Monthly Shipment Transaction Reports shall be submitted in an electronic format utilizing American Standard Code for Information Interchange (ASCII) text file type. The ASCII files can be created in fixed width/length format or in pipe-delimited format (i.e., “|”). All other data file types must be approved on a per case basis by the PMO.

6.10.3.4. Report Errors. In the event that errors (e.g. missing information) are found in the Monthly Transaction Data Feed submitted by the Contractor, the Government shall provide written notice and a description of the error via email to the Contractor. The Contractor shall have two business days to submit corrections for any errors (See File Resubmissions 6.10.3.6). The Contractor may be granted additional time, provided an action plan has been submitted to and approved by the PMO.

6.10.3.5. Report Changes. In the event that the Contractor wants to make changes to the structure or delivery time period they must coordinate with the PMO 30 days in advance of the desired implementation date, providing samples of the new format.

6.10.3.6. File Resubmissions. Any files resubmitted due to errors must be sent back using the same file name. The new file will replace the original and its contents. The Contractor shall return the file within the two business days unless an action plan has been submitted to and approved by the PMO.

6.10.3.7. Record Corrections. In the event the Contractor needs to UPDATE a shipment level record the Contractor will make the change to the shipment information in the original file and resubmit to the PMO using a different filename and in the same file format as the original. In the event the Contractor needs to DELETE a shipment level record the Contractor will remove the shipment information from the original file and resubmit to the PMO using the same filename and same file format as the original.

6.10.3.8. Report Accuracy Assessment. The assessment will be conducted in a two phasetwo-phase process. Phase one will determine if the Monthly Transactional Data Feed was received by the 20th of the month. Phase two will calculate the accuracy of the required fields see below 6.10.3.9. Shipment Detail Data Elements. Accuracy of fields will be measured with an overall acceptance percentage to be no less than 85% per month with a plan to improve visibility and accuracy of the data if below the acceptable percentage amount.

6.10.3.8.1. File Receipt Issues validates for corrupt files, bad file names and incorrect file format. If any of these issues are identified an update file will be required from the Contractor and the number of records in each file which failed will be counted individually.

6.10.3.8.2. File Check Issues validates the shipment transaction and reference data provided by the Contractor to include file structure, content, or business rules applied to the data (i.e. Delivery date must be a real date and on or after pickup date, negative weights unrecognized country codes). The number of records in each file which failed will be counted individually. Comment by Mannino, John CIV USTRANSCOM J5J4: This PWS change was a result of Market Research RFI responses

6.10.3.8.3. Transaction Record Issues validates the required fields, location information, unknown codes and Orphans (i.e. Orphans are Accessorials or Delays without Detail Records). The number of records in each file which failed will be counted individually. (See 6.10.3.9.).

6.10.3.8.4. Example Government-Defined Report Accuracy Assessment – Phase Two

6.10.3.9. Shipment Detail Data Elements. The Contractor shall submit shipment transaction details as part of the shipment transaction level data. Shipment detail data provides an entry per shipment describing key attributes. The provided shipment detail data can be in multiple data files in the approved format (see 6.10.3.3. File Format). At a minimum, Tthe files must be able to support at least the following data elements per their Required Property attributes that are described in business terms. All other data elements must be approved on a per case basis by the PMO.:

6.10.3.9. Shipment Delays Data Elements. The Contractor shall submit shipment delays data as part of the shipment transaction level data. Shipment delays data provides delay information about each shipment. A shipment can have zero-to-one associated records in the shipment delays file. The provided shipment delays data can be in multiple data files in the approved format (see 6.10.3.3. File Format). The files must be able to support at least the following data elements and must contain only records that are associated to the shipment details data:

6.10.3.10. Shipment Accessorials Data Elements. The Contractor shall submit shipment accessorials data as part of the shipment transaction level data. Shipment accessorials data provides accessorial information about each shipment. A shipment can have zero-to-many associated records in the shipment accessorials file. The provided shipment accessorials data can be in multiple data files in the approved format (see 6.10.3.3. File Format). The files must be able to support at least the following data elements and must contain only records that are associated to the shipment details data:

6.10.4. Reference Reports

6.10.4.1. Frequency. The Contractor shall submit shipment transaction reference data to the Government no later than 30 calendar days after award, before first use of a reference element, and when any changes occur to the files. Changes include file structure, content, or business rules applied to the data.

6.10.4.2. Method of Delivery. The Contractor shall work with the PMO to develop the exact mechanism to be used (e.g. SFTP - Secure File Transfer Protocol) and the frequency of which to transmit data in a way that complies with Government security requirements. All other transmission methods must be approved on a per case basis by the PMO.

6.10.4.3. File Format. The Transaction Reference Reports shall be submitted in an electronic format utilizing American Standard Code for Information Interchange (ASCII) text file type. The ASCII files can be created in fixed width/length format or in pipe-delimited format. All other data file types must be approved on a per case basis by the PMO.

6.10.4.4. Report Errors. In the event that errors (e.g. missing information) are found in the Monthly Transaction Data Feed submitted by the Contractor, the Government shall provide written notice and a description of the error via email to the Contractor. The Contractor shall have two business days to submit corrections for any errors (See File Resubmissions 6.10.4.6). The Contractor may be granted additional time, provided an action plan has been submitted to and approved by the PMO.

6.10.4.5. Report Changes. In the event that the Contractor wants to make changes to the structure or delivery time period they must coordinate with the PMO 30 days in advance of the desired implementation date, providing samples of the new format.

6.10.4.6. File Resubmissions. Any files resubmitted due to errors must be sent back using the same file name. The new file will replace the original and its contents. The Contractor shall return the file within the two business days unless an action plan has been submitted to and approved by the PMO.

6.10.4.7. Record Corrections. In the event the Contractor needs to UPDATE a shipment level record the Contractor will make the change to the shipment information in the original file and resubmit to the PMO using a different filename and in the same file format as the original. In the event the Contractor needs to DELETE a shipment level record the Contractor will remove the shipment information from the original file and resubmit to the PMO using the same filename and same file format as the original.

6.10.4.8. Delivery Exception Codes Reference Elements. Contractor will provide a list of delay codes associated with each Government delivery exception code (see 6.10.4.8.1). Only those contractor-provided delay codes that map to a Government-identified delivery exception code will be determined excusable for purposes of determining on-time delivery performance. The provided reference data must be in a single data file that contains at least the following elements:

6.10.4.8.1. Government-Defined Standard Delivery Exception Codes

6.10.4.9. Accessorial Codes Reference Elements. Contractor will provide a list of accessorial codes used. The provided reference data must be in a single data file that contains at least the following elements:

6.10.4.9.1. Government-Defined Standard Accessorial Categories

Government Accessorial Category

Address Correction

Adjustments

Constant Surveillance

Credit/Refund

Customs Fees

Dangerous Goods

Debit

Declared Value

Discount

Dry Ice Fee

Exceeds Dimension

Freight Amount

Handling

Insurance

International Duty/VAT

Other Charges

Out of Area

Peak/Seasonal Charge

Pickup/Delivery Attempt

Remote Delivery

Residential Pickup

Residential Delivery

Saturday Pickup

Saturday Delivery

Shipping Materials

Storage Fee

Tariff/Brokerage Fee

Taxes

6.10.4.10. Service Level Reference Elements. Contractor will provide a list of service level codes used. The provided reference data must be in a single data file that contains at least the following elements:

6.10.4.10.1. Government-Defined Standard Service Levels

Government Service Level Category

Domestic Express Next Day First AM

Domestic Express Next Day Mid-Morning

Domestic Express Next Day Afternoon

Domestic Express Second Day AM

Domestic Express Second Day

Domestic Express Third Day

Domestic Ground

Domestic Ground Residential

International Express Service

6.10.4.11. Package Type Codes Reference Elements. Contractor will provide a list of package type codes used. The provided reference data must be in a single data file that contains at least the following elements:

6.10.4.11.1. Government Defined Standard Package Type Reference Elements

6.10.4.12. Country Code Reference Elements. The contractor will either identify what standard country codes (ISO 3166, GENC, or FIPS10-4) are used in the transactional and reference data or provide a country code reference data if they are using non-standard country codes. Provided non-standardized country code reference data must map to one of the following three standard code sets: 1) ISO 3166, 2) GENC, or FIPS 10-4.

6.10.4.12.1. Example Country Code Cross-Reference Data Set.

6.10.4.13. International Country Holiday List Reference Elements. The contractor will provide a list of recognized holiday dates per country that is serviced. Any holidays not identified will not be counted as an excused delay and will be considered a normal business day.

6.10.4.13.1. Example Country Holiday Reference Data Set.

6.10.4.14. International Weekends Reference Elements. The contractor will provide a list of recognized international weekend dates per country that is serviced. If the contractor does not provide then all countries are assumed to be Saturday and Sunday.

6.10.4.14.1. Example International Weekends Reference Data Set.

6.10.4.15. Domestic Transit Times Reference Elements. The Contractor shallwill provide the capability to determine the contracted Delivery Commitment Timestamp to measure performance. methodology and any If the government is to calculate the Delivery Commitment Timestamp, the Contractor shall provide supporting reference data used to make this calculation.e the domestic transit time required to measure performance. This would potentially include postal code mappings, performance matrixes, package types, service levels, and other information to support domestic transit time calculations and performance assessment. Any record that cannot be accessed will be considered late.

6.10.4.16. Customer Hierarchy Records Shipper/Account Reference Elements. The Contractor shall work with the PMO to establish a numbering and naming convention for each customer account utilizing existing data infrastructure that allows for data reporting down to the lowest Government agency child-name. In accordance with para 2.24.4 of Attachment 1, Tthe Contractor shall provide customer details (e.g. point of contact, address, phone number, etc.) associated with a Shipper Account Number. Upon request of the PMO, the Contractor shall modify individual account hierarchy placement.

Note: See “NGDS_Government_Account_Hierarchy_Reference_vYYYY-MM-DD.xlsx” as an example.

6.10.4.17. Contractor Unique Data Elements. Should the contractor have additional data elements which enhance the data feed (i.e., “data about data”) such as element descriptions, lists of valid values and corresponding reference data, metadata will be required.

6.10.4.18. Account Information Report. The Contractor shall provide a semi-annual report listing all accounts that are aligned with the contract. This report shall be provided to the Contracting Officer in a Microsoft Excel format with the following field headings: Agency Name; Account Number; Unit Name; City; State; Postal Code; Customer First Name; Customer Last Name; Phone Number; Email; Date of Last Shipment; Date of Last Payment; and Past Due Balance. The Contractor shall submit account information reports to the Contracting Officer no later than 31 March and 30 September of each performance year under NGDS. See Appendix 1, para 2.24.6.

image1.png