External Tasks in EnergyCAP
There are several tasks that can be performed external to the EnergyCAP program, including import (imptsk) and export (exptsk) of billing data, as well as bill audits (audittsk) e-mail report batches (rpttsk) and accruals (accrualesttsk).
The executable task files are located in the EnergyCAP program directory, and can be scheduled via the Windows interface for Scheduled Tasks (Control Panel/Scheduled Tasks).
NOTE: It is possible to use an ADO Connection Object (encapsulated in quotes) for the [dsn] connection string referenced in the tasks below. An example of the usage for the ADO Connection Object is:
-d "Provider=SQLOLEDB;Password=MyesuserPassword;User ID=esuser;Initial Catalog=MyDatabaseName;Data Source=MyOwnComputer\SQLEXPRESS"
The AUDIT task (AUDITTSK.EXE)
Audit Task is used for external auditing of EnergyCAP utility bill data. As in the EnergyCAP application, the Audit Task permits the user to specify the group, dates or accounts to be audited. The audit task automates repetitive, time-consuming processes by allowing them to run unattended during times of reduced network and/or database use. Audit speed may be enhanced if tasks can be run on a devoted workstation. This will help ensure maximum processor efficiency.
The Audit Task Applet command line will execute the specified Audit Group. Therefore, the audit group must exist, and the EnergyCAP User indicated in the command line MUST have permission to execute Audits.
Not all options are required depending on what type of audit is being run. For billing audits, the account number would be used and not the channel option. If no dates, channels, or accounts are specified, the audit group will run for all. The required fields are the dsn, user, pwd, start date, end date, audit group and logfile.
Data that ‘fails’ the Audit will appear in the Workflow Manager of ECE. To review this data, expand the Channels Folder, or Bill Folder and then select the ‘Problems’ folder. The Channels or bills with data that needs to be reviewed will appear on the right side of the screen and explanations of why the data ‘failed’ the applied Audit are provided in the lower window. You may need to select edit and refresh if you are already in the application to display any new problems found by the audits.
The usage is:
audittsk -d [dsn] -u [user] -p [pwd] -g [auditgroup] -a [approve bills Yes/No] -b [batch code] -s [start date] -e [end date] -l [logfile]
Where:
- [dsn] is the ODBC data source name OR a complete ADO Connection String
- [user] is the system user id (must have permissions to run audits)
- [pwd] is the system password for the above user
- [auditgroup] is the id of the EnergyCAP audit group to be run
- [auto-approve bills Yes/No] YES will automatically approve bills that have passed the audit group specified
- [batch code] is batch code for the batch to be audited
- -s [start date] -e [end date] in yyyy-MM-dd format defines the date range in which the bill create date must fall for inclusion in the audit (see Notes below)
- [logfile] is the log filename
EXAMPLE: "c:\program files\energycap enterprise\rel51-52-12\audittsk" -d "energyorg" -u autouser -p autouser -g quickaudit -a YES -b 98765 -l "c:\data\audittask-log.log"
NOTES: Not all parameters are necessary.
For Channel audits, the dates define a range in which the data observation date must fall.
For Bill audits, the audit task selects EITHER the bills in the specified batch OR all bills with a Create date falling between the start and end dates specified. The Create date is the date when the bill was entered into EnergyCAP. When date switches AND batch code switches are both present, the Batch code takes priority over the Create date range.
For start/end dates, it is also permissible to specify (in lieu of a date) a value indicating the number of days before the current computer system date.
EXAMPLE: 3d = three days prior to the current system date. The number of days is assumed to be negative. To audit the previous 2 days of data, the format should be ‘-s 2d’). The dates used will look at the bill dates or channel dates (not the date it was entered, etc).
-e [end date] is the date [yyyy-MM-dd] or [number]d to stop auditing data (the number of days is assumed to be negative. To end the audit two days previous to the system date, the format should be ‘-e 2d’). If you are running this task overnight, either have the end date 1 day prior to the system date, or do not include this switch in the command line.
The EXPORT task (EXPTSK.EXE)
Export Task is used for external export of EnergyCAP utility bill data.
The usage is:
exptsk -d [dsn] -u [user] -p [pwd] -c [converter] -f [path] -s [section]
Where:
- [dsn] is the ODBC data source name
- [user] is the system user id
- [pwd] is the system password
- [converter] is the converter; the specified converter must be STANDARD:BILL_TEXT
- [path] is the file to be exported
- [section] is the export profile name (as previously defined and listed in the drop-down list of Bill Export Profiles in the Bill Export window in EnergyCAP).
NOTE: All parameters listed above are required for successful export.
EXAMPLE: exptsk -d myodbc -u myuser -p mypassword -c STANDARD:BILL_TEXT -f “c:\temp\apexp_file.txt” -s myexportprofile
The IMPORT task (IMPTSK.EXE)
Import Task is a multi-use task which can be used for external import of EnergyCAP utility bill data, setup of accounts and meters, and processing of interval data. Functionality is dependent upon the converter selected (see related topics).
IMPORT converter names:
STANDARD:SIMPLE_TEXT (Channel Data)
STANDARD:ACCOUNT_IMPORT (Setup Sheet Data)
STANDARD:HISTORICAL_BILL_IMPORT (Utility Billing Data)
The usage is:
imptsk -d [dsn] -u [user] -p [pwd] -c [converter] -f [path] -x -a [path] -l [path] -s [section] -v [verbose messages] -remote -t [traceID]
Where:
- -d [dsn] is the ODBC data source name
- -u [user] is the system user id
- -p [pwd] is the system password
- -c [converter] is the name of the converter
- -f [path] is the file to be imported
- -x will delete the input file after importing the data
- -a [path] will archive the data to the path specified
- -l [path] is the log file
- -s [section] is the profile section name
- -v [message text] when used, will add additional detail to the import log file.
- -remote is the command to connect to the HTTP data provider (use in conjunction with the -d switch, which identifies the Datasource from the Catalog Server (as displayed in the EnergyCAP Login window)
- -t [traceID] is the SQL server trace to initiate during verification
EXAMPLE: The imptsk command could be used with the following syntax to import historical bills from a bills.csv file to the organization1 database:
imptsk.exe -u myuser -p mypassword -d organization1 -c STANDARD:HISTORICAL_BILL_IMPORT -f “c:\temp\bills.csv” -l “c:\temp\bills_log.txt” -remote
NOTE: Not all parameters are necessary.
The REPORT task (RPTTSK.EXE)
Report Task is a command line program designed to facilitate e-mail distribution of report batches. When being used with Report Email Groups, it is typically run as a Window scheduled task.
The usage is:
Rpttsk -d [dsn] -u [user] -p [password] -b [report batch] -l [logfile]
Where
- -d is the ODBC datasource name. An ODBC connection is required to run the reports.
- -u is the EnergyCAP system user id of the user that set up the Report Email Groups.
- -p is the EnergyCAP system password of the user that set up the Report Email Groups.
- -b is the name of a report batch. This option is not used with Report Email Groups.
- -l is the log file name. If the file does not exist, it will be created.
- -a automatically find and run the batches. This switch must be set to use Report Email Groups.
A command line example is:
rpttsk.exe -d myodbc -u myusername -p mypassword -l C:\temp\rpttsk.log -a
The ACCRUAL Tasks (ACCRUALESTTSK.EXE and POPMDAYTSK.EXE)
The Accrual tasks can be used to initiate the accrual function external to EnergyCAP, producing the estimated accrual data for MDay (steps 1 and 2 in the accrual process).
POPMDAYTSK.EXE is the initialization step which must be done before everything else because it populates the database with historical data for the meters that will be used to create accrual bills.
ACCRUALESTTSK is the way to generate the estimated data which is used when accrual bills are generated.
For POPMDAYTSK.EXE:
The usage is:
popmdaytsk.exe -r [response file] -d [dsn] -u [user] -p [pwd] -s [start date] -e [end date] -l [logfile] -v [vendor] -c [cost center] -pl [place] -o [commodity]
Where
- -r is the response file (name of file containing commands).
- -d is the datasource name. An ODBC or ADO connection is required to run the reports.
- -u is the EnergyCAP system user id of the user seeking to perform the action.
- -p is the EnergyCAP system password of the user of the user seeking to perform the action.
- -s is the start date (yyyy-MM-dd) or [number]d
- -e is the end date (yyyy-MM-dd) or [number]d
- -l is the log file name. If the file does not exist, it will be created.
Only ONE of the following options should be used:
- -v is the Vendor Code.
- -c is the Cost Center Code.
- -pl is the Place Code.
- -o is the Commodity Code.
You may specify a date as the number of days before "today" by entering, for example, -s 7d to indicate a start date seven days before "today". "Today is the current system date when the program runs.
Other options can be set using a response file.
NOTE:
The -r option to use a response file allows ALL the other options to be kept in a response file. Using this option reduces the command line to something like this:
EXAMPLE: popmdaytsk -r PopulateMDayOptions.txt
For ACCRUALESTTSK.EXE:
The usage is:
accrualesttsk -r [response file] -d [dsn] -u [user] -p [pwd] -s [start date] -e [end date] -l [logfile] -v [vendor] -c [cost center] -pl [place] -o [commodity]
Where
- -r is the response file (name of file containing commands).
- -d is the ODBC datasource name. An ODBC connection is required to run the reports.
- -u is the EnergyCAP system user id of the user seeking to perform the action.
- -p is the EnergyCAP system password of the user of the user seeking to perform the action.
- -s is the start date (yyyy-MM-dd) or [number]d
- -e is the end date (yyyy-MM-dd) or [number]d
- -l is the log file name. If the file does not exist, it will be created.
Only ONE of the following options should be used:
- -v is the Vendor Code.
- -c is the Cost Center Code.
- -pl is the Place Code.
- -o is the Commodity Code.
You may specify a date as the number of days before "today" by entering, for example, -s 7d to indicate a start date seven days before "today". "Today is the current system date when the program runs.
Other options can be set using a response file.
Sample Response Files
EXAMPLE #1
# Sample Response File for use with popmdayconsole.exe or popmdaytsk.exe
# Lines that begin with # are comments and ignored
# Format is OPTION=VALUE or OPTION="A VALUE"
DATABASE=DSN_NAME
USERID="User Name"
PW=PASSWORD
# Dates use format 2009-01-20
BEGINDATE=2007-07-01
ENDDATE=2007-09-01
# Default is to process ALL accounts but you can limit to ONE group # of accounts defined by Vendor OR by CostCenter OR by Place OR by Commodity
# VENDOR=VENDORCODE
# or
# COSTCENTER=COSTCENTERCODE
# or
# COMMODITY=ELECTRIC
# or
# PLACE=MYPLACE
# if you provide more than one, the LAST one will be used.
# to use just one account provide its account code
# SINGLEACCOUNT="8000 484 577 0"
EXAMPLE #2
# Sample Response File for use with accrualestconsole.exe and accrualesttsk.exe
# Lines that begin with # are comments and ignored
# Format is OPTION=VALUE or OPTION="A VALUE"
DATABASE=DSN_NAME
USERID=USERLOGIN
PW=USERPASSWORD
# Dates use format 2009-01-20
BEGINDATE=2007-07-01
ENDDATE=2007-09-01
# Estimate missing defaults to YES with 5 days averaged for estimate
# This sets value to 5 which is the same as the default
ESTIMATEMISSING=5
# This turns estimation off -- it undoes the previous line
# ESTIMATEMISSING=NO
# This turns overwrite of existing values on
OVERWRITE=YES
# This is the same as OVERWRITE plus it will also overwrite manually entered data
OVERWRITEMANUAL=NO
# Default is to use same cost as source. This will increase it by 10%
# COSTADJUSTVALUE=10
# Another option is to use the current unit cost x usage instead
UNITCOSTOPTION=YES
# When UNITCOSTOPTION is YES, COSTADJUSTVALUE is ignored
# Default is to use actual accrual from last year
# To use most recent accrual data do this
LASTDAYDATA=YES
# Default is to process ALL accounts in the user's topmost cost center
# but you can limit to ONE group # of accounts defined by
# Vendor OR by CostCenter OR by Place OR by Commodity
# VENDOR=VENDORCODE
# or
# COSTCENTER="COST CENTER CODE"
# or
# COMMODITY=ELECTRIC
# or
# PLACE=MYPLACE
# if you provide more than one, the LAST one will be used.
# To use just one account provide its account code
# SINGLEACCOUNT="8000 484 577 0"
The VISUAL BASIC SCRIPT EXECUTION task (VBTSK.EXE)
The Visual Basic Script Execution task can be used to launch the Virtual Bill Processor from a command line external to the EnergyCAP application itself. For EnergyCAP clients with in-house VB scripting expertise, this functionality may be useful.
! CAUTION: Always back up your EnergyCAP database prior to executing any script!
The usage is:
Vbtsk.exe -d [dsn] -u [user] -p [pwd] -s [startdate] -e [enddate] -f [fix] -r [accountlistfile]
Where:
- [dsn] is the OBDC data source name OR a complete ADO Connection String
- [user] is the system user id (must have permissions to run audits)
- [pwd] is the system password for the above user
- -s [start date] -e [end date] in yyyy-MM-dd format defines the date range in which the bill create date must fall for inclusion
- [fix] indicates rerun failed bills (default is to create new bills)
- [accountlistfile] is the name of the file containing the account:vendor list.
The “-r” parameter is to allow the executable to read a list of account identifiers from a file. The format of the file is to have one line per account, and the format of the line is:
ACCOUNT_CODE_1:VENDORCODEONE
ACCOUNT_CODE_2:VENDORCODEONE
ACCOUNT_CODE_3:VENDORCODETWO
Etc.
The ACCOUNTCODE must be less than or equal to 32 characters in length and the VENDORCODE must be less than or equal to 16 characters in length.
NOTES:
Not all parameters are necessary.
For start/end dates, it is also permissible to specify (in lieu of a date) a value indicating the number of days before the current computer system date. EXAMPLE: -s 30d = thirty days prior to the current system date. The number of days is assumed to be negative.
The COST AVOIDANCE PROCESSOR Task (CAPROCTASK.EXE)
The Cost Avoidance Processor Task runs the Cost Avoidance Processor locally. CAPROCTASK.EXE cannot be run remotely via web; it is typically run as a Window scheduled task.
The usage is:
CAProcTask -d [dsn] -u [user] -p [password] -b -w -t -l [filename]
Where:
- -d is the ODBC datasource name.
- -u is the EnergyCAP system user id of the user
- -p is the EnergyCAP system password of the user
- -b is reprocess baseline.
- -w is No Weather.
- -t is used to limit the processor to two months
- -l is used to name the log file.
A command line example is:
CAProcTask.exe -d myodbc -u myusername -p mypassword -b -t -l C:\temp\rpttsk.log
The CLEANBILL/STAGING Task (RUNTSK.EXE)
The Cleanbill/Staging Task runs Cleanbill and refreshes the staging tables to address timeout issues. RUNTSK.EXE cannot be run by the user; it is used internally by the EnergyCAP application.
The SPLIT BILL PROCESSOR Task (SPLTTSK.EXE)
The Split Bill Processor Task runs the Split Bill Processor locally. SPLTTSK.EXE cannot be run remotely via web; it is typically run as a Window scheduled task.
The usage is:
Splttsk.exe -d [dsn] -u [user] -p [password] -b [batch code] -s [start date] -e [end date] -r [filename]
Where:
- -d is the ODBC datasource name.
- -u is the EnergyCAP system user id of the user
- -p is the EnergyCAP system password of the user
- -b is the batch code.
- -s is the start date in the format yyyy-mm-dd.
- -e is the end date in the format yyyy-mm-dd.
- -r is the name of the file containing the account list.
A command line example is:
splttsk.exe -d myodbc -u myusername -p mypassword -s 2010-01-01 -e 2010-02-01 -r C:\temp\accountlist.txt
The VIRTUAL CHANNEL PROCESSOR Task (VCTSK.EXE)
The Virtual Channel Processor Task runs the Split Bill Processor locally. VCTSK.EXE cannot be run remotely via web; it is typically run as a Window scheduled task.
The usage is:
Vctsk.exe -d [dsn] -u [user] -p [password] -s [start date] -e [end date] -r [filename]
Where:
- -d is the ODBC datasource name.
- -u is the EnergyCAP system user id of the user
- -p is the EnergyCAP system password of the user
- -b is the batch code.
- -s is the start date in the format yyyy-mm-dd.
- -e is the end date in the format yyyy-mm-dd.
- -f is the name of the response file.
A command line example is:
vctsk.exe -d myodbc -u myusername -p mypassword -b mybatchcode -s 2010-01-01 -e 2010-02-01 -f C:\temp\responsefile.txt
