Overview

You require assistance with contract renewal or changing product or rate class for commercial or residential accounts. Also, you may request to add a commercial product plan, update the contract rate on your system and activate the contract. You want to make sure the new contract will be added to the account.  

 

Solution

Please contact the support team and raise a support ticket (if not already done so). You are requested to provide the Ista# (account number), LDC#, Contract Start Date, Contract End Date, Sold Date, Contract Rate, Terms (in months).
In the case of a single account, you can provide the Product Description. In case of multiple accounts, please provide the Product ID of the target product for each contract/account.

We will apply the contract to the account, send a rate change 814_C transaction (in case of a Rate ready account) and inform you. You can verify that the contract has been added to the account using the steps in the Testing section below.

<supportagent>

Pre-requisites

• Access to the client-specific CSR site

• Access to the Query Automation Tool (QAT)

• Access to the Client (CIS) database

Case I - Single Account 

For a single account, you can add the contract or apply the renewal through the rate transition in CSR.

Case II - Multiple Accounts

For multiple accounts, the update needs to be done in the database.

 

Steps

Case I - Single Account

I. If the product description or product name or product code or rate code is not provided by the client, it can be fetched using the following steps:

A Log into the client-specific CSR site. Search for the given customer account number (or LDC Account Number).
From the resultant information, note the Utility that this account belongs to from the Utility Accounts tab. 

B Next, run a select query to get the LDCCode for this fetched Utility/LDC.
Replace the parameter LDCName with the utility name you fetched in the previous step.

select LDCID, LDCCode, LDCName 
from LDC 
where LDCName = 'Consolidated Edison of New York'

C Next, get the customerTypeID for the given account. Replace the parameter  custNo with the given customer account number.

select customertypeID 
from customer 
where custNo='4001339592'

D Next, get the custType for this account. Replace the parameter customerTypeID with the value fetched in the previous step.

select custType 
from customertype 
where customerTypeID=11

E Next, get the product details corresponding to previously fetched LDCCode and CustType values.

select productCode, termsid, rateid 
from product 
where custType='C' and LDCCode=13

In the query result, note the productCode for the entry with termsID corresponding to the given 'terms' value. For example, termsID is expected to be 15 for a term of 15 months. 
Typically, a single product code is expected to be returned in the result. If this is the case, proceed to step II skipping sub-steps F-H
 
F If multiple product codes are returned in the query result above, run a select query on the terms table to know the corresponding term details.
Replace the parameter termsID with the value noted in the query result above.

Select  termscode,description 
from terms 
where termsid=47;

G If it is not possible to isolate a single product code based on the fetched filter values for termsID, custType and LDCCode, check the rate amount against the product using the steps below:

Run a select query using the rateID values fetched in E above.

select rateAmt,  FixedCapRate 
from ratedetail 
where rateID in (258068, 258069)

In the query result, if the RateAmt is NULL, run the select query below. Replace the parameter rateIndexTypeID with the value for FixedCapRate fetched above.

select IndexRate from RateIndexRange where rateIndexTypeID=286.00000000
order by RateIndexRangeID desc;

This fetched IndexRate is the rate amount.

H If the rate amount from the previous step does not match the given rate amount, it is required to reach out to the client to clarify the product code that should be applied to the account.
Share the fetched product codes from step E and request the client to specify the exact product code.

II. With the fetched (or provided) product code, follow the steps below to apply the product code.

 

1. Log in to the client-specific CSR site. In the Search section on the top-right side of the screen, select Account No from the drop-down, enter the provided account number (Ista#) in the field beside it and click on the search icon. 

2. The customer account information will be displayed in the dashboard. Click on the Edit Rate icon under the Billing Information tab.



3. From the rate transition screen, select the Product that needs to be applied to the account from the displayed list. Use the provided Product Description value for this look-up from the list. 
Use the dates that have been provided by the client for the Start date,  End date, and Sold date. The Start Date provided may be backdated.
Click on the Save option.

4. You can refresh the account, then, go to Activity >> View Rates from the toolbar. Here you will see the rate details for a newly added product.
For Bill Ready, Supplier consolidated, and Dual accounts you will be able to see the rates immediately as the product change does not require a rate change sent out for the above billing methods.

To update the contract rate and activate the contract, follow steps 5-12 below.  
 
5. Run the following query by replacing the parameter c.custNo with the provided Ista# or account number. 

select c.custID from customer c where c.custNo in (‘4001286192’); 

6. Note the CustID value from the result of the previous query and use it as the parameter to run the following query.

 Select top 1 * from RateTransition where CustID=586957;

7. Note the RateTransitionID from the result of the previous query and use it as the parameter to run the following query. 

Select rd.RateDetID, rd. * from RateDetail rd where RateTransitionID = 2345;

 

 Note the RateDetID from the result of the previous query.

8. The next step is to update the rate amount to the rate given by the client. 
Log in to the Query Automation Tool (QAT).
Click on Query Management.
Enter 113 in the ID field.
Click on Search. When the search result displays, click on the link under Query Name. 

9. When the query loads, select the Database name specific to the client. 

10. Populate the parameter values by clicking on the Value field and entering the data. Click outside the field to save it.
For the RateAmt parameter, enter the client-provided value for the account.
For the RateDetID parameter, enter the RateDetID value from the query result in step 7.

11. Click on Run Query.

12. Check the message for the expected number of rows being affected in the SQL Execution Result section. 
Then click on the Commit Transaction option (this button should be displayed as active after the Run Query button is clicked).

13. Check the Billing Type for the account to know if it is Rate ready or not.
Run the select query below. 

Select Custno, BT.Description 
From  Customer C
Join CustomerAdditionalinfo Cai on Cai.custid=C.custid
Join Billingtype Bt on Bt.billingtypeid=Cai.billingtypeid
where c.custNo='4001339598'

The BT.Description column in the query result will display the value "Rate Ready" for a rate-ready account. If found so, send a rate change transaction using the steps in the article Triggering-a-Change-Transaction-814-C-via-CSR.

 

Case II - Multiple Accounts

1. Log in to the QAT.

Click on Query Management.

Enter 871 in the ID field.

Click on Search. When the search result displays, click on the link under Query Name. 

2. When the query loads, select the Database name specific to the client. 

3. Create an Excel file with the headers CustNo, enddate, ProductID, RateAmt, SoldDate, switchdate.
Populate using the provided values: CustNo is mapped to Ista# (account number), enddate to Contract End Date, RateAmt to Contract Rate, SoldDate to Sold Date, ProductID to Product ID, switchdate to Contract Start Date. 
The Start Date provided may be backdated.

4. Click on Choose option to select and upload this Excel file. This is to load the parameter values in the query. 

5. Click on Run Query.

Check the message for the expected number of rows being affected in the SQL Execution Result section. 
Then click on the Commit Transaction option (this button should be displayed as active after the Run Query button is clicked).

6. After adding the Renewals using the above steps, you need to get the newly added ratedetid for the products.  Execute steps 5-7 from the section Case I - Single Account to get the RateDetID value for the accounts.

7. The next step is to update the rate amt for the accounts. Search for Query ID 113 in Query Management. When the query loads, select the Database name specific to the client. 

8. Create an Excel file with the headers RateAmt, RateDetID.
Populate using the provided values. For the RateAmt parameter, enter the client-provided value for each account. For the RateDetID parameter, enter the RateDetID value from the query result in step 6 above. Now, repeat steps 4 and 5 from this section.

9. Check the Billing Type for each account to know if it is Rate ready or not.
Run the select query below. 

Select Custno, BT.Description 
From  Customer C
Join CustomerAdditionalinfo Cai on Cai.custid=C.custid
Join Billingtype Bt on Bt.billingtypeid=Cai.billingtypeid
where c.custNo in ('4001339598','4001339599')

The BT.Description column in the query result will display the value "Rate Ready" for a rate-ready account. If found so, send a rate change transaction using the steps in the article Triggering-a-Change-Transaction-814-C-via-CSR.

</supportagent>

Testing

Check if a particular product/rate code and the rate are applied to the customer account to confirm contract renewal.

• Log in to the CSR site. Search using an Account Number.
• In the resultant information, under the tab Customer Information -> Billing section -> Rate Code, check if the displayed Rate Code is as per the applied Rate Code (Product Description).
• Click on the Edit Rate icon.
• In the resultant Rate Management screen, check if the updated Rate/Rate Amount is displayed in the Amount column.
  If both the values are as per the applied values, it can be confirmed that contract addition/renewal is successful for the account.