Purpose

This document explains how to update missing jurisdiction information within TMWSuite.  It also explains the process for manually sending data to Vusion using the Vusion Extract Agent, to ensure that days containing invalid data are corrected.


In order for Vusion to report on a company’s fuel purchases, data is extracted from the TMWSuite database using the Vusion Extract Agent.  After extraction, it is sent to Vusion for analysis.  Since tax laws relating to fuel differ between jurisdictions, it is vital that these extracted records have accurate city, state, and zip code information attached.  Without that information, the records cannot be processed.


Updating Stops and Cities with Blank Jurisdiction Values

If your Vusion data extraction is missing jurisdiction information, you may have to update the information stored within TMWSuite.  The two areas most likely to be missing jurisdiction information are the following:

  1. The Fuel Truck Stops section of TMW’s Fuel Maintenance module
  2. The table in TMW’s database that houses city information

Entries in both the Fuel Truck Stops and the database’s City table should have jurisdiction information. If possible, it is best to update both.  Keeping both sections accurate best protects against further failed extractions.


Fuel Maintenance

In addition to information stored within the Cities section of File Maintenance, information is stored at an individual stop level within TMW Fuel Maintenance.  To best ensure that records of fuel purchases have City, State, and Zip Code information, Fuel Stop information should be as accurate as possible. 


If Vusion has provided a Station ID that is missing jurisdiction information, it can be accessed and updated by executing the following steps.


From within TMW File Maintenance:

1.   Open “Fuel Truck Stops” from the “Fuel Maintenance” drop-down menu at the top of the screen

2.   Enter the Fuel Stop ID that is missing City, State, or Zip Code information

3.   Update the Fuel Stop’s information with correct jurisdiction information

4.   Click the Save button to finalize changes and update the stop

This will ensure that fuel purchases made at the given Fuel Stop will have proper jurisdiction information attached.


SQL Level: Checking the City Table

The City and State information stored on Fuel Purchased records is sourced from TMW’s City information.  This information is typically populated by TMW during the implementation of TMWSuite products.  In the rare chance that a city’s information is incomplete, it will have to be updated.  In order to view all information relating to cities in TMW, the TMWSuite database has to be queried.  To view all City information within TMW that is missing Name, State, or Zip Code information, execute the following steps:


From your company’s database server:

  1. Open SQL Server Management Studio
  2. Open a new SQL query
  3. From the drop-down menu, choose the Production TMW database
    • By default, this database is named TMW_Live
  4. Enter the following SQL statement into the new query tab:
SELECT *

FROM city C WITH (NOLOCK)

WHERE C. [cty_name] = ' '

      OR C. [cty_name] IS NULL

      OR C. [cty_state] = ' '

OR C. [cty_state] IS NULL

      OR C. [cty_zip] = ' '

      OR C. [cty_zip] IS NULL

 5.   Execute the script


This can be done via the F5 key on the keyboard, or by clicking the Execute button. This will pull all records within TMW’s City table that do not have City Name, State, or Zip Code information attached.  From there, the information can either be updated within the database, or the city can be updated from within TMW’s File Maintenance.  This will ensure that any time a city’s information is used by TMW, its jurisdiction information is accurate.


Optional: Perform a Manual Extraction of Data

Vusion may request that fuel information within TMW be re-extracted after jurisdiction information is updated.  This can be accomplished by using the Vusion Agent to manually send data from a specified date range.  To manually extract data to your Vusion FTP site, execute the following steps:


1.   Open the Vusion Extraction Agent Configuration Window

  1. The Vusion Agent is typically installed on the SQL server that TMW resides on, or on an application server
  2. The agent can be found under Start Menu à All Programs à Vusion Agent à Configure Agent

2.   With the Vusion Agent window open, click “Perform an Extraction”

3.   On the Manual Extraction window that appears, click the box next to Fuel

4.   In the Starting Date field next to Fuel, choose the first day of the current year

  1. The Ending Date field can be left at its default value, to ensure that it re-extracts all data up to the current day

5.   Click the “Queue Extraction” button to queue an extraction of data to your company’s FTP site.

  1. The extraction will occur at the next scheduled extraction window
  2. To immediately extract data, click the “Send Status Button” on the Vusion Agent

After a successful extraction is performed, Vusion will have valid jurisdiction information for your company’s fuel purchased records.