Overview

You may want to re-calculate adjustments to the invoice amounts of a customer for a certain period in the past, with different rates than were actually applied. This might be for customers who were rolled on to variable-rate plans and now need invoice amounts for fixed-rate plans for certain billing period(s).

Solution

Create a support ticket mentioning the customer's account number or customer ID and the rate schedule comprising of the date ranges during which those rates will be effective and the effective rates for those ranges. Make sure that the rate intervals start on the month boundaries as shown below.

<supportagent>

If the rate schedule intervals shared by the client don't start on month boundaries then get it corrected with the client.

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. Select the client-specific database.
5. Execute the following query to create a temporary table containing the rate schedule. There will be as many select statements as there will be months in the rate schedule, as depicted below for the example suggested above.

   create table #tmpa(dt Date,Rate decimal(15,7),Mon Varchar(20))
   insert into #tmpa (dt,rate,mon)
   -- below select formatted as select 'MM/01/YYYY' ,'RATE' ,MM/YYYY' union all 
   select '03/01/2019' ,'0.611' ,'03/2019' union all 
   select '04/01/2019' ,'0.611' ,'04/2019' union all 
   select '05/01/2019' ,'0.611' ,'05/2019' union all 
   select '06/01/2019' ,'0.611' ,'06/2019' union all 
   select '07/01/2019' ,'0.611' ,'07/2019' union all 
   select '08/01/2019' ,'0.611' ,'08/2019' union all 
   select '09/01/2019' ,'0.611' ,'09/2019' union all 
   select '10/01/2019' ,'0.611' ,'10/2019' union all 
   select '11/01/2019' ,'0.611' ,'11/2019' union all 
   select '12/01/2019' ,'0.611' ,'12/2019' union all 
   select '01/01/2020' ,'0.611' ,'01/2020' union all 
   select '02/01/2020' ,'0.611' ,'02/2020' union all 
   select '03/01/2020' ,'0.519' ,'03/2020' union all 
   select '04/01/2020' ,'0.519' ,'04/2020' union all 
   select '05/01/2020' ,'0.519' ,'05/2020' union all 
   select '06/01/2020' ,'0.519' ,'06/2020' union all 
   select '07/01/2020' ,'0.519' ,'07/2020' union all 
   select '08/01/2020' ,'0.519' ,'08/2020' union all 
   select '09/01/2020' ,'0.519' ,'09/2020' union all 
   select '10/01/2020' ,'0.519' ,'10/2020' union all 
   select '11/01/2020' ,'0.519' ,'11/2020' union all 
   select '12/01/2020' ,'0.519' ,'12/2020' union all 
   select '01/01/2021' ,'0.519' ,'01/2021' union all 
   select '02/01/2021' ,'0.519' ,'02/2021' union all 
   select '03/01/2021' ,'0.519' ,'03/2021'

6. Execute the following query, remembering to substitute either the provided account number or customer ID depending on what the client provided, to generate the revised billing amount

   select c.CustNo,c.CustID,i.InvoiceID,i.InvDate,i.InvAmt,i.ServiceFrom,i.ServiceTo,
   id.CategoryID,id.InvDetDesc,id.InvDetQty,id.Rate as Invoice_Rate,id.InvDetAmt,
   a.Rate as PTC_Rate,(a.Rate-id.Rate) as RateDiff, id.InvDetQty * (a.Rate-id.Rate) as InvAdj
   from Customer c
   join Invoice i on i.CustID = c.CustID
   join InvoiceDetail id on id.InvoiceID = i.InvoiceID
   left join #tmpa a on Concat(DATEPART(month, a.dt), DATEPART(year, a.dt)) = Concat(DATEPART(month, i.InvDate), DATEPART(year, i.InvDate) )
   where 1=1
   and c.CustID=<client-provided customer ID>
   -- and c.CustNo='<client-provided account number>'
   and id.CategoryID=1
   order by i.invdate

7. The InvAdj column in the above query has the adjustment amount for each invoice. Export this result set and share it with the client.

</supportagent>