SmartFusion Modules > System Setup > Data Entry > System File > Daily Balance

Recent Changes:  
6.206 - Added bill_date, the customer location's latest billing date, to the Dropcountr export

6.204 - Added connection_id and our meter location id to the Dropcountr export

6.202 - Added format 5, Smart Earth export

6.195 - Added format 4, Dropcountr export

6.192 - Modified format 2 to include the customer location status

6.109 
-updated format 1 and 2 to exclude finalized customers with a 0 balance.
-added label showing where the file will be saved.

Refer to bottom of page for changes prior to version 6.100

Introduction

A license for Daily Balance Export will be needed to access this functionality. This system setting allows the upload of an exported balance file to a designated FTP site.

In addition, a compatible version of SQL Server with SQL Server Agent functionality is required. Specifically, SQL Server Express is not supported.

Explanation

Selections

Modules - select UB (Tax is currently not enabled)
FTP Directory/File Name
(The export directory will be C:\Program Files (x86)\CSI\SQLServerAgentExports and the export filename will be the same as the FTP filename)
FTP Site Address
FTP User Name
FTP Password - this password is not encrypted or hidden
Delimiter - default is a comma
Format - Changes the format of the export file. Current options include:

  • 1 - UB Arista Format
  • 2 - UB Format 2
  • 3 - UB BEACON Format
  • 4 - UB Dropcountr Format

Time Increment - default is 24 hours

Outline of Process

  • The SQL Server Agent service will need to be running
  • User will need to login using a SQL server name and not "localhost"
  • User will complete field selections
  • On "Save", the user will be asked if they want to create/update a SQL Server Agent job; If they answer "Yes" the settings will be saved and the job will be started with the new settings; If they answer "No" the settings will not be saved and the job will not be started (The user will be notified accordingly)
  • An export file will be placed in C:\Program Files (x86)\CSI\SQLServerAgentExports
  • This file will also be uploaded to the appropriate FTP site
  • Windows firewall settings can affect a successful FTP upload; The most common scenario is that a new but empty file is found on the FTP site

Output File Layout for Utility Billing - Arista Format

  • Originally included in SmartFusion for the city of Toccoa
  • Includes a delimiter as specified on the form. As mentioned above, default is ",", but the user can input any symbol
  • Includes the delimiter prior to the first field
  • Includes a header on the 1st line
  • Includes an '*' for the 1st field
  • Dates can be formatted as MM/dd/yyyy or MM-dd-yyyy
  • Amounts without a value will be set to 0
Field Comments
CustomerID Account Number
ServiceAddress
DueDate
BillDate
ArrearsAmountDue Total Amount Due - Current Amount Due
If < 0 will be set to 0
TotalAmountDue same as CurrentBalanceDue
CurrentAmountDue Current Bill Amount
CurrentBalanceDue same as TotalAmountDue
LastPaymentAmount
LastPaymentDate

Output File Layout for Utility Billing - Format 2

  • Originally included in SmartFusion for the city of Jennings, LA
  • Includes a delimiter as specified on the form. As mentioned above, default is ",", but the user can input any symbol
Field Comments
AccountNumber Account Number
Name Customer Name
StreetAddress
City
State
Zip
BalanceDue Sum of amount
RouteNumber
RouteSequnce
RouteSuffix
RouteExtension
LocationStatus values are Inactive, Active, PendingActive, PendingFinal, and Finalized

Output File Layout for BEACON - Format 3

  • Originally included in SmartFusion for the Town of Biltmore Forest, NC
  • Includes a delimiter as specified on the form. As mentioned above, default is ",", but the user can input any symbol
Field Comments
Account_ID Required
Account_First_Name
Account_Last_Name
Account_Full_Name
Account_Email
Account_Phone
Billing_Address_Line1
Billing_Address_Line2
Billing_Address_Line3 Blank
Billing_City
Billing_State
Billing_Zip
Billing_Country Defaults to "USA"
Person_ID
Account_Status
Account_Portal_Status Defaults to "S"
Account_Billing_Cycle
Location_ID Required
Location_Name Blank
Location_Address_Parity Blank
Location_Address_Line1
Location_Address_Line2
Location_Address_Line3 Blank
Location_City
Location_State
Location_Zip
Location_County_Name Blank
Location_Country Defaults to "USA"
Location_Latitude
Location_Longitude
Location_Site
Location_Main_Use Blank
Service_Point_ID
Service_Point_Type
Service_Point_Cycle
Service_Point_Route
Service_Point_Class_Code
Service_Point_Class_Code_Normalized
Service_Point_Latitude
Service_Point_Longitude
Service_Point_Timezone Blank
Meter_ID can be the same as Meter_SN, Required
Meter_SN Serial Number
Meter_Manufacturer
Meter_Model
Meter_Size
Meter_Size_Unit
Meter_Note
Meter_Continuous_Flow
Register_Number
Register_Unit_Of_Measure
Register_Resolution
Meter_Install_Date
Meter_Install_Start_Read
Meter_Removal_Date
Meter_Removal_End_Read
SA_Start_Date
SA_End_Date
Endpoint_SN ERT transponder ID
Endpoint_Type ERT Type, Only populates if the value is "J", "N", "G", or "Z", Only G is case-sensitive - all other letters will be upper-cased
Endpoint_Install_Date Blank
Endpoint_Removal_Date Blank
Read_Sequence
Alert_Code Blank
High_Read_Limit Blank
Low_Read_Limit Blank
Utility_Use_1 Blank
Utility_Use_2 Blank

Output File Layout for Dropcountr - Format 4

  • Comma delimited export
  • filename will be named "connections" with a timestampe appended, for example, "connections-2023-11-21132048.csv"
Field Comments
account_id string, required
meter_id string, required
premise_id string
active_from RFC3339 timestamp
active_until RFC3339 timestamp, required
pricing_id string, required
customer_name string, required
service_address_street string, required
service_address_city string, required
service_address_state string, required
service_address_zip string, required
billing_started_on RFC3339 date, required
bill_period ISO8601 duration, required
email string
connection_id string, our meter location id, required
bill_date RFC3339 date

Output File Layout for Smart Earth - Format 5

Please see this page for details on the specification

Dev test settings

please see attached document for dev test setings using skynet ftp You can not use local host, so you'll need a named server instance devsettings.png

Changes Prior to Version 6.100

6.57 - Modified format 2 to only include finalized customer locations if they owed a balance

6.54 - Added a third format for BEACON export

6.52 - Added route suffix and route extension for ub daily balance export for format 2

6.50 - Added route sequence and suffix for ub daily balance export for Format 2

6.45 - Added a second format for export and a control to allow for that selection.

6.36 - Modified the file export to remove a space at the beginning of each line.

6.35 - Modified the file export to include the specified delimiter at the beginning of each line in the export.

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