AP Export Fields - Dictionary and Best Practice Reference
Last Revised: EnergyCAP Release and Database Version: EnergyCAP Enterprise 6.2.64.130 [DB 64]
Purpose:
This document lists and describes all exportable AP fields and (if applicable) their field names in the EnergyCAP Enterprise database (assuming the database version mentioned above). This document will be helpful in developing specifications and processes for sharing EnergyCAP information with accounting (AP/GL) interfaces.
This topic does not provide details of the EnergyCAP bill export process. To learn more about exporting bill data, see Bill Export Detail and Process .
Line Selection
During the export setup, the user may select which lines of data to export. Best practices as of 4Q 2011 dictate that AT MINIMUM, the Bill summary, Account summary, Meter summary, G/L summary, and Bill Line summary should be selected for export. The individual fields within each of these lines may be chosen at the Project Manager’s discretion.
A Note on String Literals
String Literals are used to clearly identify individual lines of the export file. Best practices dictate that for EVERY line type used for bill export, a string “literal” field should be added to the output fields to identify the line type for any AP interface. This is done during export profile definition using the EnergyCAP export interface:
At minimum, the string literal must contain the letter “L” plus a number (0-6), depending upon the line type. Also, the string literal MUST be the FIRST field, in order, from top to bottom. The string literal should contain the [abbreviation] and full name of the line type. Custom also dictates that an “@” character is added to the END of the string literal. Customarily, each string literal will contain the name of the output line with which it is associated.
Below is an example demonstrating how to create and insert the string literal for the Bill Header line of the export profile during profile definition:
Bill Header
The Bill Header line appears ONCE at the top 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 | This is the date that the export file is generated. | 01/10/2012 |
| Current time | N/A |
This is the time that the export file is generated . |
03:45:16 |
| Number of bills | N/A | This is the number of bills written to the export file. | 9 |
| Total debits | N/A | This is the total cost of all debits in the export file. | $0.00 |
| Total credits | N/A | This is the total cost of all credits in the export file. | $31152.79 |
| Total amount | N/A | This is the sum of the Bill.TotalCost amounts for all the bills in the export file. | $31152.79 |
Bill Summary
Bill Summary lines appear ONCE per bill. They contain high-level summary information about a particular bill.
| Field Name | Database Field | Field Description | Sample Value |
| Bill ID | Bill.billID | This is the unique ID number assigned to each bill. | 1024 |
| Cost center | CostCenter.costCenterInfo | This is the display name of the Cost Center containing the Account associated with this bill. | My Cost Center |
| Cost center code | CostCenter.costCenterCode | This is the unique code of the Cost Center containing the Account associated with this bill. | MYCOSTCENTER |
| Parent cost center | CostCenterParentChildView.costCenterInfo | This is the display name of the Cost Center containing the Cost Center associated with the Account associated with this bill. | Parent Cost Center |
| Parent cost center code | CostCenterParentChildView.costCenterCode | This is the unique code of the Cost Center containing the Cost Center associated with the Account associated with this bill. |
PARENTCC |
| Account | Account.accountCode | This is the unique account code of the Account associated with this bill. | 66661 |
| Start date | Bill.beginDate | This is the begin date of the time period covered by the bill. | 01/10/2012 |
| End date | Bill.endDate | This is the end date of the time period covered by the bill. | 02/09/2012 |
| Exported | Bill.exported | This is the flag to show whether or not the bill has been exported. | 0 |
| Exported date | Bill.exportDate | This is the date upon which the bill has been exported. If the bill has never been exported, this field will contain the value “<none>”. |
<none> |
| Total cost | Bill.totalCost |
This is the total cost of all charges on the bill | $150.00 |
| Vendor |
Vendor.vendorCode | This is the unique vendor code for the vendor assigned to the account with which this bill is assigned. | VENDOR2 |
| Billing period | Bill.billingPeriod | This is the billing period month to which this bill is assigned. | Jan |
| Billing year | Bill.billingPeriodYear | This is the billing period year to which this bill is assigned. | 2012 |
| Batch number |
Batch.batchCode | This is the unique batch number to which this bill is assigned. This field will be blank if the bill is not assigned to a batch. | 012012 |
| Account period | Bill.accountPeriod | This is the account period month assigned to this bill. This field will be blank if the bill is not assigned to an account period. |
March |
| Account year | Bill.accountPeriodYear | This is the account period year assigned to this bill. This field will be blank if the bill is not assigned to an account period. |
2012 |
| Control code | Bill.controlCode | This is the control code assigned to this bill. This field will be blank if there is no control code assigned to this bill. | 0010102012 |
| Due date | Bill.dueDate | This is the due date assigned to the bill. This field will contain the value “<none>” if no due date is assigned. | <none> |
| Estimated | Bill.estimated | This flag indicates whether the bill is an estimated bill or an actual bill. | 0 |
| Invoice number | Bill.invoiceNumber | This is the invoice number assigned to the bill. | 14243414241 |
| Next reading | Bill.NextReading | The Date/Time Stamp of when we expect the next reading to be taken. | 01/02/2012 |
| Statement date | Bill.StatementDate | This is the statement date assigned to the bill. | <none> |
| Observation method ID | Bill.observationMethodID |
This is the unique identifier for the type of bill. * |
|
| Account service Address | Bill.addressid | This is the service address assigned to the account to which this bill is assigned.** | |
| Account active | Account.active | This flag indicates whether or not the account to which this bill is assigned is active. | 1 |
| Created Date | Bill.createdDate |
This is the date on which the bill was created and saved. | 01/10/2012 |
| Modified Date | Bill.modifiedDate | This is the date on which the bill was last modified. | 01/10/2012 |
*Bills can be Automatic (1), Manual (2), Estimated (3), Simulated (4), Accrual (5), Adjustment (6) … from the ObservationMethod Table. The Observation Method used depends on what method is used to create the bill. For example, Manual is when bills are hand-keyed, Automated is for Bills that are imported, Estimated is when the Estimated flag is checked, Simulated is from processors like Bill Splits and Calculated Accounts. Accrual bills are created by the accruals module/processor, and Adjustment bills are when the “monkey wrench” is used from the bill edit screen.
**Joined to Address table where addresstypeid = 3 (Note: Physical – 1, Billing – 2, Service – 3)
Account Summary
Account Summary lines appear ONCE per bill. They contain account-level summary information about a particular bill.
| Field Name | Database Field | Field Description | Sample Value |
| Account ID | Account.accountID | This is the unique ID number assigned to the account. | 1024 |
| Start date | Bill.beginDate | This is the begin date of the time period covered by the bill. | 01/10/2012 |
| End date | Bill.endDate | This is the end date of the time period covered by the bill. | 02/09/2012 |
| Code |
Account.accountCode | This is the unique account code of the Account associated with this bill. | 66661 |
| Display |
Account.accountInfo | This is the display name of the Account associated with this bill. | 66661 |
| Vendor ID | Account.vendorID | This is the unique ID number of the vendor to which this account is assigned. Generated automatically by EnergyCAP during vendor creation. |
1024 |
| Vendor | Vendor.vendorCode | This is the unique code associated with the vendor to which this account is assigned. |
VENDOR1 |
| Rollup cost | N/A | This is the sum of all the dollar amounts belonging to the meters and account charges assigned to this account for this bill. | $1548.23 |
Meter Summary
Meter Summary lines appear MULTIPLE times 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 | This is the unique ID number assigned to the place to which this meter is assigned. Generated automatically by EnergyCAP during Place creation. | 216 |
| Place code | Place.placeCode | This is the unique place code of the Place to which this meter is assigned. | MYPLACE |
| Place display | Place.placeInfo | This is the display name of the Place to which this meter is assigned. | My Place |
| Place type | PlaceType.placeTypeCode | This is the unique place type code of the Place to which this meter is assigned. | BUILDING |
| Meter ID | Meter.meterID | This is the unique ID number assigned to this meter. Generated automatically by EnergyCAP during Meter creation. | 52 |
| Meter code | LogicalDevice.logicalDeviceCode | This is the unique code assigned to this meter. | MULTIMETERS-ELE01 |
| Meter display | LogicalDevice.logicalDeviceInfo | This is the display name assigned to this meter. | MULTIMETERS-ELE01 |
| Cost | N/A | This is the sum of all the charges in this bill which are assigned to this meter. |
$845.12 |
| Use | N/A | This is the usage assigned to this meter in this bill. | 457.0000 |
| Demand | N/A | This is the demand assigned to this meter in this bill |
1245.0000 |
| Use unit |
Unit.unitCode | This is the unique unit code assigned to the use for this meter for this bill. | KWH |
| Demand unit | Unit.unitCode | This is the unique unit code assigned to the demand for this meter for this bill. | KW |
| Cost per Unit | N/A | This is the unit cost for the usage assigned to this meter for this bill (Cost / Use). | 1.85 |
G/L Summary
G/L, GL, or General Ledger Summary lines appear ONCE per Meter Summary (if GL records are assigned to those meters), ONCE per Account Summary (for account-charges, if a GL record is assigned to the meter), or MULTIPLE times per Account Summary (for special charges, if the user inserts extra, non-template bodylines). They contain GL-level summary information about a particular bill.
| Field Name | Database Field | Field Description |
Sample Value |
| Code | GeneralLedger.generalLedgerCode | This is the unique general ledger code of the General Ledger record assigned to this meter. | ABC123 |
| Subcode 1 | GeneralLedger.subCode01 | This is a unique information placeholder. |
ABC124 |
| Subcode 2-19 | GeneralLedger.subCode02-19 | This is a unique information placeholder. | ABC125-143 |
| Subcode 20 | GeneralLedger.subCode20 | This is a unique information placeholder. | ABC144 |
| Cost |
N/A | This is the rolled-up cost of all charge lines underneath this GL. | $123.45 |
Bill Line Summary
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 | This is the unique commodity code for the commodity tracked by this bill bodyline. | MONEY |
| Observation type | ObservationType.observationTypeCode | This is the unique observation type code for the observation type tracked by this bill bodyline. | CHARGE |
| Unit | Unit.unitCode | This is the unique unit code for the unit of measure tracked by this bill bodyline. |
KWH |
| Cost unit | Unit.unitCode | This unique unit code for the cost unit of measure tracked by this bill bodyline. | USDOLLARS |
| Value | BodyLine.value | This is the numerical value for this bodyline. | 1234.0000 |
| Cost | Bodyline.cost | This is the cost for this bodyline. | $123.45 |
Bill Footer
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 |
This is the date that the export file is generated. |
01/10/2012 |
| Current time | N/A |
This is the time that the export file is generated. |
03:45:16 |
| Number of bills | N/A |
This is the number of bills written to the export file. |
9 |
| Total debits | N/A |
This is the total cost of all debits in the export file. |
$0.00 |
| Total credits | N/A |
This is the total cost of all credits in the export file. |
$31152.79 |
| Total amount | N/A |
This is the sum of the Bill.TotalCost amounts for all the bills in the export file. | $31152.79 |
