IDOT CVISN Documentation Project

International Fuel Tax Agreement (IFTA)

Background

From: http://www.icc.illinois.gov/mc/EOSSifta.aspx (Credentials information updated from communications with Trent Knoles)

The International Fuel Tax Agreement (IFTA) is an agreement in which the base jurisdiction administers motor fuel use taxes for all IFTA jurisdictions (all contiguous United States and Canadian provinces), and apportions payments to those jurisdictions. Illinois based carriers operating commercial motor vehicles interstate must register and comply with the IFTA program requirements. Qualified motor vehicles are defined as those having two axles and a gross vehicle weight or registered gross vehicle weight exceeding 26,000 pounds, or, having three or more axles regardless of weight, or, used in a combination and the weight exceeds 26,000 pounds.

The motor carrier's registration is authorized for one calendar year, with a grace period for previously registered carriers from January 1 through February 28 to affix the decals. After registration, the carrier is responsible for filing quarterly tax returns detailing the miles traveled and gallons purchased in each jurisdiction. In Illinois, IFTA is administered by the Department of Revenue (DOR).

A new motor carrier either mails or phones in a request for a blank application. A carrier previously registered with Illinois IFTA, and in good standing, will receive a computer generated preprinted application each October. The carrier completes the application, including the order for the number of decals requested, and submits the application and payment for the decals to the IFTA section. Decals are $3.75 per set. One set of decals must be affixed to each qualified vehicle in an account's fleet. Each commercial motor vehicle must carry a photocopy of the license.

Upon receipt of the application, the IFTA section reviews the contents to determine that all information required has been submitted, and that it has been properly signed. The carrier is notified by telephone or letter of any missing information. Applications are input into the department's processing system where online edits are performed to identify if all returns have been filed, or if the taxpayer has outstanding liabilities, or if the account has been revoked. The fees are calculated for the number of decals requested, and payments are posted. The license is printed at the department. The system identifies the next available serial number from the decal inventory, and assigns these numbers to a specific carrier. The license and decals are mailed to the carrier, or physically given to walk-in carriers in Department of Revenue lobby locations.

Returns, as well as IFTA Fuel Tax Rate Sheets, are printed each quarter, approximately thirty (30) days before each due date.

The taxpayer must detail all miles traveled and gallons purchased for each fuel type in each IFTA jurisdiction for the quarter to be filed. Taxes are calculated on the net gallons in each jurisdiction. The taxpayer pays or is credited/refunded the net amount. The carrier completes the return, signs the form and mails it to the IFTA section accompanied by the required payment, if applicable.

Upon receipt of the return, the envelope is retained for proof of postmark. The IFTA staff enters the data into the Polk system. In addition to calculating the correct tax rates, the system performs many math calculations and systemic edits.

The IFTA Section is required to enter data from all returns 30 days from the postmark date to comply with the International Fuel Tax Agreement. A transmittal is generated from the system on the last day of each month, detailing for each jurisdiction the miles and gallons reported by the Illinois carriers. The accumulation of data represents the amounts that should be apportioned to the individual jurisdiction.

In 2006, there were 12,667 IFTA licenses and 176,411 decals issued.

Contacts

• Lead administrator of the IFTA Illinois Department of Revenue (International Fuel Tax Agreement):
• Name: Trent Knoles 
• Phone: (217)  524-6784
• e-Mail: TRENT.KNOLES @ Illinois.gov>
• Assistant to IFTA administrator
• Name: Maryann Market
• Phone (217) 785-6493
• Technical Contact: Sales & Excise Tax Division Information Services Administration
• Name: Peggy Lau
• Phone: (217)  785-4177
• e-Mail: PEGGY.LAU @ Illinois.gov
• Technical Contact: 
• Name Roland Marr
• e-Mail: ROLAND.MARR @ Illinois.gov

Database Environment

The DOR has "reports" relative to IFTA, which it sends to the ILCC.  Since most of the information needed by CVIEW/SAFER is contained on these reports, it was decided to modify the reports to cover the data required for CVIEW/SAFER.  As such, the internal database environment becomes the nature of these reports.

The reports are fix field text files with a preamble containing the date and headers followed by one row per record and followed by a postamble containing the number of records.  See Mapping for more details.

Mapping

The standard general mappings on the home tab need to be applied.

The DOR "reports" sent to the ILCC relating to IFTA are fix-field text files with a preamble containing the date and headers followed by one row per record and followed by a postamble containing the number of records.  They are detailed below:

Preamble

Data Records

Notes:

1. The CVIEW/SAFER IFTA_STATUS_CODE needs to be derived from the following legacy fields:  STATUS_CD,  REVOKE_CD, WITHDRL_DT.  Note STATUS_DT and WITHDRL_DT can be in the future (though not often).  Active 0, 1, 2 Suspense 0, 1, 2 are all in good standing.  
2. The Count is the number of records in the IFTA system as of 10/5/2005.
3. Map is the globalIftaStatusType which IFTA_STATUS_CODE will be mapped to.
4. IFTA_STATUS_CODE Mapping Table:

   Count	STATUS_CD	REVOKE_CD	WITHDRL_DT	Map	Meaning
   11379	ACTIVE	(blank)	(blank)	1	Active
   608	ACTIVE	0	(blank)	2	Active Pending
   161	ACTIVE	1	(blank)	2	Active Pending
   61	ACTIVE	2	(blank)	2	Active Pending
   72	ACTIVE	R	(blank)	3	Active Delinquent
   1	CANCEL	R	(blank)	7	Canceled
   9	INACTIVE	R	(blank)	A	Inactive Delinquent
   255	SUSPENSE	(blank)	(blank)	6	Suspended
   11	SUSPENSE	0	(blank)	2	Active Pending
   2	SUSPENSE	1	(blank)	2	Active Pending
   0	SUSPENSE	2	(blank)	2	Active Pending
   9	SUSPENSE	R	(blank)	C	Revoked
   7700	CANCELED	(blank)	date	4	Closed
   478	CANCELED	0	date	4	Closed
   7	CANCELED	1	date	4	Closed
   4	CANCELED	2	date	4	Closed
   1386	CANCELED	R	date	4	Closed
   2568	INACTIVE	(blank)	date	4	Closed
   85	INACTIVE	0	date	4	Closed
   1	INACTIVE	1	date	4	Closed
   3	INACTIVE	2	date	4	Closed
   6090	INACTIVE	R	date	4	Closed
   66	PENDING	(blank)	date	5	Closed Pending
   7	PENDING	0	date	5	Close Pending
   7	PENDING	1	date	5	Close Pending
   3	PENDING	2	date	5	Close Pending
   132	PENDING	R	date	5	Close Pending

5. IFTA_STATUS_DATE mapping should be the latest date among LIAB_DT, STATUS_DT, REVOKE_DT and WITHDRL_DT (some of which may be missing).  Note: there is one record which has a WITHDRL_DT in the future.
6. Country code mapping:

   (MAIL/BUS)_COUNTRY	IFTA_ADDRESS.COUNTRY	Notes
   USA	US
   CA	CA	Only one occurrence
   Canada	CA
   (blank)		all US states

   
   
7. Generally fields should be trimmed of white space on the right in the conversion process.
8. Some dates are blank and should be omitted - but there should always be a mapped IFTA_STATUS_DATE

Postamble

Triggers

Because the DOR periodically generates "reports" that is a complete picture of the status of IFTA, there is no need for trigger fields.  A baseline load can be done each time a new report is created.  Based on experience of doing the prototype, this can be done well within a reasonable time period.  If there is desire to have update-only transactions, a difference from the previous report could be generated and that could be the basis for deciding which records should be included in the T0019 transaction.

US_DOT Cross Reference Mapping

Based on a snapshot of IFTA data as of 10/5/2006 of the 12,581 carriers with ACTIVE STATUS_CD only 37 do not have US_DOT carrier IDs.  Of those without US_DOT carrier IDs,  2 have REVOKE_CD of '0' and 1 has REVOKE_CD of 'R'.

XML Creation Script

Below is a php script which was used to generate T0019 transactions which were then applied to XCVIEW implementation of CVIEW.  A "report" was provided by the DOT. The application of these actual cases was successful and the scripts could be used as the basis for implementation of a XML creation script for the IFTA system.

<?php
DEBUG = FALSE;
$MaxRecords = 50000;
// This script will open the text file from Illinois Department of Revenue (DOR)
// of the International Fuel Tax Agreement (IFTA) and creates a T0019V1.xsd
// transactions file CVIEWT0019V1.xml which can be run as part of the XCVIEW
// CVIEW.bat process.

// get report date
$FileName = ($argc > 1) ? $argv[1] : "SAFER.txt";
($fh = fopen($FileName, "r")) || die("Cannot open file $FileName\n");

// open output file
$FileNameOut = ($argc > 2) ? $argv[2] : "CVIEWT0019V1.xml";
($fhout = fopen($FileNameOut, "w")) || die("Cannot open output file $FileNameOut\n");

// open DB
mysql_connect("localhost","root","sqlmysql");
mysql_select_db("IFTA");
// should be of format

// CURRENT_DATE
// ------------
// MM/DD/YYYY HH:MM:SS am/pm
$Line = rtrim(fgets($fh));
if ($Line != "CURRENT_DATE") die("$Line - Expecting line 1 CURRENT_DATE\n");
$Line = rtrim(fgets($fh));
if ($Line != "------------") die("Expecting line 2 ------------");
$Line = rtrim(fgets($fh));
$SnapDate = date("Y-m-d\Th:i:s",strtotime($Line));
echo "$SnapDate\n";

// 2 blank lines
$Line = rtrim(fgets($fh));
if ($Line != "")die("Expecting line 4 empty");
$Line = rtrim(fgets($fh));
if ($Line != "")die("Expecting line 5 empty");

// Header line with field names
// Header line with -- for field separators

$Line = rtrim(fgets($fh));
$Line = rtrim(fgets($fh));
// ignore header and ----

// output XML heading
$XMLHeader = "<?xml version=\"1.0\"?>
<T0019 xmlns=\"http://www.safersys.org/namespaces/T0019V1\"
xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"
xsi:schemaLocation=\"http://www.safersys.org/namespaces/T0019V1 T0019V1.xsd\">
 <INTERFACE>
 <NAME>SAFER</NAME>
 <VERSION>04.02</VERSION>
 </INTERFACE>
 <TRANSACTION>
 <VERSION>01.00</VERSION>
 <OPERATION>REPLACE</OPERATION>
 <DATE_TIME>$SnapDate</DATE_TIME>
 <TZ>CD</TZ>
 </TRANSACTION>\n";
fwrite($fhout,$XMLHeader);

// Data follows 1 line per record - no data lines start with blank
$LineCount = 0;
$Line = fgets($fh);
$Line = substr($Line, 0, strlen($Line)-2);
while (trim($Line) != ""){
 $LineCount++;
 if ($LineCount > $MaxRecords) break;
 $TAXPAYER_ID = trim(substr($Line, 0, 15));
 $BUSINESS_NAME = htmlspecialchars(rtrim(substr($Line, 16, 50)));
 $DBA_NAME = htmlspecialchars(rtrim(substr($Line, 67, 50)));
 $BUSINESS_ADDR1 = htmlspecialchars(rtrim(substr($Line, 118, 50)));
 $BUSINESS_ADDR2 = htmlspecialchars(rtrim(substr($Line, 169, 50)));
 $BUSINESS_CITY = htmlspecialchars(rtrim(substr($Line, 220, 25)));
 $BUS_ST = rtrim(substr($Line, 246, 6));
 $BUS_ZIP = rtrim(substr($Line, 253, 9));
 $BUS_COUNTRY = rtrim(substr($Line, 263, 15));
 $US_DOT = rtrim(substr($Line, 279, 12));
 $STATUS_CD = rtrim(substr($Line, 292, 10));
 $PieceSTDT = trim(substr($Line, 303, 10));
 $STATUS_DT = $PieceSTDT == "" ? "" : date("Y-m-d",strtotime($PieceSTDT));
 $PieceLBDT = trim(substr($Line, 314, 10));
 $LIAB_DT = $PieceLBDT == "" ? "" : date("Y-m-d", strtotime($PieceLBDT));
 $PieceWDDT=trim(substr($Line, 325, 10));
 $WITHDRL_DT = $PieceWDDT == "" ? "" : date("Y-m-d",strtotime($PieceWDDT));
 $REVOKE_CD = rtrim(substr($Line, 336, 10));
 $PieceRKDT = trim(substr($Line, 347, 10));
 $REVOKE_DT = $PieceRKDT == "" ? "" : date("Y-m-d",strtotime($PieceRKDT));
 $MAIL_ADDR1= htmlspecialchars(rtrim(substr($Line, 358, 50)));
 $MAIL_ADDR2 = htmlspecialchars(rtrim(substr($Line, 409, 50)));
 $MAIL_CITY = htmlspecialchars(rtrim(substr($Line, 460, 25)));
 $MAIL_ST = rtrim(substr($Line, 486, 7));
 $MAIL_ZIP = rtrim(substr($Line, 494, 9));
 $MAIL_COUNTRY = rtrim(substr($Line, 504, 15));
// special mappings
// if no US_DOT make STATE ID
 $CARRIER_ID_NUMBER = $US_DOT == "" ? "$TAXPAYER_ID" : $US_DOT;
// Status code mapping
 $STATUS_CODE = 0; // not available - for any which fall thru
 if ($WITHDRL_DT == "") { // no Withdrawal date
 if ($REVOKE_CD == "") { // blank revoke code
 if ($STATUS_CD == "ACTIVE") $STATUS_CODE = "1"; //Active
 else if ($STATUS_CD == "SUSPENSE") $STATUS_CODE = "6"; // Suspended
 }
 else if ($REVOKE_CD == "R"){
 if ($STATUS_CD == "ACTIVE") $STATUS_CODE = "3"; //Active Delinquent
 else if ($STATUS_CD == "CANCELED") $STATUS_CODE = "7"; // Canceled
 else if ($STATUS_CD == "INACTIVE") $STATUS_CODE = "6"; // Inactive Delinquent
 else if ($STATUS_CD == "SUSPENSE") $STATUS_CODE = "C"; // Revoked
 } else { // REVOKE_CD 0, 1, 2
 if ($STATUS_CD == "ACTIVE" || $STATUS_CD == "SUSPENSE")
 $STATUS_CODE = "2"; // Active Pending
 }
 } else { // Has withdrawal date
 if ($STATUS_CD == "PENDING") $STATUS_CODE = "5"; // Close Pending
 else $STATUS_CODE = "4"; // Closed
 }
// Status Date - look at STATUS_DT, REVOKE_DT and WITHDRL_DT
 $STATUS_DATE = $PieceLBDT;
 if ($PieceSTDT != "" && strtotime($PieceSTDT) > strtotime($STATUS_DATE)) 
 $STATUS_DATE = $PieceSTDT;
 if ($PieceWDDT != "" && strtotime($PieceWDDT) > strtotime($STATUS_DATE)) 
 $STATUS_DATE = $PieceWDDT;
 if ($PieceRKDT != "" && strtotime($PieceRKDT) > strtotime($STATUS_DATE))
  $STATUS_DATE = $PieceRKDT;
 $STATUS_DATE = date("Y-m-d", strtotime($STATUS_DATE));

// Map Country Code
 if ($BUS_COUNTRY != ""){
 if ($BUS_COUNTRY == "USA") $BUS_COUNTRY = "US";
 else if ($BUS_COUNTRY == "CA" || $BUS_COUNTRY == "Canada") $BUS_COUNTRY = "CA";
 }
 if ($MAIL_COUNTRY != ""){
 if ($MAIL_COUNTRY == "USA") $MAIL_COUNTRY = "US";
 else if ($MAIL_COUNTRY == "CA" || $MAIL_COUNTRY == "Canada") $MAIL_COUNTRY = "CA";
 }
 $XMLAccountValid = TRUE; // hack to detect bad data - need xml checker
 $XMLAccount = "
 <IFTA_ACCOUNT>
 <IFTA_CARRIER_ID_NUMBER>$CARRIER_ID_NUMBER</IFTA_CARRIER_ID_NUMBER>
 <IFTA_BASE_COUNTRY>US</IFTA_BASE_COUNTRY>
 <IFTA_BASE_STATE>IL</IFTA_BASE_STATE>
 <IFTA_LICENSE_NUMBER>$TAXPAYER_ID</IFTA_LICENSE_NUMBER>
 <IFTA_STATUS_CODE>$STATUS_CODE</IFTA_STATUS_CODE>
 <IFTA_STATUS_DATE>$STATUS_DATE</IFTA_STATUS_DATE>
 <IFTA_ISSUE_DATE>$LIAB_DT</IFTA_ISSUE_DATE>\n";
 // check "UPDATE_DATE"
 if ($WITHDRL_DT != "") $XMLAccount .=
" <IFTA_EXPIRE_DATE>$WITHDRL_DT</IFTA_EXPIRE_DATE>\n";
 $XMLAccount .=
" <IFTA_UPDATE_DATE>$STATUS_DATE</IFTA_UPDATE_DATE>
 <IFTA_NAME>
 <NAME_TYPE>LG</NAME_TYPE>
 <NAME>$BUSINESS_NAME</NAME>
 <IFTA_ADDRESS>
 <ADDRESS_TYPE>PH</ADDRESS_TYPE>
 <STREET_LINE_1>$BUSINESS_ADDR1</STREET_LINE_1>\n";
 if ($BUSINESS_ADDR2 != "") $XMLAccount .=
" <STREET_LINE_2>$BUSINESS_ADDR2</STREET_LINE_2>\n";
 //<!-- Check for PO BOX addresses in data -->
 $XMLAccount .=
" <CITY>$BUSINESS_CITY</CITY>
 <STATE>$BUS_ST</STATE>
 <ZIP_CODE>$BUS_ZIP</ZIP_CODE>
 <!-- Omit County, COLONIA -->\n";
 if ($BUS_COUNTRY != "") $XMLAccount .=
" <COUNTRY>$BUS_COUNTRY</COUNTRY>\n";
 if ($BUS_ST == "" || $BUS_ZIP == "") $XMLAccountValid = FALSE;
 $XMLAccount .=
" </IFTA_ADDRESS>\n";
// deal with different mail address
 if ($MAIL_ADDR1 != "" && $MAIL_ADDR1 != $BUSINESS_ADDR1 ||
 $MAIL_ADDR2 != "" && $MAIL_ADDR2 != $BUSINESS_ADDR2 ||
 $MAIL_CITY != "" && $MAIL_CITY != $BUSINESS_CITY ||
 $MAIL_ST != "" && $MAIL_ST != $BUS_ST ||
 $MAIL_ZIP != "" && $MAIL_ZIP != $BUS_ZIP ||
 $MAIL_COUNTRY != "" && $MAIL_COUNTRY != $BUS_COUNTRY){
 $XMLAccount .=
" <IFTA_ADDRESS>
 <ADDRESS_TYPE>MA</ADDRESS_TYPE>
 <STREET_LINE_1>$MAIL_ADDR1</STREET_LINE_1>\n";
 if ($MAIL_ADDR2 != "") $XMLAccount .= "
 <STREET_LINE_2>$MAIL_ADDR2</STREET_LINE_2>\n";
 //<!-- Check for PO BOX addresses in data -->
 $XMLAccount .=
" <CITY>$MAIL_CITY</CITY>
 <STATE>$MAIL_ST</STATE>
 <ZIP_CODE>$MAIL_ZIP</ZIP_CODE>
 <!-- Omit County, COLONIA -->\n";
 if($MAIL_COUNTRY != "") $XMLAccount .=
" <COUNTRY>$MAIL_COUNTRY</COUNTRY>\n";
 $XMLAccount .=
" </IFTA_ADDRESS>\n";
 if ($MAIL_ST == "" || $MAIL_ZIP == "") $XMLAccountValid = FALSE;
 }
// deal with different DBA
 if ($DBA_NAME != "" && $DBA_NAME != $BUSINESS_NAME){
 $XMLAccount .=
" </IFTA_NAME>
 <!-- include DBA only if different and include no addresses -->
 <IFTA_NAME>
 <NAME_TYPE>DB</NAME_TYPE>
 <NAME>$DBA_NAME</NAME>\n";
 }
// end tags
 $XMLAccount .=
" </IFTA_NAME>
 </IFTA_ACCOUNT>\n";
 if ($XMLAccountValid) fwrite($fhout,$XMLAccount);
 else echo "*** Invalid XMLAccount\n$XMLAccount\n\n";
 if ($DEBUG) {
 echo "*** $LineCount ***" . strlen($Line) . "\n";
 echo "|$US_DOT|\n";
 echo "|$TAXPAYER_ID|\n";
 echo "|$BUSINESS_NAME|\n";
 echo "|$DBA_NAME|\n";
 echo "|$BUSINESS_ADDR1|\n";
 echo "|$BUSINESS_ADDR2|\n";
 echo "|$BUSINESS_CITY|\n";
 echo "|$BUS_ST|\n";
 echo "|$BUS_ZIP|\n";
 echo "|$BUS_COUNTRY|\n";
 echo "|sc:$STATUS_CD|\n";
 echo "|sd:$STATUS_DT|\n";
 echo "|ld:$LIAB_DT|\n";
 echo "|wd:$WITHDRL_DT|\n";
 echo "|rc:$REVOKE_CD|\n";
 echo "|rd:$REVOKE_DT|\n";
 echo "|$MAIL_ADDR1|\n";
 echo "|$MAIL_ADDR2|\n";
 echo "|$MAIL_CITY|\n";
 echo "|$MAIL_ST|\n";
 echo "|$MAIL_ZIP|\n";
 echo "|$MAIL_COUNTRY|\n";
 } // DEBUG
 $QryStr = "INSERT INTO IFTA (SnapDate, US_DOT, TAXPAYER_ID, BUSINESS_NAME,
 DBA_NAME, BUSINESS_ADDR1, BUSINESS_ADDR2, BUS_ST, BUS_ZIP, BUS_COUNTRY,
 STATUS_CD, STATUS_DT, LIAB_DT, WITHDRL_DT, REVOKE_CD, REVOKE_DT, MAIL_ADDR1,
 MAIL_ADDR2, MAIL_ST, MAIL_CITY, MAIL_ZIP, MAIL_COUNTRY) VALUES (
 '$SnapDate','$US_DOT','$TAXPAYER_ID','$BUSINESS_NAME',
 '$DBA_NAME','$BUSINESS_ADDR1','$BUSINESS_ADDR2','$BUS_ST','$BUS_ZIP','$BUS_COUNTRY',
 '$STATUS_CD','$STATUS_DT','$LIAB_DT','$WITHDRL_DT','$REVOKE_CD','$REVOKE_DT',
 '$MAIL_ADDR1',
 '$MAIL_ADDR2','$MAIL_ST','$MAIL_CITY','$MAIL_ZIP', '$MAIL_COUNTRY')";
 if (strlen($REVOKE_CD) > 1) echo "$QryStr\n";
 /*if (mysql_query($QryStr) === FALSE){
 die("Failed Query $QryStr\n");
 }
 */
 $Line = fgets($fh);
}
// 2 blank lines
$Line = rtrim(fgets($fh));
//if ($Line != "") die("Expecting empty line near end of file");
$Line = rtrim(fgets($fh));///
echo "$LineCount is the count - $Line\n";
// yyyyy record(s) selected
// EOF
fwrite($fhout,"</T0019>\n");
fclose($fhout);
?>

XML Transactions

T0019.xsd IFTA input transaction