Reconciling Recurring and Recurring History in SedonaOffice

05/13/2024 5:38 pm EDT

Whenever a change is made to a customer’s recurring in SedonaOffice, that change is logged in the Recurring History for that customer. The recurring history can also be manually edited. If the recurring history is manually edited, or if recurring is imported into the software, bypassing the history, the actual recurring on the customer account can become out of sync with the totals in the recurring history. The following script can be run to identify customers where the total active/canceled recurring on the account, and the total active/canceled recurring in the recurring history, do not match. The script will identify which specific recurring item on the customer is out of sync. The script will not identify the source of the discrepancy, or correct the discrepancy. Any corrections will need to be done by manually editing the recurring history and/or recurring lines.

--Please Add "//" if you want to run this through the SQL windows in the Sedona Application

If (select object_id('tempdb..#RMR_report','U')) is not null

begin

Drop table #RMR_report;

End;

with AR as (

Select c.customer_id, Customer_Site_Id, Customer_System_Id, Item_Id, Sum(r.Monthly_Amount) [TotalRecurring], Count(*) [RmrCount]

from AR_Customer c with (nolock)

Inner join AR_Customer_Recurring r with (nolock) on r.Customer_Id = c.Customer_Id

where c.customer_id > 1

and Cycle_End_Date <= '1900-01-01'

group by c.customer_id, Customer_Site_Id, Customer_System_Id, Item_Id

Tr as (

Select c.customer_id, Site_Id, Customer_System_Id, Item_Id, Sum(r.RMR_Amount) [TotalTracking]

from AR_Customer c with (nolock)

Inner join AR_RMR_Tracking r with (nolock) on r.Customer_Id = c.Customer_Id

where c.customer_id > 1

group by c.customer_id, Site_Id, Customer_System_Id, Item_Id

)

Select Coalesce (r.customer_id, t.customer_ID) [customer_ID], Coalesce (r.customer_site_id, t.Site_ID) [Site_ID], Coalesce (r.customer_system_id, t.customer_System_ID) [System_ID],

Coalesce (r.Item_id, t.Item_ID) [Item_ID], Isnull(r.rmrCount, 0) [RmrCount], Isnull(r.TotalRecurring, 0) [TotalRmrAmount], isnull(t.TotalTracking, 0) [TrackingAmount]

into #RMR_report

from AR r

Full Join Tr as t on r.Customer_Id = t.Customer_Id

and r.Customer_Site_Id = t.Site_Id

and r.Customer_System_Id = t.Customer_System_Id

and r.Item_Id = t.Item_Id

where Isnull(r.TotalRecurring, 0) <> isnull(t.TotalTracking, 0)

Select customer_number, s.Business_Name, s.Site_Number, case when r.System_Id > 1 then sy.Alarm_Account else 'Site Level RMR' end [System Account], item_code,

r.TotalRmrAmount, r.RmrCount, r.TrackingAmount

from #RMR_report r

inner join AR_Customer c on r.Customer_Id = c.Customer_Id

Inner join ar_customer_site s on s.Customer_site_Id = r.Site_ID

Inner join ar_customer_system sy on sy.customer_system_id = r.System_Id

inner join Ar_item i on i.Item_Id = r.Item_Id

order by customer_number, r.site_id, r.item_id

Was this article helpful?