SmartFusion Modules > System Setup > Data Entry > System File > Government Windows

Recent Changes: 
6.174 
- Added options to use a custom, user defined folder for Governement Windows UB and Tax exports.

6.162 
- Added an extended format for tax customers with a "Government Windows Extended Tax" license  

6.103 
- Changed the logic to allow all accounts with a balance that does not equal zero to be exported, regardless of location status

6.31 
- Corrected formatting for the amount and due date fields

6.30
- Added new system setting for exporting Government Windows in Tax and Utility Billing   
- Added stored procedures and a view to create a SQL Server Agent Job and execute it

Selections

  • Modules - select TX or UB
  • FTP Utility Billing Directory/File Name (Current location utilities/in/filename.csv)
  • Export UB to custom location - if checked will allow the user to browse for a folder location
  • FTP Tax Directory/File Name (Current location taxes/in/filename.csv)
  • Export TX to custom location - if checked, will allow the user to browse for a folder location
  • FTP Site Address (ftp.governmentwindow.com)
  • FTP User Name
  • FTP Password
  • Time Increment - default is 24 hours

Outline of Process

  • User will complete field selections
  • On "Save" user will be prompted to run with current settings. If yes, a SQL Server Agent job will be created/updated
  • For now, the job will need to be started manually
  • An export file will be placed in C:\Program Files (x86)\CSI\SQLServerAgentExports (Separate files will be created for Utility billing and Tax)
  • This files will also be uploaded to the appropriate FTP site

Output File Layouts

The file format for both Taxes and Utility Billing Export files will be a CSV file

Utility Billing
**Field**         | **Field Type**          
------------------|--------------------------
Account #         | ALPHA/Numeric           
Account name      | ALPHA/Numeric           
Service address   | ALPHA/Numeric           
Route             | Numeric                 
Sequence          | Numeric                                 
Amount due        | Decimal                 
Due date          | Date    
Cash only         | Logical
Tax Billing Export
**Field**         | **Field Type**          
------------------|--------------------------
Tax year          | Numeric                 
Installment       | Numeric                 
Bill#             | Numeric                 
Owner name        | ALPHA/Numeric           
Property address  | ALPHA/Numeric           
Property ID       | ALPHA/Numeric           
Due date          | Date                    
Amount due        | Decimal
Tax Billing Export - Extended Format

This format requires a license in Support Utilities

**Field**                 | **Field Type**          
--------------------------|-----------------------
Tax year                  | Numeric                 
Installment               | Numeric                 
Bill#                     | Numeric                 
Owner name                | ALPHA/Numeric           
Property address          | ALPHA/Numeric           
Property ID               | ALPHA/Numeric           
Due date                  | Date                    
Amount due                | Decimal      
Total Penalties           | Decimal
Total Interest            | Decimal
Total Payments            | Decimal
Exemption #1 Description  | ALPHA/Numeric
Exemption #1 Amount       | Decimal
Exemption #2 Description  | ALPHA/Numeric
Exemption #2 Amount       | Decimal
Exemption #3 Description  | ALPHA/Numeric
Exemption #3 Amount       | Decimal
Exemption #4 Description  | ALPHA/Numeric
Exemption #4 Amount       | Decimal
Exemption #5 Description  | ALPHA/Numeric
Exemption #5 Amount       | Decimal
Assessment #1 Description | ALPHA/Numeric
Assessment #1 Amount      | Decimal
Assessment #2 Description | ALPHA/Numeric
Assessment #2 Amount      | Decimal
Assessment #3 Description | ALPHA/Numeric
Assessment #3 Amount      | Decimal
Assessment #4 Description | ALPHA/Numeric
Assessment #4 Amount      | Decimal
Assessment #5 Description | ALPHA/Numeric
Assessment #5 Amount      | Decimal     
Levy Description          | ALPHA/Numeric
Levy Rate                 | Decimal
Account Number            | INT
FMV                       | NUMERIC
Mail Address1             | ALPHA/Numeric
Mail Address2             | ALPHA/Numeric
Mail City                 | ALPHA/Numeric
Mail State                | CHARACTER
Mail Zip                  | ALPHA/Numeric

Troubleshooting Tips

  • In order for the job to run, SQL Server Agent must be started. Please refer to this page for details on how to set up SQL Server Agent to automatically restart.
  • If the file is sent but there is no data in the file, it is potentially due to the windows firewall. The 'File Transfer Program' must be allowed through the Domain for it to work correctly.
  • FTP related errors are logged in the SQL error log.
  • SQL Server Agent Jobs require the server name and not localhost. A warning message should appear if a user is logged in with localhost instructing them to log off and log back in with a valid server name.
  • Data files as well as temp FTP command files are located in C:\Program Files (x86)\CSI\SQLServerAgentExports on the server. The files will be overridden the next time the job runs if the file names are the same. The data files are the files that are FTPed to Government Windows and could be done manually if needed. The FTP commands are in the Temp FTP command text files

Dev Notes

SQL error logging exists for errors related to FTP. We are currently handling the following errors

  • Unknown host - Issues related to the FTP site location
  • Login failed - Login credentials issue
  • Could not create file - Issues related to the FTP directory location or file creation