Overview

A customer of yours renewed their contract, but the renewal failed for the particular product and you need assistance for the same.

Solution

Open a Support ticket with the below information:

1. Customer's account number
2. Renewal product code
3. Rate
4. Start date and end date of renewal

After this is completed, Support will inform you about it.

<supportagent>

1. Add the renewal from the CSR interface
2. Update the Rate in the database
3. Insert contract details in the database
4. Add new product into ClientCustomer.RateTransition
5. Add entry into ClientCustomer.ChangeProductRequest

1. Add the renewal from the CSR interface

1. Login to the client's CSR interface.
2. Find the customer using the account number in the top search box.
3. Click Edit Rate icon under Billing Information:

1. On the Rate Management screen, select the Start Date, End Date as provided by the client. Sold Date is the date of the renewal and if not provided by the client, we will use the current date for future date renewals or contract start date if renewal is being back-dated. Search and select the product as provided by the client and click Save:

 

2. Update the Rate in the database

1. Login to the Jump server 10.185.40.141.
2. Open Microsoft SSMS and connect to aes.cons.com.
3. Select New Query in the top bar.
4. Get the RateTransitionId of the newly created entry above. This entry can be identified using the SwitchDate, EndDate, and SoldDate columns from the results of the query:

   use <Client specific Database>;
   select * from ratetransition where custid = (select custid from customer where custno = '<client provided customer number>')

5. Get the RateDetId from RateDetail using the below query:

   use <Client specific Database>;
   select * from ratedetail where ratetransitionid = <from results of previous query> and categoryid = 1 --for Energy rate

6. Update the RateAmt using the below query:

   use <Client specific Database>;
   update ratedetail set rateamt = '<rate provided by client>' where ratedetid = <RateDetId from the results of above query>

 

3. Insert contract details in the database

Find the product using the query:

use <Client specific Database>;
Select * from product where productname like '%<client provided product name>%'

Run the below query to add an entry in the Contract table:

use <Client specific Database>;
Insert into contract (CustID, BeginDate,EndDate,ProductID,ActiveFlag) Values('<Customer ID from the DB>', <'Begin/Start Date as provided by the client'>,<'End Date as provided by the client'>,<Product ID from previous query>,1)

 

4. Add new product into ClientCustomer.RateTransition

1. Get the RateTransitionId of the newly created entry above. This entry can be identified using the SwitchDate, EndDate, and SoldDate columns from the results of the query:

   use <Client specific Database>;
   select * from ratetransition where custid = (select custid from customer where custno = '<client provided customer number>')

2. Insert new product into ClientCustomer.RateTransition using the below query:

   use <Client specific Database>;
   insert into ClientCustomer.RateTransition (RateTransitionID, ProductChangeType, NextRateTransitionDate, DesiredStartDate) values('RateTransitionId from results of previous query','Renewal','@EndDate + 1','@SwitchDate/Start date provided by the client')

 

5. Add entry into ClientCustomer.ChangeProductRequest

insert into ClientCustomer.ChangeProductRequest (ContractId,CustId,ProductId,ProductChangeType,ProductEffectiveDate,
RequestedContractStartDate,ContractTermsInMonths,ActualContractStartDate) values (
'<Recently inserted ContractID from previous query>',
'Custid',
'ProductID from step 3',
'Renewal',
'<SwitchDate/Start date provided by the client>',
'<SwitchDate/Start date provided by the client>',
'<Contract term in months as provided by the client>',
'<SwitchDate/Start date provided by the client>')

</supportagent>