Export fields used in reformatters

This article lists all exportable AP fields and their names. These are used with Accounting Interfaces.

String literals

String literals are used to identify lines in the export file. Each line of the export file must have a string literal to identify the line type for the Accounting interface.

  • Must contain the letter L plus a number (0-6), depending on the line type.
  • Must be the first field.
  • Must contain the abbreviation and the full name (highlighted in yellow).

Exported cost formatting

The dollar symbol prefix ($) indicates the value is money and not use or demand.
  • Source currency is used for L1 through L5. The dollar symbol prefix ($) indicates the value is money and not use or demand. The cost unit in L5 lists the source currency.
  • System currency is used for L0 and L6. The system currency is set in Settings, Data Reporting Settings.

Bill header (file header)

L0:[BH]

The Bill Header line appears once at the top of a given export file. It contains the highest level summary information about the bills in the file.

Field Name Database Field Field Description Sample Value
Current Date n/a Date the export file is generated 01/10/2020
Current Time n/a Time the export file is generated 03:45:16
Number of bills n/a Number of bills in the export file 9
Total debits n/a Total cost of all bills less than zero in the export file $0.00
Total credits n/a Total cost of all bills greater than zero in the export file $2156.32
Total amount n/a Sum of the Bill.TotalCost amounts for all bills in the file $2156.32

 

Bill summary

L1:[BS]

Bill summary lines appear once per bill. They contain high-level summary informaiton about a bill.

Field Name Database Field Field Description Sample Value
Bill ID Bill.billID Unique ID number assigned to each bill 1024
Cost center CostCenter.costCenterInfo Name of the cost center containing the account associated with this bill My Cost Center
Cost center code CostCenter.costCenterCode Unique code of the cost center containing the account associated with this bill MYCOSTCENTER
Parent cost center

CostCenterParentChildView.

costCenterInfo>

Name of the parent cost center Parent Cost Center
Parent cost center code

CostCenterParentChildView.

costCenterCode

unique code of the parent cost center  PARENTCC
Account Account.accountCode unique account code of the account  1234
Start date Bill.beginDate begin date of the time period covered by the bill 01/10/2020
End date Bill.endDate end date of the time period covered by the bill 02/10/2020
Exported Bill.exported flag to show whether or not the bill was exported 0
Exported date Bill.exportDate Export date of the bill. If the bill was never exported, this field contains the value “<none>”.  <none>
Total cost Bill.totalCost  Total cost of all charges on the bill (source currency) $150.00
Vendor  Vendor.vendorCode Unique vendor code for the vendor  VENDOR2
Billing period Bill.billingPeriod Billing period month for the bill Jan
Billing year Bill.billingPeriodYear Billing period year for the bill 2020
Batch number Batch.batchCode Unique batch number of the bill 012012
Account period Bill.accountPeriod Accounting period month assigned to this bill. This field is blank when the bill is not assigned to an accounting period.  March
Account year Bill.accountPeriodYear Account period year assigned to this bill. This field is blank if the bill is not assigned to an account period.  2020
Control code Bill.controlCode Control code assigned to this bill. This field is blank if there is no control code assigned to this bill. 0010102012
Due date Bill.dueDate Due date assigned to the bill. This field  contains the value “<none>” if no due date is assigned. <none>
Estimated Bill.estimated Flag indicates if the bill is an estimated bill or an actual bill. 0
Invoice number Bill.invoiceNumber Invoice number assigned to the bill. 1456871
Next reading Bill.NextReading Date/time stamp for the next reading 01/01/2020
Statement date Bill.StatementDate Statement date assigned to the bill 01/02/2020
Observation method ID Bill.observationMethodID  Unique identifier for the type of bill. *   
Account service Address Bill.addressid Service address assigned to the account **  
Account active Account.active This flag indicates whether or not the account is active. 1
Created Date Bill.createdDate  Created date of the bill. 01/10/2020
Modified Date Bill.modifiedDate The date the bill was last modified. 01/10/2020

 

*The Observation Method used depends on what method is used to create the bill. Bills can be (1) Automatic (imported), (2) Manual, (3) Estimated (set with flag), (4) Simulated (chargebacks), (5) Accrual, (6) Adjustment (in Version 6), from the ObservationMethod Table.

**Joined to Address table where addresstypeid = 3 (Note: Physical -1, Billing - 2, Service - 3)

Account summary

L2:[AS]

Account summary lines appear once per bill. They contain account line summary information about a particular bill.

Field Name Database Field Field Description Sample Value

Account ID

Account.accountID Unique ID assigned to the account 1024
Start date Bill.beginDate Begin date of the bill 01/10/2020
End date Bill.endDate End date of the bill 02/11/2020
Code Account.accountCode Unique acount code  12345
Display Account.accountInfo Name of the Account Big Electric Co.
Vendor ID Account.vendorID  Unique ID of the vendor 1024
Rollup Cost n/a Sum of all the amounts belonging to the meters and account charges assigned to this account for this bill $1548.23

 

Meter summary

L3:[MS]

Meter summary lines appear once for each meter represented on a bill. If there are multiple meters on a bill, there are multiple meter summaries per account summary. They contain meter-level summary information about a particular bill.

Field Name Database Field Field Description Sample Value

Place Id

LogicalDevice.placeID Unique ID number assigned to the place the meter is assigned. Generated automatically during place creation. 216
Place code Place.placeCode Unique place code of the place to which this meter is assigned MYPLACE
Place display Place.placeInfo Name of the place the meter is assigned My Place
Place type PlaceType.placeTypeCode Unique place type code of the place the meter is assigned BUILDING
Meter ID Meter.meterID Unique ID number assigned to the meter. Generated automatically during meter creation. 52
Meter code LogicalDevice.logicalDeviceCode Unique code assigned to the meter MULTIMETERS-ELE01
Meter display LogicalDevice.logicalDeviceInfo Name for the meter MULTIMETERS-ELE01
Cost n/a Sum of all the charges in this bill  assigned to the meter $834.34
Use n/a  Use assigned to the meter 457.0000
Demand n/a Demand assigned to the meter 1245.0000
Use unit Unit.unitCode Unique unit code assigned to the use for the meter for this bill KWH
Demand unit Unit.unitCode Unique unit code assigned to the demand for the meter for this bill KW
Cost per unit n/a Unit cost for the use assigned to this meter for this bill (Cost / Use) 1.85

 

GL summary

L4:[GS]

GL summary contains GL level summary information about a particular bill.

GL summary lines appear once per meter summary (if GL records are assigned to those meters) or once per account summary (if a GL Record is assigned to the account). 

Field Name Database Field Field Description Sample Value
Code GeneralLedger.generalLedgerCode Unique general ledger code of the General Ledger record assigned to the meter ABC123
Subcode 1 GeneralLedger.subCode01 Unique information placeholder ABC124
Subcode 2-19 GeneralLedger.subCode02-19 Unique information placeholder ABC125-143
Subcode 20 GeneralLedger.subCode20 Unique information placeholder ABC144
Cost n/a Total cost of all charge lines for this GL $123.45

 

Bill line summary

L5:[LS]

Bodyline or bill line summary lines appear multiple times per GL summary (if GL Records are assigned to accounts or meters) or multiple times per account or meter summary. They contain bill bodyline-level information about a particular bill and contain the most granular information of all the export line types.

Field Name Database Field Field Description Sample Value
Commodity Commodity.commodityCode Unique commodity code for the commodity tracked by this bill bodyline MONEY
Observation type ObservationType.observationTypeCode Unique observation type code for the observation type tracked by this bill bodyline CHARGE
Unit Unit.unitCode Unique unit code for the unit of measure tracked by this bill bodyline KWH

Cost unit

Unit.unitCode Unique unit code for the cost unit of measure tracked by this bill bodyline USDOLLARS
Value BodyLine.value Numerical value for this bodyline 1234.0000
Cost Bodyline.cost Cost for this bodyline $123.45

 

Bill footer (file footer)

L6:[BF]

The bill footer line appears once at the bottom of a given export file. It contains highest-level summary information about the bills contained in the file.

Field Name Database Field Field Description Sample Value
Current date n/a Generation date of the export file 01/01/2020
Current time n/a Generation time of the export file 03:45:16
Number of bills n/a Number of bills written to the export file 9
Total debit n/a Total cost of all bills in the export file $0.00
Total credits n/a Total cost of all bills in the export file $31152.79
Total amount n/a Sum of the Bill.TotalCost amounts for all the bills in the export file $31152.79