CAT A PWS Exhibit B Detail Report Revision 1_15 Jul 08.doc
DOC document 173 KB Posted
- Attached to
- Cat A Cargo Delivery Services
- Solicitation number
- HTC711-08-R-0019
About this file
CAT A Exhibit B Revision 1_15 Jul 08
View the file
Other files
Show all 32
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
SOLICITATION: HTC711-08-R-0019-A00001
Exhibit b
DETAIL REPORT Required Data Element Fields
Standardized File Format and File Naming conventions:
1. Data file shall contain records for individual pieces DELIVERED in the month being reported, as well as, pieces returned as undeliverable.
2. Each record shall represent only one piece from a total shipment when the shipment is two or more pieces.
3. ASCII pipe | delimited file (See example of Shipment Information Data File below).
4. Fields shall be sequenced according to the tables provided below.
5. First row shall contain a header row identifying the field names. The first field does not have a delimiter in front and the last field does not have a delimiter at the end.
6. Each record must have end of record marker or a line feed.
7. There shall not be a text qualifier (example |test| or |”test”|)
8. If there is no data for a particular field then leave blank and use a delimiter to represent field.
9. If the data type of a field is numeric/date then only place valid data types. (e.g. Pickup Date; do not place “Unavailable”).
10. 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/english_country_names_and_code_elements.htm).
11. 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. Shipment Type – Identifies Shipment Type (A = Shipment Type Identifier)
e. File Type – identifies type of data contained in file (SI = Shipment Information, AI = Assessorial Information, CC = Country Reference, AC = Accessorial Code, EC = Exception/Delay Codes, DC = Delivery Status Code)
f. Submission date - date file was sent
g. Example: 200705_XXXX_A_SI_20070703.txt represents shipment information files for deliveries made in May 2007. 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 and the file was submitted 8-Jan-2008 for the month of Dec 07.
File Name: 200712_SMPL_A_SI_20080108.txt
Records: 2 records with a header record being the field names
File Info: File is saved in ASCII format using a pipe between each field.
Air_Bill_Number|Shipper_Account_Number|Shipper_Agency|Shipper_Unit_Name|Shipper_City|Shipper_State|Shipper_Country_Code|
Recipient_Unit_Name|Recipient_City|Recipient_State|Recipient_Country_Code|Notification_Date|Pickup_Date|Delivery_Date|Return_Date|
Time_in_Transit|Days_in_Transit|Delivery_Status|Exception_Codes|Pieces|Actual_Weight|Actual_Weight_Unit|Shipment_Cost |Content Description|Accessorial_Charges|TCN|Contract_Number|Theater_Name|Region|Recipient_DoDAAC|Shipper_DoDAAC|Charged_Weight|
Charged_Weight_Unit|Delivery_Signature|Lead Identifier|GBL|TAC|Shipment_Number|Aircraft_Body_Type|Remarks|Prime_Carrier_SCAC|
Sub_Carrier_SCAC|Program_Category|Notification_Date_TmZone|Pickup_Date_TmZone|Delivery_Date_TmZone|Return_Date_TmZone| Non_Transit_Time|Dim_Weight|
11111111|222222221|DLA|DDC|Harrisburg|PA|US|AMC|Baghdad||IZ|1/1/2008 12:32|1/2/2008 14:54|1/4/2008 13:09||46.25|OT||1|165|LBS|
855.60|SUPPLIES, MEDICAL||FB562173440231AXX|0066BK|||FM4420|SW3123|299|LBS|P.DELAPENA|||S1XM|11111111|WB||SMPL||||||||299|
11111112|222222221|DLA|DDC|Harrisburg|PA|US|TACOM|Doha||QA|1/15/2008 13:12|1/17/2008 8:11|1/20/2008 18:26||82.25|LT|RNR|2|
162|LBS|986.52|SERVOCYLINDER ASSEMBLY, HYDRAULIC||W91DAS73400205LXX|0066AQ|||W91DAS|SW3123|287|LBS|
Shipping/Receiving|||S1LM|11111112|NB||SMPL||||||||| 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
I/P = “If Provided” by the Shipper, 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 |
| 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 to each record in the detail report provided. [Primary Key of the Table] |
| 2 |
| 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) |
| 3 |
| 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…) |
| VARCHAR(35) |
| Must be provided as an alpha… not numeric. |
| 4 |
| I/P |
| Shipper_Unit_Name |
| Unit or Organization name. (e.g. TACC, 597th TG, 375th LRS) |
| VARCHAR(50) |
| If not a DOD or government organization, then leave blank. |
| 5 |
| R |
| Shipper_City |
| The city from which the package was shipped (e.g. PORTSMOUTH, BOISE) |
| VARCHAR(30) |
| 6 |
| W/A |
| Shipper_State |
| The code which identifies the state from where the package was shipped (e.g. ID, VA, TX) |
| VARCHAR(2) |
| Provide for CONUS shipper locations. Must be a 2 Digit Code. May be left blank for OCONUS locations |
| 7 |
| R |
| Shipper_Country_Code |
| The country code from which the shipment was shipped. (e.g. IQ, JP, US) |
| CHAR(2) |
| Must be a 2 Digit ISO 3166-1-alpha-2 code country codes. |
| 8 |
| I/P |
| Recipient_Unit_Name |
| Unit or Organization name. (e.g. TACC, 597th TG, 375th LRS) |
| VARCHAR(50) |
| If not a DOD or government organization, then leave blank. |
| 9 |
| R |
| Recipient_City |
| The city where the shipment was received. (e.g. DOHA, THUMRAIT, ROBINS AFB) |
| VARCHAR(30) |
| 10 |
| W/A |
| Recipient_State |
| This code which identifies the state where the shipment was received. (e.g. TX, WA) |
| CHAR(2) |
| Provide for CONUS Recipient locations. Must be a 2 Digit Code. May be left blank for OCONUS locations |
| 11 |
| R |
| Recipient_Country_Code |
| The country code where the shipment was received (e.g. IQ, JP, US) |
| CHAR(2) |
| Must be a 2 Digit ISO 3166-1-alpha-2 code country codes. |
| 12 |
| W/A |
| Notification_Date |
| The date and time of when the carrier was notified the package was available for shipping. (e.g. 06/20/2007 07:23) |
| DATETIME |
| Date and Time of Notification in Zulu/GMT. Format MM/DD/YYYY HH:MM. If Zulu/GMT is not possible then provide the package Notification Time Zone in a separate column (In column 43 labeled Notification_Date_TmZone). |
| 13 |
| R |
| Pickup_Date |
| The date the package was pickup by the carrier from the shipper/government representative. (e.g. 06/20/2007 09:23) |
| DATETIME |
| Date and Time of Pickup in Zulu/GMT. Format MM/DD/YYYY HH:MM. If Zulu/GMT is not possible then provide the package Pickup Time Zone in a separate column (In column 44 labeled Pickup_Date_TmZone). |
| 14 |
| R |
| Delivery_Date |
| The date the package was successfully delivered to government customer/representative. |
(e.g. 06/21/2007 10:23)
| DATETIME |
| Date and Time of Delivery in Zulu/GMT. Format MM/DD/YYYY HH:MM. If Zulu/GMT is not possible then provide the package Delivery Time Zone in a separate column (In column 45 labeled Delivery_Date_TmZone). |
| 15 |
| W/A |
| Return_Date |
| The date that the package was sent back to the shipper because the item was not deliverable. |
| DATETIME |
| Date and Time of Delivery in Zulu/GMT. Format MM/DD/YYYY HH:MM. If Zulu/GMT is not possible then provide the package Delivery Time Zone in a separate column (In column 46 labeled Delivery_Date_TmZone). |
| 16 |
| 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.
| 17 |
| R |
| Delivery_Status |
| Code which identifies if the delivery was on-time, late, excusably late or not deliverable. (e.g. OT, LT, EL, ND) |
| 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 column 18 that explains the reason for these status’ and the ultimate disposition of the item being shipped as necessary. |
| 18 |
| W/A |
| Exception_Codes |
| Code which identifies the reason for the delivery of shipment being late, excusably late or not deliverable in #17 above. Must identify which exception codes carrier considers package to be excusably late. (e.g. RNR = Recipient not ready for delivery) |
| VARCHAR(10) |
| Carrier shall provide reference table and explanation of the carrier’s exception codes. |
| 19 |
| R |
| Pieces |
| The number of pieces contained in the shipment. (e.g. 2, 6) |
| INTEGER |
| 20 |
| R |
| Actual_Weight |
| The total weight of the individual piece. |
| DECIMAL(10,1) |
| 21 |
| R |
| Actual_Weight_Unit |
| Indicated unit of measurement for actual weight |
| VARCHAR(3) |
| LBS=Pounds & KGS=Kilograms |
| 22 |
| R |
| Shipment_Cost |
| (Cost to AMC) 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)
| 23 |
| 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. |
| 24 |
| 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 column 22 above.
| 25 |
| I/P |
| TCN |
| (Transportation Control Number) Identifies the provided DoD tracking number, when provided. (e.g. W915127195F009XXX) |
| 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. |
| 26 |
| R |
| Contract Line Number |
| Contract Line Item Number (CLIN) or Service Line Item Number (SLIN). |
| VARCHAR(25) |
| Corresponds to the Contract or Service Line Number. |
| 27 |
| R |
| Theater_Name |
| Contract Theater associated with the destination country (e.g. Southern, European, etc…) |
| VARCHAR(30) |
| Leave Blank. |
| 28 |
| R |
| Region |
| Contract Region associated with destination country (A, B, HVR, etc…) |
| VARCHAR(3) |
| Leave Blank. |
| 29 |
| R |
| Recipient_DoDAAC |
| Recipient Department Of Defense Activity Address Code. (e.g. FB3029, N69058) |
| VARCHAR(6) |
| Six digit code that represents the address of the recipient. |
| 30 |
| R |
| Shipper_DoDAAC |
| Shipper Department Of Defense Activity Address Code. (e.g. W90Y8E, N09281) |
| VARCHAR(6) |
| Six digit code that represents the address of the shipper. |
| 31 |
| R |
| Charged_Weight |
| The total charged weight of the shipment |
| DECIMAL(10,1) |
| 32 |
| R |
| Charged_Weight_Unit |
| Indicated unit of measurement for charged weight |
| VARCHAR(3) |
| LBS=Pounds & KGS=Kilograms |
| 33 |
| I/P |
| Delivery_Signature |
| Signature of the Recipient or his agent |
| VARCHAR(50) |
| Persons name who signed for the item |
| 34 |
| I/P |
| Lead Identifier |
| Lead TCN or Master Air Way Bill |
| VARCHAR(50) |
| Unique number representing a consolidated shipment. |
| 35 |
| I/P |
| GBL |
| Government Bill of Lading (e.g. 1594C1323041 000) |
| VARCHAR(25) |
| Document number used to procure commercial transportation services outside the DTS. |
| 36 |
| I/P |
| TAC |
| Transportation Account Code |
(e.g. N194, F8A0)
| VARCHAR(3) |
| Code that represents who is paying for the movement. |
| 37 |
| 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. |
| 38 |
| W/A |
| Aircraft_Body_Type |
| Identifies aircraft body type used to transport cargo (e.g. Wide = WB, Narrow = NB) |
| CHAR(2) |
| 39 |
| W/A |
| Remarks |
| Brief Explanation of Issues |
| VARCHAR(255) |
| 40 |
| R |
| Prime_Carrier_SCAC |
| Standard Carrier Alpha Code for the Prime contractor (e.g. FedEx = FDE) |
| VARCHAR(4) |
| 41 |
| W/A |
| Sub_Carrier_SCAC |
| Standard Carrier Alpha Code for the sub-contractor carrier (e.g. FedEx = FDE) |
| VARCHAR(4) |
| Field is only required if a subcontractor carrier associated shipment |
| 42 |
| R |
| Program_Category |
| Used to identify the shipment program category based on weight. ISPX =(L) Light Weight and IHX=(H) Heavy Weight |
| CHAR(1) |
| Leave Blank. |
| 43 |
| W/A |
| Notification_Date_TmZone |
| If Zulu/GMT is not possible for Notification_Date (Column 12) then provide the package Pickup Time Zone. (e.g. ACDT, ADT, HADT, etc…) |
| VARCHAR(10) |
| Field is only required if Notification_Date is not in GMT/Zulu (Column 12) |
| 44 |
| W/A |
| Pickup_Date_TmZone |
| If Zulu/GMT is not possible for Pickup_Date (Column 12) then provide the package Pickup Time Zone. (e.g. ACDT, ADT, HADT, etc…) |
| VARCHAR(10) |
| Field is only required if Pickup_Date is not in GMT/Zulu (Column 13) |
| 45 |
| W/A |
| Delivery_Date_TmZone |
| If Zulu/GMT is not possible for Delivery_Date (Column 13) then provide the package Pickup Time Zone. (e.g. ACDT, ADT, HADT, etc…) |
| VARCHAR(10) |
| Field is only required if Delivery_Date is not in GMT/Zulu (Column 14) |
| 46 |
| W/A |
| Return_Date_TmZone |
| If Zulu/GMT is not possible for Return_Date (Column 13) then provide the package Pickup Time Zone. (e.g. ACDT, ADT, HADT, etc…) |
| VARCHAR(10) |
| Field is only required if Return_Date is not in GMT/Zulu (Column 15) |
| 47 |
| W/A |
| Non_Transit_Time |
| Calculated hours for weekends and holidays included in column 16 above. |
| DECIMAL(10,2) |
Leave Blank.
| 48 |
| W/A |
| Dim_Weight |
| The Dimensional Weight of the piece. |
| DECIMAL(10,1) |
| Applicable when the dimensional weight exceeds the actual weight. |
Additional Information: File required monthly.
File Name Example: 200705_XXXX_A_SI_20070623.txt represents Shipment Information for deliveries made in May 07, which was provided on Jun 23, 2007. The XXXX is a place holder for the prime contactor carrier SCAC.
Assessorial Information (AI) Data File
| FIELD 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: File required monthly.
File Name Example: 200705_XXXX_A_AS_20070623.txt represents Assessorial Information for deliveries made in May 07, which was provided on Jun 23, 2007. The XXXX is a place holder for the prime contractor carrier SCAC.
Accessorial Codes (AC) Reference Data File
| FIELD 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 associated Accessorial Code. (e.g. Weekends, Holidays, Export Declaration, Insurance) |
| VARCHAR(50) |
| 3 |
| 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 year or when changes are made.
File Name Example: 200710_XXXX_A_AC_20071005.txt represents Assessorial Codes as of Oct 2007, which was provided on Oct 5, 2007. The XXXX is a place holder for the prime contractor carrier SCAC.
Exception/Delay Codes (EC) Reference Data File
| FIELD 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 in #17 above. 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 year or when changes are made.
File Name Example: 200710_XXXX_A_EC_20071005.txt represents Exception/Delay Codes as of Oct 2007, which was provided on Oct 5, 2007. The XXXX is a place holder for the prime contractor carrier SCAC.
Delivery Status Codes (DC) Reference Data File
| FIELD 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 year or when changes are made.
File Name Example: 200710_XXXX_A_DC_20071005.txt represents Delivery Status Codes as of Oct 2007, which was provided on Oct 5, 2007. The XXXX is a place holder for the prime contractor carrier SCAC.
PERFORMANCE WORK STATEMENT - EXHIBIT B
15-JULY-08
File details come from the government source that posted it. Updated .