EDAMS Billing and Customer Information
Frequently Asked Questions
July 2010
Run the following query :
1. select * from PROCESS_PROGRESS
Find a record that has PROCESS_NAME = null
2.Get the cycle_id
select CYCLE_ID , * from SUM_BCYC where BILLGROUP = '10' and CYCLE_CLSD = 0
4. Reverse step 3
5. Run manually to see full error.
EXECUTE [database name_Edams]..[Billing_Steps.InvoicesForConsumption] [cycle_id], [process_id],'HCS'
Back to Top
Run the following query :
--- Query to list receipts sessions posted for a
--- specific period, and receipt recorded to customer transactions
select SESSION_NO,SESS_CHSR, SESS_DATE, FROM_RCPT, TO_RCPT,
sum(mr.AMOUNT) as RECEIPTS_RECORDED, sum((ft.AMOUNT *-1)) as TRANSACTIONS_RECORDED
from mreceipt mr , f_trans ft, RECEIPTING_SESSIONS rs
where mr.custkey = ft.custkey
and mr.stamp_date = ft.effect_dte
and mr.trns_type = ft.trns_type
and mr.trns_stype = ft.trns_stype
and mr.receipt_no = ft.documnt_no
and mr.custkey = ft.custkey
and mr.rcpt_cnsld <> 1
and mr.SESSION_NO = rs.SESS_ID
and rs.SESS_DATE between '2008-09-01' and '2008-09-30' /* Enter period here for which you want the summary */ group by SESSION_NO,SESS_CHSR, SESS_DATE, FROM_RCPT, TO_RCPT
Back to Top
Run the following query :
select a.CUSTKEY, a.CL_BLNCE - isnull(b.AMOUNT,0) as BALANCE_BEFORE_INTEREST,
isnull(b.AMOUNT,0) as INTEREST_CHARGED, a.CL_BLNCE
from F_STATM a left outer join F_TRANS b
on a.CUSTKEY = b.CUSTKEY
and a.STATM_NO = b.STATM_NO
and b.TRNS_TYPE = 50
and b.TRNS_STYPE = 12
where a.BILNG_DATE = '2008-07-09'
and a.SUBSTM_NO = 1
Back to Top
: Run the following query :
----
---- Generation query works as follows.
----
/*
SELECT MAX(ANSWER) ANSWER
FROM ( SELECT MAX(PROP_REF) ANSWER
FROM PROP_DTL
WHERE TOWNSHIP_ID = 1
AND PROP_REF > '001/00100/000'
AND PROP_REF < '001/99999/000' UNION
SELECT MAX(PROP_REF)
FROM CS_APPLICATIONS
WHERE TOWNSHIP_ID = 1
AND PROP_REF > '001/00100/000'
AND PROP_REF < '001/99999/000' ) TBL
*/
---
--- Backup tables
---
select * into KITWE_ARCHIVE..BAK_PROP_DTL_PROP_REF_CHANGE from PROP_DTL
select * into KITWE_ARCHIVE..BAK_CS_APPLICATIONS_PROP_REF_CHANGE from CS_APPLICATIONS
---
--- Put undescrores in front of wrongly formated Prop-refs.
--- (see output first) [0123456789] means a single character which is a number so we have 000/00000/000 = 13
---
select PROP_REF, replicate('_',20 - len(PROP_REF)) + PROP_REF , len(PROP_REF)
from PROP_DTL
where not (PROP_REF like '[0123456789][0123456789][0123456789]/[0123456789][0123456789][0123456789][0123456789][0123456789]/[0123456789][0123456789][0123456789]')
and PROP_REF <> 'NEW'
select PROP_REF, replicate('_',20 - len(PROP_REF)) + PROP_REF , len(PROP_REF)
from CS_APPLICATIONS
where not (PROP_REF like '[0123456789][0123456789][0123456789]/[0123456789][0123456789][0123456789][0123456789][0123456789]/[0123456789][0123456789][0123456789]')
and PROP_REF <> 'NEW'
---
--- Do Update
---
update PROP_DTL
set PROP_REF = replicate('_',20 - len(PROP_REF)) + PROP_REF
where not (PROP_REF like '[0123456789][0123456789][0123456789]/[0123456789][0123456789][0123456789][0123456789][0123456789]/[0123456789][0123456789][0123456789]')
and PROP_REF <> 'NEW'
update CS_APPLICATIONS
set PROP_REF = replicate('_',20 - len(PROP_REF)) + PROP_REF
where not (PROP_REF like '[0123456789][0123456789][0123456789]/[0123456789][0123456789][0123456789][0123456789][0123456789]/[0123456789][0123456789][0123456789]')
and PROP_REF <> 'NEW'
Back to Top
Run the following query :
select * from vwF_STATM a
where BILNG_DATE = '2008-07-09'
and SUBSTM_NO = 1
and (AGE_180 <> 0 or AGE_1YR <> 0 or AGE_2YR <> 0 or
AGE_3YR <> 0 or AGE_4YR <> 0 or AGE_5YR <> 0)
Back to Top
a. For Water Connections :
i. For Water Connections we use no. of flats from Connection Details (Conn_Dtl)
ii. If number of flats is Zero in connections details we inherit the no. of flats from Property (Prop_dtl).
i.e if No of flats in conn_dtl = 0 then use No of flats from Prop_dtl....
b. Sewer Connections
i. Sum of No of flats for all connections of property is linked to first sewer connection only from SCONN_DTL
ii. If No. Of flats in PROP_DTL greater than Sum of No of flats (1 above). Then inherit no_of flats from PROP_DTL and not use SCONN_DTL sum.
i.e if Sum of flats at sewer connection level less than no of flats at Property level then use No of flats from Prop_dtl.
Consumption Charges: No of flats is used to apportion consumption to the no of flats on the connection/property
Regular Charges: No of flats is used to charge the min charge to each flat.
Back to Top
This function exists in SA -- System Parameters --> Allow Application Duration editing.
Back to Top
1 Setting up HHU for Import/Export of Walk Route Data.
1.1 All setup can be done through EDAMS Billing.
1.2 Enter Meter Reading Module, and make sure the following settings have been set correctly.
1.2.1 Make sure hand held unit is registered in “Meter Books”.
1.2.2 Define the File format to be used under “Walk Route – file format”. Appendix 1 (at end of this document) is a full typical file format applied. The sections marked Returned = Yes are values returned from the HHU to EDAMS Billing.
File Format
The file format is of fixed field length format.
NB!! It is important that the key fields Meter Book (Book_No), Walk Day (WALK_NO) and Walk Sequence (SEQUNCE_NO) (see figure below).
At least the Current Reading and Reading Date should be supplied back (see figure below).
See Appendix 1 for full definition
1.2.3 In “General” the settings must be as follows
Link Paramaters – “-P1,28800” , where P1 designates the serial port on which the hand held is connected, and the speed of the connection. (Passed to supplied batch files for processing)
Directories, define the directories where the import and export files will be created.
1.2.4 Make sure the HHU is registered in ‘Register Unit’.
1.2.5 All communication to setup the HHU is then performed through “Initialise Unit”. Make sure the HHU is in “Transfer Data” mode.
• Select the Unit to be set.
• Select “Set Unit id.” to register on the HHU the Unit id.
• Set Date and Time.
• If needed the software can be reloaded using “Refresh Program”.
• The HHU can be rebooted using “Reboot Hand Held”, this will replace all software on the HHU including booting software and Meter Reading Software and will also reboot the HHU. (This function will not format the HHU) 2 Uploading and Downloading Meter Reading Data 2.1 Select the correct Billing Group 2.2 Select “Hand Held Unit Processing”
2 Uploading and Downloading Meter Reading Data
2.1 Select the correct Billing Group
2.2 Select “Hand Held Unit Processing”
2.3 Select the Walk Route to be imported into the HHU. To start the transferring process press button labelled 1, see above. (To export from the HHU the button labelled 2 applies)
2.4 The following steps will follow
Before the following step, make sure the HHU is in the cradle and in “Transfer Data” mode. At this point the file to be transferred will be found in the previously defined import/export folder (and supplied batch files will be invoked to communicate these files with HHU).
Please refer to the relevant documentation for using the HHU Meter Reading Software. Files exported from EDAMS have the extension .DLD (Download) Files to be imported to EDAMS have the extension .ULD (Upload)
3 Batch files for communication with hand-held unit.
3.1 Place the supplied sample batch files under the folder ‘c:\program files\edams\billing\hheld’, create this folder if it does not exist.
3.2 These batch files are invoked within EDAMS B&CI to perform communication tasks between EDAMS B&CI and Handheld unit (HHU)
3.3 [For developers] Edit the batch files and configure to handle communication with specific handheld unit you are using. Expected parameters are documented in the batch files.
3.4 Batch files
3.4.1 hhwrite.bat invoked to send exported data file to unit (see 2.1 above)
3.4.2 hhread.bat invoked to receive data file from unit (see 2.1 above)
3.4.3 hhboot.bat used to boot the units software (see 1.2.5 above)
3.4.4 hhrefresh.bat used to refresh the units software (see 1.2.5 above)
3.4.5 hhsetid.bat used to set the unit id (see 1.2.5 above)
3.4.6 hhsettime.bat used to synchronise the current date on the hhu (see 1.2.5 above)
Appendix 1 Sample Export/Import file layout
Cont.
Cont.
Back to Top
The vbs file Export_Station_Receipts.vbs can be used to export Station Receipt files.
The files are exported to a specified directory and zipped using the file name of receipts exported as well as the date of export.
You have the option to send each exported file by e-mail if a SMTP server is available on the network or to send the file to a FTP server.
Installation
-------------
1.Place the file in a folder on the Station Server in the folder "C:\Edams\Zipper\"
2.Also place the accompanied file blat.exe in the same folder.
3.Edit the Export_Station_Receipts.vbs file and modify the following Settings (see below)
4.Use Windows Scheduler to schedule a daily export task of the receipts by running the Export_Station_Receipts.vbs.
Settings::
'---------------------------------------
'Station's Database login details
'---------------------------------------
strDBServer = "DAWAS-STATION\SQL2005" --- SQL database name
strProject = "DAWAS" --- Project identifier
strUser = "sa" --- User
strPassword = "hcs" --- Password
'------------------------------------------------
'Paths for files
'------------------------------------------------
strExportPath = "C:\Edams\DailyExportOfReceipts\" --- Folder where exported files to be kept.
strLogPath = "C:\Edams\Log\" --- Path where logs to be kept
strZipperPath = "C:\Edams\Zipper\" --- Path where the zip 7za.exe and Blat.exe are to be found
'---------------------------------------
'For sending exported files by E-mail
'---------------------------------------
strEmail = "n" --- Send exported file by E-mail y to switch e-mailing on
strSender = "Market" --- Sender, to appear in E-mail (suggest using name of station)
strRecipient = "johnm@edams.com" --- Receipient of the E-mail
strMailServer = "edamsnet" --- SMTP Server to be used to send e-mail.
'-------------------------------------------
'For sending exported files to a FTP server
'-------------------------------------------
strFTP = "y" --- Send exported file to FTP site y to send
strFTPSite = "edamsnet" --- FTP site ip
strFTPUser = "User1" --- FTP User
strFTPPassword = "Password1" --- FTP Password
strFTPPath = "test" --- Subfolder on Server to place file
Back to Top
Use the following query :
select BILLGROUP, CUSTKEY,
PROP_REF + '/' + cast(CONN_NO as nvarchar) as CONNECTION_REF, METER_REF,
case when CON_STATUS = 1 then 'Connected Meter Installed'
when CON_STATUS = 2 then 'Disconnected Meter Installed'
end as STATUS
from PROP_DTL pd, CONN_DTL cd, METR_DTL md, CUST_DTL cu
where pd.PROPERTY_ID = cd.PROPERTY_ID
and md.METER_ID = cd.METER_ID
and pd.BILL_CUSTKEY = cu.CUSTKEY
and pd.SRV1_ALCTO <> -1
and cd.CON_STATUS in (1,2)
order by BILLGROUP, CUSTKEY, PROP_REF
Back to Top
When we tried to change the billing group of a customer, it gives us messages as shown in the screen shots below.
When positioning a customer in a walk route, i get the above message, then finally it give s me the screen shot below,
Most likely the problem is that customer walk routes sequences were messed up so the system was unable to place the customer to the new customer walk route. After running the Customer walk routes re-sequencing from system maintenance the problem should be resolved.
In order to do so go to system maintenanceàdata correctionsàrebuild tablesàre-sequence customer walk routes
Back to Top
After running an update we cannot open the EDAMS Billing software. When we try to open the application we get the following error.
Also the system hangs when opening the application...
This error can appear when there is a problem with the loader. Please try to execute edams_billing.exe manually from its folder. It should be located in a folder similar to C:\program files\edams(43)\bin\ edams_billing.exe.
Back to Top
Step 2 is running fine but we can't input meter readings because of the access violation error we are getting each time we click on the Processing of meter readings per WR button.
Run step 2 again for these billing groups as normal. You will have to do the following:
1. Start new cycles for each billing group - as these are new.
2. Run step 2 to create the MHHREADINGS_XXX table and populate it
Back to Top
There is a problem with step 2 crashing as below.
This error message appears when you have installed only Service Pack 2 on SQL Server 2000. Please install Service Pack 4.
Back to Top