Bill Table Descriptions
Overview
In the EnergyCAP database, there are three tables that maintain Bill details:
- Bill table
- Bodyline table
- BillAccountMeter (BAM) table
This topic provides detailed information about each table, including:
- Database table contents
- Database table relationships
A thorough understanding of these three tables will be helpful in maintaining the database and extracting meaningful data in EnergyCAP reports and custom spreadsheets.
The Bill Table
The purpose of the Bill Table is to store summary bill information. The Bill table contains all of the optional Bill Header data available through the Workflow Wizard setup process: The following table lists each data element column in the Bill table, along with the input Data Type, NULL information, and a Description of the element.
| Column Name | Data Type | Allow NULLs | Description |
| billID | int | No | Unique bill identifier. A BillID equates to ONE bill in EnergyCAP |
| batchID | int | Yes | References the Batch table and ties a Bill to a Batch |
| accountID | int | No | References the Account table and ties a Bill to an Account |
| beginDate | datetime | No | Start Date of the Bill |
| endDate | datetime | No | End Date of the Bill |
| billingPeriodID | int | No | References the BillingPeriod table and ties a Bill to a Billing Period |
| billingPeriod | numeric(6, 0) | Yes | Billing Period of the Bill |
| billingPeriodYear | int | No | Billing Period Year of the Bill |
| accountPeriodID | int | Yes | References the AccountPeriod table and ties a Bill to a Accounting Period |
| accountPeriod | numeric(6, 0) | Yes | Accounting Period of the Bill |
| accountPeriodYear | int | Yes | Accounting Period Year of the Bill |
| totalCost | money | No | Pay Amount of the Bill |
| estimated | bit | No | Indicates if the Bill is an Estimated Bill or not |
| approved | bit | No | Indicates if the Bill is Approved or not |
| approveDate | datetime | Yes | When the Bill was Approved |
| approvedBy | int | Yes | Who Approved the Bill - References the SystemUser table (approvedby = systemuserid) |
| exported | bit | No | Indicates if the Bill is Exported or not |
| exportDate | datetime | Yes | When the Bill was Exported |
| exportedBy | int | Yes | Who Exported the Bill - References the SystemUser table (exportedby = systemuserid) |
| observationMethodID | int | No | References the ObservationMethod table - Indicates the method by which the Bill was created (Manual, Simulated, Automatic/Imported, Accrual, Estimated) |
| statementDate | datetime | Yes | Statement Date of the Bill |
| dueDate | datetime | Yes | Due Date of the Bill |
| nextReading | datetime | Yes | Next Reading of the Bill |
| controlCode | varchar(255) | Yes | Control Code of the Bill - often stores the Bill Image file name without the file extension |
| invoiceNumber | varchar(32) | Yes | Invoice Number of the Bill |
| invoicePages | int | Yes | Number of Invoice Pages of the Bill |
| checkNumber | varchar(32) | Yes | Check Number used to pay the Bill |
| checkDate | datetime | Yes | Date of the Check used to pay the Bill |
| payStatus | varchar(10) | Yes | Status of the Bill Payment |
| clearedDate | datetime | Yes | Date the Check used to pay the Bill was cleared |
| createdBy | int | No | Who Created the Bill - References the SystemUser table (createdby = systemuserid) |
| createdDate | datetime | No | When the Bill was Created |
| modifiedBy | int | No | Who last Modified the Bill - References the SystemUser table (modifiedby = systemuserid) |
| modifiedDate | datetime | No | When the Bill was last Modified |
| void | bit | No | Indicates if the Bill is VOID |
| dirty | bit | No | Indicates if the Bill is "dirty" - meaning if it is dirty, BillAccountMeter may not have the proper values for the Bill and cleanbill should be run |
| importVerified | bit | No | Indicates if the Bill was verified after import to EnergyCAP |
| accrual | bit | No | Indicate if the Bill is an Accrual |
| accrualReversed | bit | No | Indicates if the Bill is an Accrual and has been reversed |
| accrualReversedDate | datetime | Yes | Indicates if the Bill is an Accrual and when it was reversed |
| exportHold | bit | No | indicates if the Bill is being held from being Exported to AP |
| glExported | bit | No | Indicates if the Bill has been exported to GL |
| glExportDate | datetime | Yes | When the Bill was exported to GL |
| glExportedBy | int | Yes | Who exported the Bill to GL - References the SystemUser table (glexportedby = systemuserid) |
| fromVendor | bit | No | Indicates if the bill was from a vendor or to a customer |
| hasBeenSplit | bit | No | Indicates if the Bill has been split yet in cases where Bill Splits are involved |
| wasSplitDate | datetime | Yes | When the Bill was split |
| transRefNum | varchar(32) | Yes | Not Used |
| paymentType | varchar(32) | Yes | Indicates the method of payment used to pay the Bill |
| actualAmountPaid | money | Yes | Indicates the actual amount that was paid |
Relationships to Bodyline and BillAccountMeter
The Bill table contains the TOTAL COST of the bill – the sum total of the Bodylines of the bill. Unlike BillAccountMeter, no meter-specific information is included. When the Observation Type rules (Ignored, Credit/Provided, Debit/Consumed) are applied the Bodylines of a bill, the total amount should equal the TotalCost in the Bill table.
The Bodyline Table
The Bodyline table contains the line by line information that is entered on the Bill Template. Each line of the Bill Template – the value and the cost and even the caption – are stored in the Bodyline table. It contains the most detailed view of a bill as it contains each and every template line that makes up a bill.
The following table lists each data element column in the Bodyline table and what it refers to.
| Column Name | Data Type | Allow NULLs | Description |
| bodyLineID | int | No | Unique ID for each row of each bill in EnergyCAP - No duplicates ever |
| meterID | int | Yes | References the Meter table. If NULL, indicates the bodyline is an Account Charge, otherwise it indicates which Meter the bodyline is associated with. |
| generalLedgerID | int | Yes | References the Generalledger table. If NULL, indicates the bodyline is not associated with a GL Record. |
| accountID | int | No | References the Account table. Indicates which Account the bodyline is associated with. |
| billID | int | No | References the Bill table. Indicates which Bill the bodyline is associated with. |
| vendorID | int | No | References the Vendor table. Indicates which vendor the bodyline is associated with. |
| observationTypeID | int | No | References the ObservationType table. Indicates which Observation Type the bodyline is associated with. |
| commodityID | int | No | References the Commodity table. Inidcates which commodity the bodyline is associated with. |
| observationMethodID | int | No | References the ObservationMethod table. Indicates the method by which the Bill was created (Manual, Simulated, Automatic/Imported, Accrual, Estimated) |
| unitID | int | Yes | References the Unit table. Indicates the unit of measure that the bodyline value is associated with. |
| value | numeric(19, 6) | Yes | The Value of the bodyline Observation Type |
| cost | money | Yes | The Cost of the bodyline Observation Type |
| costUnitID | int | Yes | References the Unit table. Indicates the unit of measure that the bodyline cost is associated with. |
| displayOrder | int | No | Indicates what order the bodyline appears on the bill when it is viewed in EnergyCAP |
| caption | varchar(32) | Yes | The caption of the bodyline from the Bill Template |
| helpTag | varchar(32) | Yes | The help tag of the bodyline from the Bill Template |
| beginDate | datetime | No | Start Date of the bodyline of the Bill - most times it is the same as the Bill's Start Date |
| endDate | datetime | No | End Date of the Bill - most times it is the same as the Bill's End Date |
Relationships to Bill and BillAccountMeter
Bodyline cost, when the Observation Type rules are taken into account, should total up to be the Bill totalcost. The stored procedure cleanbill will read the bodylines of a particular bill and then summarize them to the BillAccountMeter table according to the Observation type rules. When a Bill is marked as dirty, cleanbill will read the bodylines that match the BillID of the dirty bill and then UPDATE the BillAccountMeter table rows that have the same BillID value. In this way, if the Common Roll-up unit is changed, the Bills in EnergyCAP can be marked as dirty so that cleanbill can re-calculate the proper Common and Global values in the BillAccountMeter table for each of the bills.
About Observation Types and the Observation Type Table
The Bodyline table contains an ObservationTypeID value which corresponds to a particular Observation Type. The master list of Observation Types can be referenced from within the EnergyCAP application by clicking on Tools then Unit System from the menu bar. The Types tab displays each Observation Type and the color indicates how the Observation Type is interpreted/calculated by EnergyCAP. The bodyline table contains the link to this Observation Type for each and every utility bill value that is entered on a bill. It is essentially the building blocks to determine the total cost, consumption, and demand for a bill.
The BillAccountMeter Table
The BillAccountMeter table contains summarized totals by meter (and where MeterID is NULL, a summary of the account charges) by account for each bill. So, as an example, if an account has two meters on it, and a bill is saved, there would be TWO BillAccountMeter records created – one for each meter UNLESS there were account charges entered as well in which case there would be THREE records created. This is the table which the majority of EnergyCAP Custom Spreadsheets, Reports, and PowerViews are based from. The true power of the BillAccountMeter table is its native, Common, and Global roll-up values. The cleanbill process utilitzes the Unit System conversions and setup to transform the bodyline value to Common and Global values. In other words, Bodyline would contain the value of 100 if a Bill were to have 100 THERMS of gas on it. BillAccountMeter would have 100 as the NativeUse, 10 as the Common Use (assuming the Common Roll-up for Natural Gas is DKTHERM), and 1 as the GlobalUse (assuming the Global Roll-up is MMBTU). So BillAccountMeter is critical to reporting on accounts of the same or different commodities that use various units of measure.
The following table lists each data element column in the Bodyline table and what it refers to.
| Column Name | Data Type | Allow NULLs | Description |
| rowNum | int | No | Used as a unique identifier |
| billID | int | No | References the Bill table. Indicates which Bill the bodyline is associated with. |
| masterAccountID | int | No | References the Account table. Indicates the Master Account ID (in cases of Bill Splits), or in other words, the source account which was used to create the bill |
| accountID | int | No | References the Account table. Indicates which Account the bodyline is associated with. |
| meterID | int | Yes | References the Meter table. If NULL, indicates the bodyline is an Account Charge, otherwise it indicates which Meter the bodyline is associated with. |
| commodityID | int | Yes | References the Commodity table. Inidcates which commodity the bodyline is associated with. |
| vendorID | int | No | References the Vendor table. Indicates which vendor the bodyline is associated with. |
| placeID | int | Yes | References the Place table. Indicates where the MeterID is located in the facility tree. |
| billingPeriod | numeric(6, 0) | No | Billing Period of the Bill |
| beginDate | datetime | No | Start Date of the Bill |
| endDate | datetime | No | End Date of the Bill |
| days | int | No | Number of days in the billing period of the bill |
| nativeUse | numeric(19, 6) | Yes | The total of the Usage Observation Type vlaues from Bodyline |
| nativeUsePerDay | numeric(19, 6) | Yes | Native Use divided by the number of days in the billing period. |
| nativeUseUnitID | int | Yes | References the Unit table. Indicates the unit of measure that was setup on the Bill Template for Usage. From the Observation Type UnitID in Bodyline. |
| commonUse | numeric(19, 6) | Yes | The total of the Usage Observation Type vlaues from Bodyline CONVERTED to the Common Roll-up Unit of Measure indicated in the Unit System for the specific commodity |
| commonUsePerDay | numeric(19, 6) | Yes | Common Use divided by the number of days in the billing period. |
| commonUseUnitID | int | Yes | References the Unit table. Indicates the unit of measure in the Unit System for usage Common Roll-up. |
| globalUse | numeric(19, 6) | Yes | The total of the Usage Observation Type vlaues from Bodyline CONVERTED to the Global Roll-up Unit of Measure indicated in the Unit System for all commodities of energy |
| globalUsePerDay | numeric(19, 6) | Yes | Global Use divided by the number of days in the billing period. |
| globalUseUnitID | int | Yes | References the Unit table. Indicates the unit of measure in the Unit System for usage Gobal Roll-up. |
| nativeBillDem | numeric(19, 6) | Yes | Same as nativeuse, but for the Observation Type Billed Demand |
| nativeBillDemUnitID | int | Yes | Same as nativeuse, but for the Observation Type Billed Demand |
| nativeActDem | numeric(19, 6) | Yes | Same as nativeuse, but for the Observation Type Actual Demand |
| nativeActDemUnitID | int | Yes | Same as nativeuse, but for the Observation Type Actual Demand |
| nativeDem | numeric(19, 6) | Yes | Same as nativeuse, but for the Observation Type Demand |
| nativeDemUnitID | int | Yes | Same as nativeuse, but for the Observation Type Demand |
| commonBillDem | numeric(19, 6) | Yes | Same as common use, but for the Observation Type Billed Demand |
| commonBillDemUnitID | int | Yes | Same as common use, but for the Observation Type Billed Demand |
| commonActDem | numeric(19, 6) | Yes | Same as common use, but for the Observation Type Actual Demand |
| commonActDemUnitID | int | Yes | Same as common use, but for the Observation Type Actual Demand |
| commonDem | numeric(19, 6) | Yes | Same as common use, but for the Observation Type Demand |
| commonDemUnitID | int | Yes | Same as common use, but for the Observation Type Demand |
| directCost | money | Yes | The sum of the costs from bodyline with Observation Type rules being considered for the meter indicated by the MeterID |
| directCostPerDay | money | Yes | The sum of the costs from bodyline with Observation Type rules being considered for the meter indicated by the MeterID divided by the number of days in the billing period. |
| propCost | money | Yes | The sum of the costs from bodyline with Observation Type rules being considered for the meter indicated by the MeterID PLUS Account Charges that were evenly distributed across the meters on the bill |
| propCostPerDay | money | Yes | The sum of the costs from bodyline with Observation Type rules being considered for the meter indicated by the MeterID PLUS Account Charges that were evenly distributed across the meters on the bill divided by the number of days in the billing period |
Relationships to Bill and Bodyline
The sum total of the BillAccountMeter propcost should sum to be the Bill totalcost. The Observation Type, value, unitid, cost, and costunitid from bodyline are all used by cleanbill to determine the nativeuse, commonuse, globaluse, nativebilldem, nativeactdem, nativedem, commonbilldem, commonactdem, and commondem in BillAccountMeter. Every time a change is made to the Unit System in EnergyCAP such as changing the Common Roll-up for natural Gas from DKTHERM to MCF, the bill need to be marked as dirty and then the cleanbill process needs to be run so that the Common Use, Common Demand, and Global Use values can be re-calculated and updated to billAccountMeter so the reports will report the correct values according to the new Unit System settings.
Extracting billing data via a Custom Spreadsheet
A Custom Spreadsheet has been created as a tool for reporting on, and extracting billing data. The Custom Spreadsheet is not part of the standard EnergyCAP installation. To obtain a copy of this Custom Spreadsheet, download the Tech Support Custom Spreadsheets file (TechSupportCS.xml) from our Support Suite technical support website.
When the contents and the relationship between EnergyCAP’s three bill tables is understood, the Tech Support Custom Spreadsheets will provide an excellent tool for reporting on, and extracting billing data.
