Hydro-Comp Enterprises

Netsh firewall set portopening tcp 445 SQLNP ENABLE ALL
Netsh firewall set portopening tcp 1433 SQL_PORT_1433 ENABLE ALL
Netsh firewall set portopening udp 1434 SQLBrowser enable ALL

How do I query to check posted receipt sessions?

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

How do I query to find Penalty charges?

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

How to put underscores infront of invalid property references?

See attached document: Put_underscores_infront_of_wrongly_formated_prop_refs.sql

What are the requirements to migrate from Oracle 9i to Oracle 10g?

1) Oracle10g With at least Patch10.2.0.3 must be installed.

2) Backup Oracle 9i using 9i exp.exe utility from DB Manager.

3) Import to 10g using 10g imp.exe utility from DB Manager

How do I find customers with old debt?

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)

How to correct the EDAMS sequences for tables that use Group Sequences.

See attached document: Add_Sequence_Ranges_for_stations.sql

How do we use the no. of flats flag for charges?

This is how it works:

Water Connections
1. For Water Connections we use no. of flats from Connection Details (Conn_Dtl)
2. If No.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....

Sewer Connections
1.Sum of No of flats for all connections of property is linked to first sewer connection only from SCONN_DTL
2. 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.

Can we set the no of days of the duration of a customer services application?

This function exists in SA -- System Parameters --> Allow Application Duration editing

How do I extract the c2v file from my dongle?

Instructions to extract .c2v file:

1. Plug-in your dongle.
2. Extract attached hasprus.exe executable and run.
3. Select Collect information.

4. Save .c2v file by naming it to be your name .c2v e.g. john_markides.c2v.

My Oracle instances character set is incorrect, how do I fix this?

Your oracle instance's character set configuration is incorrect. Is should be UTF8

Log file shows.

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export server uses UTF8 NCHAR character set (possible ncharset conversion)

1)
First drop all edams objects.
2)
login as sys/manager as sysdba and run the following commands.

SHUTDOWN IMMEDIATE;   --- or NORMAL
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE NATIONAL CHARACTER SET UTF8;
SHUTDOWN IMMEDIATE;    --- OR NORMAL
STARTUP RESTRICT all;
SHUTDOWN IMMEDIATE;    --- OR NORMAL
STARTUP;

How do I query to see all primary keys in the database?

select a.TABLE_NAME,COLUMN_NAME, ORDINAL_POSITION
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS a, INFORMATION_SCHEMA.KEY_COLUMN_USAGE b
where a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE = 'PRIMARY KEY'
order by a.TABLE_NAME, ORDINAL_POSITION

How do I check users by a query that belong to certain security profiles

select d.ORGPOST_NO, d.DESCRIPTION, a.PROF_SID, a.PROF_NAME, b.GRP_SID,
b.GRP_NAME, [User_Name], Initial, surname,
f.*
from SEC_PROFILES a, SEC_GROUPS b, SEC_GROUPS_PROFILE c, ORGPOSTS d, EMPLOYEES e,
SEC_ACES_SID f
where a.PROF_SID = c.PROF_SID
and b.GRP_SID = c.GRP_SID
and d.PROF_SID = a.PROF_SID
and e.ORGPOST_NO = d.ORGPOST_NO
and c.GRP_SID = f.[SID]
order by b.GRP_SID

How can I schedule to export receipts on a daily basis?

See attached document: Scheduled Exporting Of Station Receipts V2.rar

How do I query to find all connections with meters connected?

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

How do I fix any missing sequences?

Identify a good database that has all sequences and run the following query

select 'exec [EDAMS.ADDSEQUENCE] '
+ cast(EDAMS_SEQ_ID as nvarchar)
+ ',''KITWE_EDAMS'',''' + TABLE_NAME + ''',''' + KEY_COL_NAME + ''''
from SILU_EDAMS..EDAMS_SEQ_TABLE a --- Good table..
where not exists (select 1 from EDAMS_SEQ_TABLE b
where a.TABLE_NAME = b.TABLE_NAME
and a.KEY_COL_NAME = b.KEY_COL_NAME )
and exists (select 1 from INFORMATION_SCHEMA.COLUMNS c
where a.TABLE_NAME = c.TABLE_NAME
and c.COLUMN_NAME = a.KEY_COL_NAME)

How do I switch to using EDAMS Windows Authentication?

See attached document: EDAMS - Windows Authentication.doc

Frequently Asked Questions (FAQs)>

How do I connect to my personal sql2005 instance from another PC?