{{ model.title }}
This page is currently being edited by {{ model.editingBy }}.SmartFusion Modules > System Setup > Data Entry > System File > Vertexone
Recent Change:
6.229
- Added new system setting for exporting utility data information for Vertexone.
- Added stored procedures and a view to create a SQL Server Agent Job and execute itIntroduction
This system setting allows the creation of standardized account file and billed consumption export files for Vertexone to the export path set up in the System File.
In addition, a compatible version of SQL Server with SQL Server Agent functionality is required. Specifically, SQL Server Express is not supported.
Selections
Vertexone Settings
Export Settings
- Time interval for export (in hours)
- File path for export
- Last time synced (MM/dd/yyyyy hh:mm:ss format)- This will get set every time the export file is created.
NOTE: For the export it pulls records from ServiceOrder where ModifyDateTime >= Last Time synced in the ConfigurationItem table. After an export is done the Last time synced setting is automatically set.
Export File Layout:
Residence/Account Export file created with name as "YYYYMMDDacctinfo
FILE 1: Residence/Account File -Standardized account file format
| Field | Description | Required/Optional |
|---|---|---|
| account_number | The unique utility account number | Required |
| customer_code | The unique utility identifier representing the payee, person or entity | Required if separate from account_number (hard coded as blank) |
| premise_code | A utility identifier representing the premise | Required if separate from account_number (LocationId) |
| class_code | A utility identifier for the class of account or rate such as single family residential, multi-tenant, irrigation only,etc. May be separated into more than one field such as class_code and fee_code depending on utility's system (Master code) | Required |
| customer_type | Information on whether the customer is renter, owner,HOA etc. (hard coded as blank) | Optional |
| billing_cycle | Identifier for the billing cycle in which the customer gets billed | Optional |
| status | 'active' or 'inactive' at the account level (Customer.Status) | Recommended |
| service_address_line_1 | The property street address (Location.Address1) | Required |
| service_address_line_2 | The property street address secondary field like 'unit','apt',building', etc.(Location.Address2) | Required |
| service_address_city | The city of the service address (Location.City) | Required |
| service_address_state | The state of the service address (Location.State) | Required |
| service_address_zipcode | The zipcode of the service address (Location.zip) | Required |
| service_address_latitude | The latitude coordinate of the service address (Location.latitude) | Optional |
| service_address_longitude | The longitude coordinate of the service address (Location.Longitude) | Optional |
| service_units | The number of units in a multi-tenant building (Location.Number Of Units) ( | Optional |
| irrigable_area | The square feet of the irrigated area (hard coded as blank) | Optional |
| lot_size | The lsquare feet of the property (hard coded as blank) | Optional |
| number_occupants | The number of occupants at the residence (hard coded as blank) | Optional |
| service_occupant_name | The occupant name at the service address (Customer.Name) | Required |
| service_occupant_first_name | The first name of the occupant (hard coded as blank) | Recommended |
| service_occupant_last_name | The last name of the occupant (hard coded as blank) | Recommended |
| service_occupant_email | The email address of the occupant (Customer.Email) | Required |
| service_occupant_phone_1 | The phone number of the occupant (Customer.Phone1) | Recommended |
| service_occupant_phone_2 | The secondary phone number of the occupant (hard coded as blank) | Optional |
| mailing_address_line_1 | The mailing address of the occupant. Used for Home water reports (Customer.MailAddress1) | Required |
| mailing_address_line_2 | The secondary mailing address line of the occupant like 'unit', 'apt', 'building'. Used for Home water reports (Customer.MailAddress2) | Required |
| mailing_address_city | The city of the mailing address. Used for Home water reports (Customer.MailCity) | Required |
| mailing_address_state | The state of the mailing address. Used for Home water reports (Customer.MailState) | Required |
| mailing_address_zipcode | The zipcode of the mailing address. Used for Home water reports (Customer.MailZip) | Required |
| mailing_address_country | The country of the mailing address. Used for Home water reports (hard coded as blank) | Optional |
| mailing_name_line_1 | The first line of the mailing name. (Customer.MailName) | Required |
| mailing_name_line_2 | The secondary mailing name line like 'C/O' or 'Attn'. (hard coded as blank) | Required |
| mailing_first_name | The first name if different from the service occupant first name (hard coded as blank) | Required |
| mailing_last_name | The last name if different from the service occupant last name (hard coded as blank) | Required |
| mailing_email | The email address if different from the service occupant email(hard coded as blank) | Required |
Ongoing billed consumption Export file created with name as "YYYYMMDDch
FILE 2: Consumption File & FILE 2A: Historic Consumption -Standardized account file format
| Field | Description | Required/Optional |
|---|---|---|
| account_number | The unique utility account number (Customer.accountnumber) | Required |
| premise_code | The unique utility identifier representing the premise (LocationId) | Required if separate from account_number |
| meter_id | The utility meter id (ar210.cserial) | Recommended |
| meter_size | The size of the meter (ar210.nmetersize) | Optional |
| read_date | The date of the billed meter read (ar012.ycurrread) | Required |
| period_length | The number of days in this reading period(hard coded as blank) | Optional |
| current_meter_read | The meter read value (ar012.ycurrread) | Recommended (required w/o consumption) |
| previous_meter_read | The previous meter read value (ar012.yprevread) | Recommended (required w/o consumption) |
| consumption | The volume of water billed\used during the billed read period (ar012.yusage) | required (optional w/meter reads) |
| consumption_units | The unit of billed consumption(gal,CF, KGAL,etc.) (Units.NameOfUnit) | optional if universal for utility |
| multiplier | The multiplier to use if different meters measure in different units (ar210.imultiplier) | Recommended |
| read_code | Additional read information (misread, estimated, open, close, adjusted) (ar210.creadcode) | Recommended |
| rate_code | A rate or service code use to determine applicable rates | Optional (required for certain rate structures) (RateTable.crate) |
| allocation | Volume of water allocated/budgeted for the billed read period | Optional (required for allocation/budget)(hard coded as blank) |
| allocation_units | The unit of allocation/budget volume (gal,CF, KGAL,etc.)(hard coded as blank) | Optional (required for allocation/budget) |