Importing and processing fuel card and advances data

6.Importing and processing fuel cardand advances data

Using the Fuel Purchase/Advance Import window, you can import data for a driver’s fuel card transactions. A fuel card is used to purchase fuel or obtain cash advances at truck stops.

Providers charge a fee for cash advances or fuel purchases. These fees are also recorded.

The system analyzes the data you import to ensure it is valid. Valid data is used to create pay details that are transferred to the Settlements application, or to track fuel purchases.

Notes:The fuel purchases data is recorded in the TMWSuite fuelpurchased table. It can be transferred to TMWSuite’s Fuel Tax Export interface. You use that interface to export it to the ALK PC*FuelTax, Inter-tax, or Comdata fuel tax applications.

Selecting data to be imported

Viewing imported data

Viewing/correcting data on the Errors tab

Viewing data on the New Entries tab

Reviewing records on the Existing Entries tab

Processing records

Reviewing processed data on the Pay Details tab

Reviewing processed data on the Fuel Purchases tab

Transferring pay details to Settlements

Reviewing transaction records in your database

Retrieving records

Editing records

Processing records

Selecting data to be imported

You must import the data file from your vendor to be processed.

1.Select ImportFuel Card/Advances. The Fuel Purchase/Advance Import window opens.

Note:The title of the window will reflect your specified vendor. For example, if you are licensed for EFS, the title of the window will be EFS Fuel Purchase/Advance Import:

2.Click Open in the sheet toolbar. The system displays the Select Advances/Fuel File dialog box.

Note: The dialog box will open the folder you accessed the last time you imported a data file. If necessary, you can select a different location from the Look in drop-down list.

3.Do one of the following:

  • Highlight the required data file and click the Open button.
  • Double-click the file name.

The system performs a data integrity check when you import a file. For example:

  • If the import file contains an account number that is not recorded in your database, the Account Error message displays:
  • If the import file contains a customer ID that is not recorded in your database, the Customer Error message displays:

Warning: The interface will load as much of the import file as it can, based on the existence of customer codes in your database. When it cannot match a customer code in the import file with one recorded in your database, it will stop displaying transaction records. If the import file contains a number of customer codes, and you have all but the last one recorded in your database, it will display the ones having customer codes that match those in your database. You could mistakenly think that all the transactions are displayed and click the Process button before you are notified that a customer code in the file is not in your database.

You must add the missing account number or customer ID in the Account Code/Customer Code Maintenance window before you can import the data file.

Note:For information on recording account numbers or customer IDs, see the “Setting up the Advances/Fuel Purchases Import interface” chapter.

Viewing imported data

The Fuel Purchase/Advance Import window has six tabs. The tabs identify how the system has categorized the imported data.

  • Errors

Transaction records that contain any data that does not match the information in the resource’s Card Maintenance record or TMWSuite profile are shown on this tab.

Note: If the imported data contains errors, the system displays the Errors tab automatically.

  • New Entries

Transaction records that have not been imported before, or those that were imported but not saved to the database, are displayed on this tab.

  • Existing Entries

If you re-import a data file, transaction records that have been imported before and saved to the database are displayed on this tab.

  • Pay Details

Valid transaction records for advances and fees that will be transferred to the Settlements application as deductions are shown on this tab.

Note: Pay details do not display on the tab until you have processed the records on the Errors, New Entries, and Existing Entries tabs.

  • Fuel Purchases

Valid transaction records for fuel purchases that will be recorded in the fuelpurchased table or as pay details are shown on this tab.

Note: Fuel Purchase records to not display on the tab until you have processed the records on the Errors, New Entries, and Existing Entries tabs.

  • Imported Data

The raw transaction data contained in the imported file is shown on this tab. Each record is shown as a text string.

The following sections describe how the data is displayed and processed on these tabs.

Viewing/correcting data on the Errors tab

Transaction records that contain any data that does not match the information in the resource’s Card Maintenance record or TMWSuite profile are considered to have errors and are shown on the Errors tab. If the imported data contains errors, the system displays the Errors tab automatically.

The Errors tab displays data in the following columns:

Status / Contains Discard and Update radio buttons, which are used to select entries for processing.
Account ID / The account number recorded in the resource’s Card record; your electronic data provider assigns these numbers to your company.
Customer ID / The customer number recorded in the resource’s Card record; the number designates the service, as specified by your electronic data provider.
Transaction Date / The date/time at which the transaction occurred.
Transaction Number / The identifying number your electronic data provider assigned to the transaction.
Employee Number / The TMWSuite ID of the resource that made the transaction.
Card Number / The unique fuel card number assigned to the resource in the Card Maintenance window.
Unit Number / The TMWSuite ID of the tractor associated with the resource who made the transaction.
Trailer Number / The TMWSuite ID of the trailer assigned on the trip on which the transaction occurred.
Trip Number / The unique ID TMWSuite assigned to the trip (the move #) on which the transaction occurred.
The driver would enter either the order number or the move number at the time of the transaction. When the data file is imported, the validation process checks to see if the value exists in your TMWSuite database. If it does, the validation process then verifies it as assigned to the tractor or driver recorded on the transaction. If it was assigned, it is determined to be valid, and the move number is displayed in this field.
Note: The order number, move number, and trip segment number will be recorded on the paydetail table for the transaction. A value of 0 is used for the order number if it was an empty move.
Truck Stop Code / The unique ID of the truck stop at which the transaction occurred, as recorded in the Fuel Truck Stop profile.
Truck Stop Name / The name of the truck stop at which the transaction occurred, as recorded in the Fuel Truck Stop profile.
Truck Stop City Name / The city location of the truck stop at which the transaction occurred, as recorded in the Fuel Truck Stop profile.
Truck Stop State / The state location of the truck stop at which the transaction occurred, as recorded in the Fuel Truck Stop profile.
Truck Stop Invoice Number / Refers to fuel purchases; the invoice number of the fuel purchase.
Network Truck Stop / Identifies the truck stop where the transaction took place as being in your vendor's network.
Total Due / The total amount of the purchase is the product of
Tractor Gallons x Tractor Cost/Gallon = Tractor Cost
Plus the values from these fields:
  • Reefer Cost
  • Oil Cost
  • Advance Amount
  • Advance Charge
  • Product Amount 1
  • Product Amount 2
  • Product Amount 3
  • Product Amount 4
  • Tax 1
  • Tax 2
  • Tax 3
  • Tax 4

Fee for Fuel/Oil Products / Refers to fuel purchases; the cost of fuel purchased.
Tractor Gallons / Refers to fuel purchases; the number of gallons of fuel purchased.
Tractor Cost/Gallon / Refers to fuel purchases; the cost per gallon of the fuel purchased.
Tractor Cost / The total cost of tractor fuel purchased.
Reefer Gallons / Refers to refrigerant purchases; the number of gallons of refrigerant purchased.
Reefer Cost/Gallon / Refers to refrigerant purchases; the cost per gallon of the refrigerant purchased.
Reefer Cost / The total cost of the refrigerant purchase.
Oil Quarts / Refers to oil purchases; the number of quarts of oil purchased.
Oil Cost / Refers to oil purchases; the cost per quart of the oil purchased.
Advance Amount / Refers to cash advances; the amount received as an advanced.
Advance Charge / Refers to cash advances; the fee charged for the advance.
Rebate Amount / The rebate amount associated with the purchase.
Note: In some programs the rebate amount is the amount shown as the purchase amount. In other cases, it is stored as a separate field and the amount shown in the purchase amount is the regular pump price.
Focus/Select / This field is applicable only if your vendor is Comdata.
Indicates the Comdata rebate program applied to the purchase. Possible values are:
  • F
A Focus program rebate applies.
Note: With a Focus program rebate, the price reduction is usually received at the pump.
  • S
Comdata’s Select program rebate applies.
Note: With the Select program rebate, a refund is received periodically.
Currency Type / The currency used in the transaction.
Nonbillable Item / This field is applicable only if your vendor is Comdata.
Indicates whether the item is payable to the truck stop or Comdata. Comdata tracks purchases on their cards that are billable by them and those that are billable by other vendors.
Product Amount 1 / The purchase amount associated with the product listed in the Product 1 field.
Notes:
1.The Product 1 field does not display on the tab. To determine the product purchased by the transaction, you must view the description for the product 1 code in the Fuel Import Layout window. For more information about the Fuel Import Layout window, see the Using the Fuel Import Layout window section in the “Setting up the Advances/Fuel Purchases Import interface” chapter. When the transaction record is processed and the pay detail is created, the pay type for the actual product purchased displays in the Pyt Itemcode field on the Pay Details tab.
2.You must have the COMMISC=Y setting in the [FuelServ] section of your TTS50.ini file to have the system process transaction records for miscellaneous purchases.
Product Amount 2 / The purchase amount associated with the product listed in the Product 2 field.
Notes:
1.The Product 2 field does not display on the tab. To determine the product purchased by the transaction, you must view the description for the product 2 code in the Fuel Import Layout window. For more information about the Fuel Import Layout window, see the Using the Fuel Import Layout window section in the “Setting up the Advances/Fuel Purchases Import interface” chapter. When the transaction record is processed and the pay detail is created, the pay type for the actual product purchased displays in the Pyt Itemcode field on the Pay Details tab.
2.You must have the COMMISC=Y setting in the [FuelServ] section of your TTS50.ini file to have the system process transaction records for miscellaneous purchases.
Product Amount 3 / The purchase amount associated with the product listed in the Product 3 field.
Notes:
1.The Product 3 field does not display on the tab. To determine the product purchased by the transaction, you must view the description for the product 3 code in the Fuel Import Layout window. For more information about the Fuel Import Layout window, see the Using the Fuel Import Layout window section in the “Setting up the Advances/Fuel Purchases Import interface” chapter. When the transaction record is processed and the pay detail is created, the pay type for the actual product purchased displays in the Pyt Itemcode field on the Pay Details tab.
2.You must have the COMMISC=Y setting in the [FuelServ] section of your TTS50.ini file to have the system process transaction records for miscellaneous purchases.
Product Amount 4 / The purchase amount associated with the product listed in the Product 4 field.
Notes:
1.The Product 4 field does not display on the tab. To determine the product purchased by the transaction, you must view the description for the product 4 code in the Fuel Import Layout window. For more information about the Fuel Import Layout window, see the Using the Fuel Import Layout window section in the “Setting up the Advances/Fuel Purchases Import interface” chapter. When the transaction record is processed and the pay detail is created, the pay type for the actual product purchased displays in the Pyt Itemcode field on the Pay Details tab.
2.You must have the COMMISC=Y setting in the [FuelServ] section of your TTS50.ini file to have the system process transaction records for miscellaneous purchases.
Tractor Hub / The odometer or tractor hub reading entered by the driver for the transaction.
Advance Amount 2 / Dash Cash advance amount. Applicable only when your vendor is T-Chek.
Previous Hubmiles / This is a custom field.
Error / If the system determines that the entry is invalid, the specific cause of the invalidity is shown in this column. An entry is invalid if the card number, driver ID, or tractor ID does not agree with those recorded in the Card Maintenance window.

Use the scroll bar at the bottom of the window to view the Error column, which by default is the last column to the right.

Note:If desired, you can change the configuration of the Errors tab, or any other tab in the Fuel Purchase/Advance Import window, and save it as you would a grid configuration in the Dispatch Planning Worksheet or other configurable windows throughout TMWSuite. For more information, refer to the “Saving and loading grid configurations” chapter in the TMWSuite Basics guide.

This illustration shows that the imported record lists both a card number and a tractor number that are not valid. These numbers are listed as invalid because they do not match the information contained in the resource’s card record or TMWSuite profile.

Perhaps the card number has not yet been recorded as assigned to the resource, or the card number was recorded incorrectly. You would need to record it, or correct it, in the Card Maintenance window before you could update the record.

Note:For instructions on recording or correcting a resource’s card number assignment, refer to the sections, Creating Card records for resources or Viewing existing Card records, in the “Setting up the Advances/Fuel Purchases Import interface” chapter.

Three columns can be updated on the Errors tab:

  • Employee Number
  • Unit Number
  • Trailer Number

Each of these columns has a drop-down list that shows the TMWSuite IDs recorded for the resource type.

If needed, you can select the correct ID in the appropriate column.

To include the records that you have corrected when you process the data file, select the Update radio button for each corrected row.

Note:You can select all records for processing, in spite of their errors, by double-clicking in the header of the Status column to select all the Update radio buttons.

When you process the import file, the records marked for updating will be processed with those that had no errors.

Viewing data on the New Entries tab

The first time you import a fuel purchases/advances data file, records that have no errors display on the New Entries tab. Additionally, when you re-import a data file, any records that were not previously saved also display on the New Entries tab.