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
- 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 |