Overview

You want to update the rate on a customer account and the fixed term or contract Start Date and End Date.

Solution

Please raise a support ticket with us mentioning the customer account number, the target rate on account, Start Date, and End Date values. We will make the changes to the account and inform you.

<supportagent>

Prerequisites:

• Access to the Client database

Steps:

1. Run the select query below to get the most recent RateDetID and RateTransitionID.
Replace the parameter c.custno with the given account.

Select c.CustNo [Stream Account],
rt.RateTransitionID, 
rt.SwitchDate 'Contract StartDate',
rt.EndDate 'Contract EndDate',
r.RateDetID,
r.RateAmt,
r.EffectiveDate 'RateDet EffectiveDate',
r.ExpirationDate 'RateDet ExpirationDate'
from RateDetail r
inner join Customer c on c.RateID = r.RateID
inner join RateTransition rt 
on rt.RateTransitionID = r.RateTransitionId
where c.custno  in
('4001591996')
and r.createdate = 
(select MAX(rd.createdate) 
from RateDetail RD 
join Customer C1 on RD.RateID = C1.RateID
where C1.CustID = C.CustID 
and CategoryID = 1 and Active = 1) 
and r.CategoryID = 1 and r.Active = 1
order by c.custno

 

2. Run the update query below to update the rate on the account.
Replace the parameter RateAmt with the given rate. 
Replace the parameter RateDetID with the RateDetID value fetched in the previous step.

UPDATE RateDetail 
SET RateAmt = 0.07990000 
WHERE RateDetId = 3403747

Alternatively, you can run the update query from the QAT tool, refer to this article.

3. Run the update queries below to update the contract Start Date and End Date in the tables.
Replace the parameters EffectiveDate and SwitchDate with the given Start Date value.
Replace the parameters ExpirationDate and EndDate with the given End Date value.
Replace the parameters RateDetId and RateTransitionID with the corresponding values from step 1.

UPDATE RateDetail 
SET EffectiveDate = '2020-12-13 00:00:00.000', 
ExpirationDate = '2023-11-13 00:00:00.000' 
WHERE RateDetId = 3403747

UPDATE RateTransition 
SET SwitchDate = '2020-12-13 00:00:00.000', 
EndDate = '2023-11-13 00:00:00.000' 
WHERE RateTransitionID = 3336082

Alternatively, you can refer to this article for updating the Start Date and this article for updating the End Date.

</supportagent>