If a customer needs an invoice deleted, while this is not recommended, it can be done in special circumstances using the script below:
begin tran
--select min(invoicenumber) InvoiceNumber
--into NewInvoiceNumber
--from aInvoice i
--left join aWorkOrder w on i.InvoiceId = w.InvoiceId
--where w.WorkOrderId is null and (InvoiceDate = '[DesiredInvoiceDate]' and InvoiceTypeId =2)
--delete forte transactions(not needed for unpaid invoices)
delete i
--select *
from afortetransactioninvoice i
where invoiceid in
(select distinct i.InvoiceId
from aInvoice i
left join aWorkOrder w on i.InvoiceId = w.InvoiceId
where w.WorkOrderId is null and (InvoiceDate = '[DesiredInvoiceDate]' and InvoiceTypeId =2))
--delete rmrgeneration
delete i
--select distinct i.*
from aRmrGeneration i
where invoiceid in
(select distinct i.InvoiceId
from aInvoice i
left join aWorkOrder w on i.InvoiceId = w.InvoiceId
where w.WorkOrderId is null and (InvoiceDate = '[DesiredInvoiceDate]' and InvoiceTypeId =2))
--delete asalstaxdetail
delete i
--select distinct i.*
from aSalesTaxDetail i
where invoiceid in
(select distinct i.InvoiceId
from aInvoice i
left join aWorkOrder w on i.InvoiceId = w.InvoiceId
where w.WorkOrderId is null and (InvoiceDate = '[DesiredInvoiceDate]' and InvoiceTypeId =2))
--delete aInvoiceDetail
delete i
--select distinct i.*
from aInvoiceDetail i
where invoiceid in
(select distinct i.InvoiceId
from aInvoice i
left join aWorkOrder w on i.InvoiceId = w.InvoiceId
where w.WorkOrderId is null and (InvoiceDate = '[DesiredInvoiceDate]' and InvoiceTypeId =2))
--Delete aForteTransaction (not needed for unpaid invoices)
delete i
--select distinct i.*
from aForteTransaction i
where RecurringInvoiceId in
(select distinct i.InvoiceId
from aInvoice i
left join aWorkOrder w on i.InvoiceId = w.InvoiceId
where w.WorkOrderId is null and (InvoiceDate = '[DesiredInvoiceDate]' and InvoiceTypeId =2))
--delete aCreditApplied
delete i
--select distinct i.*
from aCreditApplied i
where invoiceid in
(select distinct i.InvoiceId
from aInvoice i
left join aWorkOrder w on i.InvoiceId = w.InvoiceId
where w.WorkOrderId is null and (InvoiceDate = '[DesiredInvoiceDate]' and InvoiceTypeId =2))
--Delete actual Invoices
delete i
--select distinct i.*
from aInvoice i
left join aWorkOrder w on i.InvoiceId = w.InvoiceId
where w.WorkOrderId is null and (InvoiceDate = '[DesiredInvoiceDate]' and InvoiceTypeId =2)
--order by InvoiceDate desc
--update d
--set NextInvoiceNumber = InvoiceNumber
----select *
--from sDealerDefaults d
--full join newinvoicenumber on 1=1
--where InvoiceNumber is not null
--drop table NewInvoiceNumber
--rollback
--commit
--select *
--from rInvoiceType
