IDOT CVISN Documentation Project

Single State Registration System (SSRS)

Background

From: http://www.icc.illinois.gov/mc/EOSSssrs.aspx (Credentials information updated by communications with Brad Smith)

Thirty-eight states, including Illinois, require interstate for-hire motor carriers of property and passengers to provide information on the carrier's Federal Motor Carrier Safety Administration (FMCSA) operating authority. Regulated interstate motor carriers file the information on an annual basis so the carrier's operations can be monitored. For each state they operate in, regulated carriers are responsible for applying for the right to operate, to pay appropriate vehicle fees, to provide continuous proof of insurance, and to identify a designated resident agent. To make the administration of regulating these carriers more efficient for the states and regulated carriers, the 38 states have entered into a cooperative agreement to administer an operating authority program. Through this program, the Single State Registration System (SSRS), carriers may obtain operating authority (for any of the participating states in which they will be active) with a single request to the carrier's base State. SSRS issues a receipt that documents the States in which that carrier is authorized to operate. The Illinois Commerce Commission (ILCC) administers the Single State Registration System in Illinois. 

The SSRS registration cycle is January to December and the renewal period is from October through December each year. In 2006, the Commerce Commission issued approximately 86,100 SSRS credentials to Illinois-based carriers. 

The Commerce Commission provides a simple 38 field application form and mails it to the SSRS registrants. The carrier completes the application and either mails or faxes the application to the SSRS Unit. The carrier also remits the proper payment for SSRS credentials to the SSRS Unit.

Upon receipt of the renewal application, which may be submitted via e-mail, the SSRS Unit reviews the contents of the renewal application. The application's contents are evaluated to determine whether all required information has been submitted. If any omissions are discovered, the carrier is contacted, either by telephone or mail, to rectify the omission. Complete applications are input into the Commerce Commission system where online edits are performed.  Upon notification that the appropriate funds have been received by the State, the SSRS Unit issues new credentials for the carrier's vehicles. 

Contacts

Database Environment

Microsoft SQL Server 2005 in a clustered environment.

Scope of SSRS Data

While the ILCC appears to have a fairly complete database on motor carriers, the purpose of this analysis is limited to the SSRS system. The process for motor carriers to register in the SSRS system includes the following areas:

The SAFER database already contains both Federal Highway Administration authority and insurance information. The resident agents is also a federal form which is filed with the Federal Motor Carrier Safety Administration (FMCSA) but does not appear to be part of the SAFER database. Dave Lazarides indicated that this was not in the scope of the SSRS system.

The SSRS data primarily is concerned with the registration of the carrier to the SSRS system along with payment for each state and number of vehicles operated in these states. The SSRS generates receipts and sends these receipts to the carrier on successful completion of the SSRS application. The carrier retains the original receipts at its office and makes copies for the vehicles which are paid for. Some carriers transport interstate commodities, which are exempt from fees, in which case they receive an exempt authority from the FMCSA. These carriers are also documented in the SSRS system.

Legacy Data Organization - Tables at a Conceptual Level

The legacy data are primarily kept in three tables, with several assisting coding (lookup) tables. The carrier ID in these three tables is a state ID number. Since SAFER/CVIEW are keyed by the federal US DOT identities, the CARRIER table is of interest since it relates to federal US DOT identities, FMCSA identities and state identities.

The three primary tables are:

Normally a carrier would have a row in BASEREG and either rows in RECEIPT or a single row in REGSTRAT but if a carrier moved to or from exempt status, it might have rows in both RECEIPT or REGSTRAT tables. 

The RECEIPT table may have several rows for a carrier in a particular state and year because the carrier may need to add vehicles after the regular registration period.

The CARRIER table has the following mapping fields:

Column Name Information
MC_NBR state ID
DOT_NBR federal US DOT ID
FMCSA_NBR federal motor carrier safety administration ID

There are presumably other fields in the CARRIER table.

Unless more than one primary table uses the coding table, its name is associated with the primary table name. See detailed coding tables below for values in these tables.

Coding Table Name Information Coded
BASEREG_TAB Indicates special type of registration (new/temporary/emergency)
REC_TAB Indicates type of vehicles (Route Bus, Charter Bus, Truck, None)
REG_TAB Type of exemption (Authorized, Bus, Charter 0, Exempt, Truck ...)
STAT_TAB Status code in both REGSTRAT and RECEIPT (Active, Suspended ...)
ER_TAB Error code used in processing in both REGSTRAT and RECEIPT (None, Missing US DOT Number ...)

Legacy Data Organization - Tables at a Detailed Level

Primary Tables

BASEREG

Field Type Key Foreign Key Table Comment
BASEREG_ID bigint primary key   internal use only
MC_NBR bigint candidate key   state ID (probably should be primary key)
BASEREG_CD nvarcar(2)   BASEREG_TAB special type of registration
SERVICE_DATE datetime     date the service was performed
AGENT_LIST_IND bit     have agent residency list
FMCSA_ORDER_IND bit     have FMCSA authority
SELF_INS_IND bit     carrier is self insured
REQ_INS_AMT money     required insurance for this carrier
EMPCODE nvarchar(32)     employee who processed this carrier
DATESTAMP datetime     time of last change

RECEIPT 

Field Type Key Foreign Key Table Comment
RECEIPT_ID bigint primary key   internal use only
FEE_CHG_ID bigint candidate key (part)   order number
MC_NBR bigint candidate key (part)   state ID
VOUCHER_ID bigint  
VOUCHER		 related to fees due to/from other states
CARRIER_STATE_CD nvarchar(2)     base state
TRAVEL_STATE_CD nvarchar(2) candidate key (part)   state traveled in
VEHICLES int     number of vehicles for this state for this order
FEE_AMT money     amount paid
REC_CD nvarchar(2)   REC_TAB type of vehicles
RECEIPT_YEAR int candidate key (part)   year this receipt is for
PRINTED_DTE datetime     timestamp receipt printed
PRINTED_BY nvarchar(32)     employee who printed receipt
ENTERED_DTE datetime     time receipt entered
ENTERED_BY nvarchar(32)     employee who entered receipt
STAT_CD nvarchar(32)   STAT_TAB status code of receipt
STAT_DTE datetime     time of STAT_CD entered
ER_CD_DISP nvarchar(32)   from ER_TAB Error message to track down problems
EMPCODE nvarchar(32)     employee last worked on receipt
DATESTAMP datetime     time of last change

REGSTRAT (Exempt status) 

Field Type Key Foreign Key Table Comments
REGSTRAT_ID bigint Primary Key   internal use only
FEE_CHG_ID bigint     order number
MC_NBR bigint Candidate key   state ID
REG_CD nvarchar(2)   REG_TAB Type of Exemption
ISSUE_DTE datetime     date exemption issued
WARN_DTE date     optional - warning about problems of exemption sent
SUSP_DTE date     optional - suspension of exemption date
REV_DTE date     optional - revocation of exemption date
STAT_CD nvarchar(32)     status code of receipt
STAT_DTE datetime     time of STAT_CD entered
ER_CD_DISP nvarchar(32)   ER_TAB Error code to track down problems
MESSAGE nvarchar(150)     Optional message associated with order
EMPCODE nvarchar(32)     Employee last worked on REGISTAT
DATESTAMP datetime     time of last update

Coding Tables

BASEREG_TAB (3 rows) 


Field Type Primary Key Comments Values
BASEREG_CD nvarchar(2) Primary Key    
BASEREG_NAM nvarchar(50)   Special Nature New, Temporary, Emergency
RETIRED_IND bit   This code is no longer active  
EMPCODE nvarchar(32)   Employee code  
DATESTAMP datetime   time of last update  

REC_TAB (4 rows) 

Field Type Primary Key Comments Values
REC_CD nvarchar(2) Primary Key    
REC_NAM nvarchar(50)     Route Bus, Charter Bus, Truck, None
RETIRED_IND bit   this code is no longer active
EMPCODE nvarchar(32)   Employee code
DATESTAMP datetime   time of last update

REG_TAB (11 rows) 

Field Type Primary Key Comments Values
REG_CD nvarchar(2) Primary Key    
REG_NAM nvarchar(50)     Authorized, Bus, Exempt, ... (see below)
RETIRED_IND bit   no longer active  
EMPCODE nvarchar(32)   Employee code  
DATESTAMP datetime   time of last update  

STAT_TAB (20 rows)

Field Type Primary Key Comment Values
STAT_CD nvarchar(2) primary key  
STAT_NAM nvarchar(50)   Name Active, Revoked, Inactive ... (see below)
RETIRED_IND bit   this code no longer active  
EMPCODE nvarchar(32)   Employee code  
DATESTAMP datetime   time of last update  

ER_TAB (66 rows)

Field Type Primary Key Comment Values
ER_CD nvarchar(2) Primary Key    
ER_NAM nvarchar(50)   Name None, Missing DOT Number ... (see below)
RETURN_IND bit   this application is returned to the carrier  
RETIRED_IND bit   this code no longer active  
EMPCODE nvarchar(32)   Employee code  
DATESTAMP datetime   time of last update  

Discussion of SSRS Organization in Extended CVIEW

Since SAFER/CVIEW has no current facility for SSRS information and CVIEW is designed for extensions like SSRS, we need to specify the organization in CVIEW as part of a mapping of legacy SSRS to CVIEW data. 

To simplify the mapping, the organization of the CVIEW data should be similar to the organization of the legacy SSRS data. Discussed below are the motivations for alterations from that organization.

  1. Since the CVIEW/SAFER system is organized around the US DOT ID, that should be either the primary key or part of the primary key in all non-coding tables.
  2. It is believed that the base state is associated with the carrier and does not need to be in the RECEIPT table but rather with the BASEREG table. 
  3. The exempt information is in a table that should be renamed. 
  4. CVIEW/SAFER is designed as a snapshot not history of information. As such, receipt information of previous years are of little value. 
  5. Voucher information - (settlement of fees between states) is not of interest to SSRS under CVIEW
  6. Employee codes are not of interest in CVIEW 

Proposed Primary Tables in CVIEW

SSRS_CARRIER

Contains BASE_REG info along with carrier ID mapping and Base State info. 
Field Type Primary Key Foreign Key Table Comment
CARRIER_ID_NUMBER bigint primary key   US DOT
STATE_MC_NBR bigint     state ID
FMCSA_NBR bigint     federal motor carrier safety administration ID
CARRIER_STATE_CD nvarchar(2)     base state
BASEREG_CD nvarcar(2)   BASEREG_TAB special type of registration
SERVICE_DATE datetime     date the service was performed
AGENT_LIST_IND bit     have agent residency list
FMCSA_ORDER_IND bit     have FMCSA authority
SELF_INS_IND bit     carrier is self insured
REQ_INS_AMT money     required insurance for this carrier
DATESTAMP datetime     time of last change

SSRS_RECEIPT

Contains Receipt info - primary key (CARRIER_ID_NUMBER, TRAVEL_STATE_CD, FEE_CHG_ID, RECEIPT_YEAR)
Field Type Primary Key Foreign Key Comment
CARRIER_ID_NUMBER bigint Primary Key (part)   US DOT
TRAVEL_STATE_CD nvarchar(2) Primary Key (part)   state traveled in
FEE_CHG_ID bigint Primary Key (part)   order number
RECEIPT_YEAR int Primary Key (part)   year this receipt is for
VEHICLES int     number of vehicles for this state for this order
FEE_AMT money     amount paid
REC_CD nvarchar(2)   REC_TAB type of vehicles
PRINTED_DTE datetime     time receipt printed
ENTERED_DTE datetime     time receipt entered
STAT_CD nvarchar(2)   STAT_TAB status code of receipt
STAT_DTE datetime     time of STAT_CD entered
ER_CD_DISP nvarchar(32)     Error code to track down problems
DATESTAMP datetime     time of last update

SSRS_EXEMPT

 Contains Exempt information for carriers who are exempt 

Field Type Primary Key Foreign Key Comment
CARRIER_ID_NUMBER bigint primary key   US DOT
FEE_CHG_ID bigint     order number
REG_CD nvarchar(2)   REG_TAB Type of Exemption
ISSUE_DTE date     date exemption issued
WARN_DTE date (optional)     warning about problems of exemption sent
SUSP_DTE date (optional)     suspension of exemption date
REV_DTE date (optional)     revocation of exemption date
STAT_CD nvarchar(32)     status code of receipt
STAT_DTE datetime     time of STAT_CD entered
ER_CD_DISP nvarchar(32)     Error code to track down problems
MESSAGE nvarchar(150)     Optional message associated with order
DATESTAMP datetime     time of last update

Proposed Coding Tables

The coding tables can be left as they are - with the following changes:

  1. Dropping the EMPCODE field
  2. Consider renaming of tables/fields to the CVIEW/SAFER style. 

Mapping of Legacy SSRS Data to Proposed CVIEW SSRS Data Considerations

  1. General Mapping rules: These rules apply to all transactions.
    1. Any special characters (&'"<>) need to get mapped to the XML equivalents.  Note: Php's htmlspecialchars does this.
    2. In addresses:
      1. At least one of the STREET_LINE addresses needs to be present.
      2. Standard States 2 character code need to be present (globalJurisdictionType)
      3. Zip codes can be between 1 and 10 characters (globalZipType)
      4. City, State, Zip codes need to be present
      5. Country names if present need to be mapped to balOptCountryType (US, CA, M, MX, P)
    3. Timestamps (date+time) need to be converted to XML format (YYYY-MM-DDTHH:MM:SS - note T is literal).
  2. Since US DOT is either the primary key or part of the primary key in these tables, mapping the legacy tables will require obtaining the US DOT ID from the CARRIER table. 
  3. The base state is now contained in the SSRS_CARRIER table.  The base state will need to be obtained from  a RECEIPT row. 
  4. Only RECEIPT rows which have the RECEIPT_YEAR greater or equal to the current year should be processed. 

Detailed Mapping of SSRS Legacy Data to Proposed CVIEW SSRS Data 

SSRS_CARRIER

To From Notes
CARRIER_ID_NUMBER CARRIER.DOT_NBR  
STATE_MC_NBR BASEREG.MC_NBR  
FMCSA_NBR CARRIER.FMCSA_NBR  
CARRIER_STATE_CD NULL initially updated with RECEIPT rows
BASEREG_CD BASEREG.BASEREG_CD Validation required
SERVICE_DATE BASEREG.SERVICE_DATE  
AGENT_LIST_IND BASEREG.AGENT_LIST_IND  
FMCSA_ORDER_IND BASEREG.FMCSA_ORDER_IND  
SELF_INS_IND BASEREG.SELF_INS_IND  
REQ_INS_AMT BASEREG.REQ_INS_AMT  
DATESTAMP BASEREG.DATESTAMP  

SSRS_RECEIPT

To From Notes
CARRIER_ID_NUMBER CARRIER.DOT_NBR  
TRAVEL_STATE_CD RECEIPT.TRAVEL_STATE_CD  
FEE_CHG_ID RECEIPT.FEE_CHG_ID  
RECEIPT_YEAR RECEIPT.RECEIPT_YEAR Validation required
VEHICLES RECEIPT.VEHICLES  
FEE_AMT RECEIPT.FEE_AMT  
REC_CD RECEIPT.REC_CD Validation required
PRINTED_DTE RECEIPT.PRINTED_DTE  
ENTERED_DTE RECEIPT.ENTERED_DTE  
STAT_CD RECEIPT.STAT_CD Validation required
STAT_DTE RECEIPT.STAT_DTE  
ER_CD_DISP RECEIPT.ER_CD_DISP  
DATESTAMP RECEIPT.DATESTAMP  
Note: when processing transactions from RECEIPT the RECEIPT.BASE_STATE_CD may need to update SSRS_CARRIER.BASE_STATE_CD if SSRS_CARRIER.BASE_STATE_CD is null.

SSRS_EXEMPT

To From Notes
CARRIER_ID_NUMBER CARRIER.DOT_NBR  
FEE_CHG_ID REGSTRAT.FEE_CHG_ID  
REG_CD REGSTRAT.REG_CD Validation required
ISSUE_DTE REGSTRAT.ISSUE_DTE  
WARN_DTE REGSTRAT.WARN_DTE  
SUSP_DTE REGSTRAT.SUSP_DTE  
REV_DTE REGSTRAT.REV_DTE  
STAT_CD REGSTRAT.STAT_CD Validation required
STAT_DTE REGSTRAT.STAT_DTE  
ER_CD_DISP REGSTRAT.ER_CD_DISP  
MESSAGE REGSTRAT.MESSAGE  
DATESTAMP REGSTRAT.DATESTAMP  

Trigger Fields

Since DATESTAMP in BASEREG, RECEIPT and REGSTRAT are updated when changes are made to those respective tables they can serve as a trigger field for including them in a transaction to update the SSRS_CARRIER, SSRS_RECEIPT, SSRS_EXEMPT tables respectively.

Additional Considerations 

  1. Since the coding tables are stable and small they can be loaded in a special process when creating the CVIEW database.
  2. Validation on BASE_REG_CD, REC_CD, REG_CD and STAT_CD is required for non null values in SSRS_CARRIER, SSRS_RECEIPT, SSRS_EXEMPT
  3. Validation required for RECEIPT_YEAR.  Only current year information should be included.
  4. Probably two different transactions - one for update and one for complete load should be set up. As part of the complete load transaction, any existing rows in the CVIEW database related to the SSRS primary tables should be removed. This will allow for clearing of last year's data over a calendar year change.
  5. The CVIEW RECEIPT table is probably too granular for summary views since for a particular state the number of vehicles can be on different orders. A view of the total number of licenses for a particular state, carrier and year separated by status may be needed.

US_DOT Cross Reference Completeness

The US_DOT Carrier ID mapping to state ID in the SSRS system is contained in the following table:

Records Category Count
Total SSRS 12763
Have US_DOT Carrier ID 12434
Missing US_DOT Carrier ID 329
Missing US_DOT Have FMCSA 254
Missing US_DOT Missing FMCSA 75

The data in this table was effective as of 10/25/2006 based on queries run by Brad Smith.

The FMCSA is the Federal Motor Carrier Safety Administration ID.  This identifier may be helpful in determining US_DOT Carrier IDs.

Legacy Coding Table Values

BASEREG_TAB - without EMPCODE or DATESTAMP

BASEREG_CD BASEREG_NAM RETIRED_IND
E Emergency Temporary Authority 0
N New Certificate/Permit 0
T Temporary Authority 0

ER_TAB - without EMPCODE or DATESTAMP

ER_CD ER_NAM RETURN_IND RETIRED_IND
00 None 0 0
01 Document not Properly Signed 1 0
02 Missing DOT Number 0 0
03 Principle Names / Titles Missing 1 0
04 Invalid Fee Code 0 0
05 Underpayment $______.__, Correct Amount is $______ 1 0
06 Missing Full Legal Name 1 0
07 Incomplete Business Address / Telephone Number 0 0
08 Business Address Contains P.O. Box Number Only 0 0
09 Duplicate Motor Carrier on File 1 0
10 Insufficient Funds to Cover Payment 1 0
11 Invalid Motor Carrier Number Specified 1 0
12 Payment not Received 1 0
13 Authority / Registration Revoked 1 0
14 Current Annual Report not on File 0 0
15 Invalid FEIN/SSN Code or Number 0 0
16 Invalid Zip Code 0 0
17 Invalid State Abbreviation 0 0
40 ICC Motor Carrier Number Missing 1 0
41 Process Agent not Specified 0 0
42 Type of Business not Specified 0 0
43 Registration Type (A or E) Missing 0 0
44 No Authority to Operate in Illinois 1 0
45 Business State Changed, No Proof Provided 1 0
46 Prior Application Revoked or Suspended 0 0
47 Pending Application Already on File 1 0
48 Revoked: Sec. of State Corporation Fees Not Paid 1 0
49 State of Incorporation Missing 0 0
50 Shipper Support Statement not Filed 0 0
51 County Code/Name Missing 0 0
52 << Invalid Error Code >> 0 0
53 << Invalid Error Code >> 0 0
54 << Invalid Error Code >> 0 0
55 << Invalid Error Code >> 0 0
56 << Invalid Error Code >> 0 0
57 Compensation type not given / amount invalid 1 0
58 Letters of Office / Probate Court Order Missing 0 0
59 Small Estate / Heirship Affidavit Missing 0 0
60 Identification of Equipment Section Incomplete 1 0
61 Lease Eff / Exp Date Missing or Incorrect 1 0
62 Lessee Control Indicator should be 'Y' 1 0
63 Lease Term is Greater Than 3 Years 1 0
64 Abstract of Shipments Missing 0 0
65 Contract For Transfer Missing 0 0
66 Operator / Dispatcher Indicator Not Present 0 0
67 No Response Present / Invalid Response 0 0
68 Driver's License Not Present / Invalid 0 0
70 No Insurance FORM-E on File 1 0
71 Duplicate Insurance Form/Policy 1 0
72 Insurance Company/Policy Number Missing or Invalid 1 0
73 COD Affidavit or Bond Already on File 1 0
74 No Active Insurance FORM/POLICY to Cancel 1 0
75 Invalid Insurance FORM/POLICY Type 0 0
76 No Insurance POLICY/BOND Effective Date 1 0
77 "ILLINOIS COMMERCE COMMISSION" name not on Form 1 0
78 Insurance Company Inactive 1 0
80 Stamp Ordering Fee Not Paid 1 0
81 Stamps Not ordered with Application 1 0
82 Missing Expiration Date for Stamps 0 0
83 Missing Quantity for Stamps 1 0
84 Total Fees for Stamps not Specified or Incorrect 1 0
85 Incomplete Stamp Mailing Address 0 0
86 Invalid Re-Issued/Re-Printed Stamp Serial Numbers 1 0
90 Other 1 0
91 Docket Sub Number Missing 0 0
99 Overpayment - Check amount reduced 0 0

REC_TAB - without EMPCODE or DATESTAMP

REC_CD REC_NAM RETIRED_IND
B Regular-Route Bus 0
C Charter Bus 0
X None 0

REG_TAB - without EMPCODE or DATESTAMP

REG_CD REG_NAM RETIRED_IND
A Authorized 0
B Bus 0
C Charter 0
E Exempt 0
N None 0
S S: Unknown 0
T Truck 0
U U: Unknown 0
V V: Unknown 0
X X: Unknown 0
Y Y: Unknown 0

STAT_TAB - without EMPCODE or DATESTAMP

STAT_CD STAT_NAM RETIRED_IND
00 Active 0
01 Permanent Pending 0
02 Temporary Pending 0
03 Registration / Authority Issued 0
04 Suspension - Insurance Canceled 0
05 Suspension - Voluntary Request 0
06 Suspension - Bankruptcy 0
07 Revoked 0
08 Inactive 0
10 Stp Order - Inter. Canceled / Intra. Okay 0
11 Stp Order - Inter. Okay / Intra. Canceled 0
12 Stp Order - Inter. Canceled / Intra. Canceled 0
13 Stp Order - Inter. Canceled / Intra. Compliance 0
14 Stp Order - Inter. Okay / Intra. Compliance 0
15 Stp Order - Inter. Acceptbl. / Intra. Okay 0
16 Stp Order - Inter. Acceptbl. / Intra. Canceled 0
17 Stp Order - Inter. Acceptbl. / Intra. Compliance 0
18 Stp Order - Don't Print This Order Yet! 0
99 Process Not Completed 0

XML Creation Script

Below is a php script which was used to generate X0011 and X0012 transactions which were then applied to XCVIEW implementation of CVIEW.  A database was created and minimally populated with test data. The application of the test cases were successful and the scripts could be used as the basis for an implementation of a XML creation script for the SSRS system.

The $Host, $Username, $Password, "LSI_SSRS" database would need to be adjusted to reflect the actual SSRS database.  This script assumes the database is a MS-SQL 2005 database server which is true in the case of SSRS information.

<?php
$Host = "lsissrshost";
// This script will open up the SSRS database and produce a transaction
// file for processing in the augmented cview tasks
/* Connecting, selecting database */
$Username = "username";
$Password = "password";
$link = mssql_connect($Host, $Username, $Password)
 or die('Could not connect\n');
if (!mssql_select_db("LSI_SSRS"))
 die("Could not select LSI_SSRS\n");
// open output file
$FileNameOut = ($argc > 1) ? $argv[1] : "CVIEWX0011V1.xml";
($fhout = fopen($FileNameOut, "w")) || die("Cannot open output file $FileNameOut\n");

$QryStr = "SELECT *, RECEIPT.DATESTAMP RECEIPT_DATESTAMP, 
BASEREG.DATESTAMP BASEREG_DATESTAMP 
FROM CARRIER, BASEREG, RECEIPT WHERE CARRIER.MC_NBR = BASEREG.MC_NBR
AND CARRIER.MC_NBR = RECEIPT.MC_NBR";
$Results = mssql_query($QryStr);
if ($Results === FALSE)
 die("Failed Query - $QryStr\n");
if ($Results === TRUE) die("no rows");
$SnapDate = date("Y-m-d\Th:i:s");
// output XML heading
$XMLHeader = "<?xml version=\"1.0\"?>
<X0011 xmlns=\"http://www.safersys.org/namespaces/X0011V1\"
xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"
xsi:schemaLocation=\"http://www.safersys.org/namespaces/X0011V1 X0011V1.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);
$Last_MC_NBR = "";
while ($Row = mssql_fetch_object($Results)){
 if ($Row->MC_NBR != $Last_MC_NBR){ // new Carrier
 if ($Last_MC_NBR != ""){ // Finish last Carrier with Receit
 $XMLReceipt =
" </SSRS_BASE>\n";
 fwrite($fhout, $XMLReceipt);
 }
 $Last_MC_NBR = $Row->MC_NBR;
 $XMLReceipt =
" <SSRS_BASE>\n";
 // SSRS_CARRIER info
 $Piece = $Row->DOT_NBR;
 $CARRIER_ID_NUMBER = $Piece != "" ? $Piece : $Row->MC_NBR;
  // Map state ID if required
 $STATE_MC_NBR = $Row->MC_NBR;
 $FMCSA_NBR = $Row->FMCSA_NBR;
 $CARRIER_STATE_CD = $Row->CARRIER_STATE_CD;
 $BASEREG_CD = $Row->BASEREG_CD;
 $SERVICE_DATE = date("Y-m-d",strtotime($Row->SERVICE_DATE));
 $AGENT_LIST_IND = $Row->AGENT_LIST_IND;
 $FMCSA_ORDER_IND = $Row->FMCSA_ORDER_IND;
 $SELF_INS_IND = $Row->SELF_INS_IND;
 $REQ_INS_AMT = $Row->REQ_INS_AMT;
 $DATESTAMP = date("Y-m-d\Th:i:s",strtotime($Row->BASEREG_DATESTAMP));
 $XMLReceipt .=
" <CARRIER_ID_NUMBER>$CARRIER_ID_NUMBER</CARRIER_ID_NUMBER>
 <STATE_MC_NBR>$STATE_MC_NBR</STATE_MC_NBR>
 <FMCSA_NBR>$FMCSA_NBR</FMCSA_NBR>
 <CARRIER_STATE_CD>$CARRIER_STATE_CD</CARRIER_STATE_CD>
 <BASEREG_CD>$BASEREG_CD</BASEREG_CD>
 <SERVICE_DATE>$SERVICE_DATE</SERVICE_DATE>
 <AGENT_LIST_IND>$AGENT_LIST_IND</AGENT_LIST_IND>
 <FMCSA_ORDER_IND>$FMCSA_ORDER_IND</FMCSA_ORDER_IND>
 <SELF_INS_IND>$SELF_INS_IND</SELF_INS_IND>
 <REQ_INS_AMT>$REQ_INS_AMT</REQ_INS_AMT>
 <DATESTAMP>$DATESTAMP</DATESTAMP>\n";
 fwrite($fhout, $XMLReceipt);
 }
 // SSRS_RECEIPT info
 //CARRIER_ID already set
 $TRAVEL_STATE_CD = $Row->TRAVEL_STATE_CD;
 $FEE_CHG_ID = $Row->FEE_CHG_ID;
 $RECEIPT_YEAR = $Row->RECEIPT_YEAR;
 $VEHICLES = $Row->VEHICLES;
 $FEE_AMT = $Row->FEE_AMT;
 $REC_CD = $Row->REC_CD;
 $PRINTED_DTE = date("Y-m-d",strtotime($Row->PRINTED_DTE));
 $ENTERED_DTE = date("Y-m-d",strtotime($Row->ENTERED_DTE));
 $STAT_CD = $Row->STAT_CD;
 $STAT_DTE = date("Y-m-d",strtotime($Row->STAT_DTE));
 $ER_CD_DISP = $Row->ER_CD_DISP;
 $DATESTAMP = date("Y-m-d\Th:i:s",strtotime($Row->RECEIPT_DATESTAMP));

 $XMLReceipt =
" <SSRS_RECEIPT>
 <CARRIER_ID_NUMBER>$CARRIER_ID_NUMBER</CARRIER_ID_NUMBER>
 <TRAVEL_STATE_CD>$TRAVEL_STATE_CD</TRAVEL_STATE_CD>
 <FEE_CHG_ID>$FEE_CHG_ID</FEE_CHG_ID>
 <RECEIPT_YEAR>$RECEIPT_YEAR</RECEIPT_YEAR>
 <VEHICLES>$VEHICLES</VEHICLES>
 <FEE_AMT>$FEE_AMT</FEE_AMT>
 <REC_CD>$REC_CD</REC_CD>
 <PRINTED_DTE>$PRINTED_DTE</PRINTED_DTE>
 <ENTERED_DTE>$ENTERED_DTE</ENTERED_DTE>
 <STAT_CD>$STAT_CD</STAT_CD>
 <STAT_DTE>$STAT_DTE</STAT_DTE>
 <ER_CD_DISP>$ER_CD_DISP</ER_CD_DISP>
 <DATESTAMP>$DATESTAMP</DATESTAMP>
 </SSRS_RECEIPT>\n";
 fwrite($fhout,$XMLReceipt);
}
XMLReceipt =
" </SSRS_BASE>
</X0011>\n";
fwrite($fhout,$XMLReceipt);
fclose($fhout);

// open output file
$FileNameOut = ($argc > 2) ? $argv[2] : "CVIEWX0012V1.xml";
($fhout = fopen($FileNameOut, "w")) || die("Cannot open output file $FileNameOut\n");
$QryStr = "SELECT *, REGSTRAT.DATESTAMP REGSTRAT_DATESTAMP, 
BASEREG.DATESTAMP BASEREG_DATESTAMP FROM CARRIER, BASEREG, REGSTRAT WHERE 
CARRIER.MC_NBR = BASEREG.MC_NBR AND CARRIER.MC_NBR = REGSTRAT.MC_NBR";
$Results = mssql_query($QryStr);
if ($Results === FALSE)
 die("Failed Query - $QryStr\n");
if ($Results === TRUE) die("no rows");
$SnapDate = date("Y-m-d\Th:i:s");
// output XML heading
$XMLHeader = "<?xml version=\"1.0\"?>
<X0012 xmlns=\"http://www.safersys.org/namespaces/X0012V1\"
xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"
xsi:schemaLocation=\"http://www.safersys.org/namespaces/X0012V1 X0012V1.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);
while ($Row = mssql_fetch_object($Results)){
 $XMLExempt=
" <SSRS_EXEMPT>
 <SSRS_BASE>\n";
 // SSRS_CARRIER info
 $Piece = $Row->DOT_NBR;
 $CARRIER_ID_NUMBER = $Piece != "" ? $Piece : $Row->MC_NBR;
  // Map state ID if required
 $STATE_MC_NBR = $Row->MC_NBR;
 $FMCSA_NBR = $Row->FMCSA_NBR;
 $BASEREG_CD = $Row->BASEREG_CD;
 $SERVICE_DATE = date("Y-m-d",strtotime($Row->SERVICE_DATE));
 $AGENT_LIST_IND = $Row->AGENT_LIST_IND;
 $FMCSA_ORDER_IND = $Row->FMCSA_ORDER_IND;
 $SELF_INS_IND = $Row->SELF_INS_IND;
 $REQ_INS_AMT = $Row->REQ_INS_AMT;
 $DATESTAMP = date("Y-m-d\Th:i:s",strtotime($Row->BASEREG_DATESTAMP));
 $XMLExempt .=
" <CARRIER_ID_NUMBER>$CARRIER_ID_NUMBER</CARRIER_ID_NUMBER>
 <STATE_MC_NBR>$STATE_MC_NBR</STATE_MC_NBR>
 <FMCSA_NBR>$FMCSA_NBR</FMCSA_NBR>
 <BASEREG_CD>$BASEREG_CD</BASEREG_CD>
 <SERVICE_DATE>$SERVICE_DATE</SERVICE_DATE>
 <AGENT_LIST_IND>$AGENT_LIST_IND</AGENT_LIST_IND>
 <FMCSA_ORDER_IND>$FMCSA_ORDER_IND</FMCSA_ORDER_IND>
 <SELF_INS_IND>$SELF_INS_IND</SELF_INS_IND>
 <REQ_INS_AMT>$REQ_INS_AMT</REQ_INS_AMT>
 <DATESTAMP>$DATESTAMP</DATESTAMP>
 </SSRS_BASE>\n";
 fwrite($fhout, $XMLExempt);
 // SSRS_EXEMPT info
 //CARRIER_ID already set
 $FEE_CHG_ID = $Row->FEE_CHG_ID;
 $REG_CD = $Row->REG_CD;
 $ISSUE_DTE = date("Y-m-d",strtotime($Row->ISSUE_DTE));
 $PieceWarn = $Row->WARN_DTE;
 $WARN_DTE = $PieceWarn != "" ? date("Y-m-d",strtotime($PieceWarn)) : "";
 $PieceSusp = $Row->SUSP_DTE;
 $SUSP_DTE = $PieceSusp != "" ? date("Y-m-d",strtotime($PieceSusp)) : "";
 $PieceRev = $Row->REV_DTE;
 $REV_DTE = $PieceRev != "" ? date("Y-m-d",strtotime($PieceRev)) : "";
 $STAT_CD = $Row->STAT_CD;
 $STAT_DTE = date("Y-m-d",strtotime($Row->STAT_DTE));
 $ER_CD_DISP = $Row->ER_CD_DISP;
 $MESSAGE = $Row->MESSAGE;
 $DATESTAMP = date("Y-m-d\Th:i:s",strtotime($Row->REGSTRAT_DATESTAMP));
 $XMLExempt =
" <SSRS_EXEMPT>
 <CARRIER_ID_NUMBER>$CARRIER_ID_NUMBER</CARRIER_ID_NUMBER>
 <FEE_CHG_ID>$FEE_CHG_ID</FEE_CHG_ID>
 <REG_CD>$REG_CD</REG_CD>
 <ISSUE_DTE>$ISSUE_DTE</ISSUE_DTE>\n";
 if ($PieceWarn != "") $XMLExempt .=
" <WARN_DTE>$WARN_DTE</WARN_DTE>\n";
 if ($PieceSusp != "") $XMLExempt .=
" <SUSP_DTE>$SUSP_DTE</SUSP_DTE>\n";
 if ($PieceRev != "") $XMLExempt .=
" <REV_DTE>$SUSP_DTE</REV_DTE>\n";
 $XMLExempt .=
" <STAT_CD>$STAT_CD</STAT_CD>
 <STAT_DTE>$STAT_DTE</STAT_DTE>
 <ER_CD_DISP>$ER_CD_DISP</ER_CD_DISP>
 <MESSAGE>$MESSAGE</MESSAGE>
 <DATESTAMP>$DATESTAMP</DATESTAMP>
 </SSRS_EXEMPT>\n";
 fwrite($fhout,$XMLExempt);
}
$XMLExempt =
" </SSRS_EXEMPT>
</X0012>\n";
fwrite($fhout,$XMLExempt);
fclose($fhout);
?>

XML Transactions

There are no XML transactions in the regular CVISN CVIEW/SAFER system.  The CVIEW system is designed so it can be augmented to include additional state systems such as SSRS. Below are the state XML transactions to accommodate SSRS systems.  The two transactions are Receipt and Exempt input transactions.

X0011V1.xsd - SSRS Receipt Transaction

<?xml version="1.0"?>
<schema xmlns="http://www.w3.org/2001/XMLSchema"
        targetNamespace="http://www.safersys.org/namespaces/X0011V1"
        xmlns:ts="http://www.safersys.org/namespaces/X0011V1"
        elementFormDefault="qualified">

    <!-- SSRSReceipt Input Transaction, X0011, version 1 -->

    <include schemaLocation="globalTypes.xsd"/>

    <!-- Root element -->

    <element name="X0011" type="ts:X0011V1Type"/>

    <!-- Root element type -->

    <complexType name="X0011V1Type">
      <sequence>
        <element name="INTERFACE" type="ts:interfaceHeaderType"/>
        <element name="TRANSACTION" type="ts:transHeaderType"/>
        <element name="SSRS_BASE" type="ts:SSRSReceiptType"
                 minOccurs="1" maxOccurs="5000"/>
      </sequence>
    </complexType>

    <!-- Interface Header type -->

    <complexType name="interfaceHeaderType">
      <sequence>
          <element name="NAME" type="string" fixed="SAFER"/>
          <element name="VERSION" type="string" fixed="04.02"/>
      </sequence>
    </complexType>

    <!-- Transaction Header type -->

    <complexType name="transHeaderType">
      <sequence>
        <element name="VERSION" type="string" fixed="01.00"/>
        <element name="OPERATION" type="string" fixed="REPLACE"/>
        <element name="DATE_TIME" type="dateTime"/>
        <element name="TZ" type="ts:globalTimeZoneType"/>
      </sequence>
    </complexType>

    <complexType name="SSRSReceiptType">
      <sequence>
        <element name="CARRIER_ID_NUMBER" type="ts:globalCarrierIdNumberType" /><!-- Check these types do we need to have minOccurs with Opt types-->
        <element name="STATE_MC_NBR" type="ts:globalCarrierIdNumberType"/>
        <element name="FMCSA_NBR" type="ts:globalOptCarrierIdNumberType" minOccurs="0" />
<!-- Receipt has this while Exempt does not -->
        <element name="CARRIER_STATE_CD" type="ts:globalJurisdictionType"
 minOccurs="0" />
        <element name="BASEREG_CD" type="ts:localBaseRegTabType"/>
        <element name="SERVICE_DATE" type="date"/>
        <element name="AGENT_LIST_IND" type="string"/>
        <element name="FMCSA_ORDER_IND" type="string"/>
        <element name="SELF_INS_IND" type="string"/>
<!-- need to adjust number -->
        <element name="REQ_INS_AMT" type="string"/>
        <element name="DATESTAMP" type="dateTime"/>
        <element name="SSRS_RECEIPT" type="ts:SSRSReceiptDetailType" minOccurs="1" maxOccurs="100"/>
      </sequence>
    </complexType>
    <complexType name="SSRSReceiptDetailType">
      <sequence>
        <element name="CARRIER_ID_NUMBER" type="ts:globalCarrierIdNumberType" /><!-- Check these types do we need to have minOccurs with Opt types-->
        <element name="TRAVEL_STATE_CD" type="ts:globalJurisdictionType"/>
        <element name="FEE_CHG_ID" type="string"/>
        <element name="RECEIPT_YEAR" type="string"/>
        <element name="VEHICLES" type="string"/>
        <element name="FEE_AMT" type="string"/>
        <element name="REC_CD" type="ts:localRecTabType"/>
        <element name="PRINTED_DTE" type="date"/>
        <element name="ENTERED_DTE" type="date"/>
        <element name="STAT_CD" type="ts:localStatTabType"/>
        <element name="STAT_DTE" type="date"/>
        <element name="ER_CD_DISP" >
            <simpleType>
              <restriction base="string">
                <minLength value = "1"/>
                <maxLength value="32"/>
              </restriction>
            </simpleType>
          </element>
        <element name="DATESTAMP" type="dateTime"/>
      </sequence>
    </complexType>
    <simpleType name="localRecTabType">
            <restriction base="string">
                    <minLength value="1"/>
                    <maxLength value="2"/>
            </restriction>
    </simpleType>
    <simpleType name="localRegTabType">
            <restriction base="string">
                    <minLength value="1"/>
                    <maxLength value="2"/>
            </restriction>
    </simpleType>
    <simpleType name="localBaseRegTabType">
            <restriction base="string">
                    <minLength value="1"/>
                    <maxLength value="2"/>
            </restriction>
    </simpleType>
    <simpleType name="localStatTabType">
            <restriction base="string">
                    <minLength value="2"/>
                    <maxLength value="2"/>
            </restriction>
    </simpleType>
</schema>

X0012V1.xsd - SSRS Exempt Transaction

<?xml version="1.0"?>
<schema xmlns="http://www.w3.org/2001/XMLSchema"
        targetNamespace="http://www.safersys.org/namespaces/X0012V1"
        xmlns:ts="http://www.safersys.org/namespaces/X0012V1"
        elementFormDefault="qualified">

    <!-- SSRSExempt Input Transaction, X0012, version 1 -->

    <include schemaLocation="globalTypes.xsd"/>

    <!-- Root element -->

    <element name="X0012" type="ts:X0012V1Type"/>

    <!-- Root element type -->

    <complexType name="X0012V1Type">
      <sequence>
        <element name="INTERFACE" type="ts:interfaceHeaderType"/>
        <element name="TRANSACTION" type="ts:transHeaderType"/>
        <element name="SSRS_EXEMPT" type="ts:SSRSExemptType"
                 minOccurs="1" maxOccurs="5000"/>
      </sequence>
    </complexType>

    <!-- Interface Header type -->

    <complexType name="interfaceHeaderType">
      <sequence>
          <element name="NAME" type="string" fixed="SAFER"/>
          <element name="VERSION" type="string" fixed="04.02"/>
      </sequence>
    </complexType>

    <!-- Transaction Header type -->

    <complexType name="transHeaderType">
      <sequence>
        <element name="VERSION" type="string" fixed="01.00"/>
        <element name="OPERATION" type="string" fixed="REPLACE"/>
        <element name="DATE_TIME" type="dateTime"/>
        <element name="TZ" type="ts:globalTimeZoneType"/>
      </sequence>
    </complexType>

    <complexType name="SSRSExemptType">
      <sequence>
        <element name="CARRIER_ID_NUMBER" type="ts:globalCarrierIdNumberType" /><!-- Check these types do we need to have minOccurs with Opt types-->
        <element name="STATE_MC_NBR" type="ts:globalCarrierIdNumberType"/>
        <element name="FMCSA_NBR" type="ts:globalOptCarrierIdNumberType" minOccurs="0" />
<!-- Receipt has this while Exempt does not -->
        <element name="CARRIER_STATE_CD" type="ts:globalJurisdictionType"
 minOccurs="0" />
        <element name="BASEREG_CD" type="ts:localBaseRegTabType"/>
        <element name="SERVICE_DATE" type="date"/>
        <element name="AGENT_LIST_IND" type="string"/>
        <element name="FMCSA_ORDER_IND" type="string"/>
        <element name="SELF_INS_IND" type="string"/>
<!-- need to adjust number -->
        <element name="REQ_INS_AMT" type="string"/>
        <element name="DATESTAMP" type="dateTime"/>
        <element name="SSRS_EXEMPT" type="ts:SSRSExemptDetailType" minOccurs="1" maxOccurs="1"/>
      </sequence>
    </complexType>
<!-- SSRS Exempt Detail type -->

    <complexType name="SSRSExemptDetailType">
      <sequence>
        <element name="CARRIER_ID_NUMBER" type="ts:globalCarrierIdNumberType" />
<!-- Check these types do we need to have minOccurs with Opt types-->
        <element name="FEE_CHG_ID" type="string"/>
        <element name="REG_CD" type="ts:localRegTabType"/>
        <element name="ISSUE_DTE" type="date"/>
        <element name="WARN_DTE" type="date" minOccurs="0"/>
        <element name="SUSP_DTE" type="date" minOccurs="0"/>
        <element name="REV_DTE" type="date" minOccurs="0"/>
        <element name="STAT_CD" type="ts:localStatTabType"/>
        <element name="STAT_DTE" type="date"/>
<!-- Check length -->
        <element name="ER_CD_DISP" >
            <simpleType>
              <restriction base="string">
                <minLength value = "1"/>
                <maxLength value="32"/>
              </restriction>
            </simpleType>
          </element>
        <element name="MESSAGE" >
            <simpleType>
              <restriction base="string">
                <minLength value = "0"/>
                <maxLength value="150"/>
              </restriction>
            </simpleType>
          </element>
        <element name="DATESTAMP" type="dateTime"/>
      </sequence>
    </complexType>
    <simpleType name="localRecTabType">
            <restriction base="string">
                    <minLength value="1"/>
                    <maxLength value="2"/>
            </restriction>
    </simpleType>
    <simpleType name="localRegTabType">
            <restriction base="string">
                    <minLength value="1"/>
                    <maxLength value="2"/>
            </restriction>
    </simpleType>
    <simpleType name="localBaseRegTabType">
            <restriction base="string">
                    <minLength value="1"/>
                    <maxLength value="2"/>
            </restriction>
    </simpleType>
    <simpleType name="localStatTabType">
            <restriction base="string">
                    <minLength value="2"/>
                    <maxLength value="2"/>
            </restriction>
    </simpleType>
</schema>