Service Tickets that link to a non-existent customer, site, or system ID will throw errors. Use the script below to find these tickets.
--Invalid Customers
select distinct d.Ticket_Number ,
a.Customer_Number
from AR_customer a, sv_service_ticket d
where d.Customer_Id not in (select Customer_Id from AR_Customer)
and d.Customer_Id <>1
Order by d.Ticket_Number, a.Customer_Number
--Invalid Site
select distinct d.Ticket_Number ,
a.Customer_Number, b.Customer_Site_Id, b.Site_Number
from AR_customer a, AR_customer_site b, sv_service_ticket d
where d.Customer_Site_Id not in (select Customer_Site_Id from AR_Customer_Site)
and d.Customer_Id > 1
and a.Customer_Id > 1
and b.Customer_Site_Id <> 1
and d.Customer_Site_Id <> 1
and d.Customer_Id = a.Customer_Id
and b.Customer_Id = a.Customer_Id
and d.Customer_Site_Id = b.Customer_Site_Id
--Invalid System
select distinct d.Ticket_Number as "Query 330 - SV_Service_Ticket-Customer System Id is Invalid (NO ROWS SHOULD BE RETURNED)",
a.Customer_Number, b.Customer_Site_Id, b.Site_Number, c.Alarm_Account
from AR_customer a, AR_customer_site b, AR_Customer_System c, sv_service_ticket d
where d.Customer_System_Id not in (select Customer_System_Id from AR_Customer_System)
and d.Customer_Id > 1
and a.Customer_Id > 1
and c.Customer_Id > 1
and b.Customer_Site_Id <> 1
and d.Customer_Site_Id <> 1
and c.Customer_System_Id <> 1
and d.Customer_System_Id <> 1
and d.Customer_Id = a.Customer_Id
and b.Customer_Id = a.Customer_Id
and d.Customer_Site_Id = b.Customer_Site_Id
