DROP VIEW IF EXISTS ZeroBalanceInvoices
GO 
Create View ZeroBalanceInvoices
as
SELECT i.CompanyID,
	   r.DocDate, 
	   i.DocType, 
	   i.RefNbr, 
	   r.CuryDocBal as InvoiceBalance,
	   'AR' as Module,
	   COUNT(*) as NumberOfPayments ,
	   MAX(a.AdjgDocDate) as PaidOffDate ,
	   DATEDIFF(day, r.DocDate, MAX(a.AdjgDocDate)) as DaysToPayOff
from ARInvoice i 
left join ARRegister r on i.CompanyID = r.CompanyID and i.DocType = r.DocType and i.RefNbr = r.RefNbr 
left join ARAdjust a on i.CompanyID = a.CompanyID and i.DocType = a.AdjdDocType and i.RefNbr = a.AdjdRefNbr 
where i.DocType = 'INV'
and r.CuryDocBal = 0
group by i.CompanyID,r.DocDate , i.DocType, i.RefNbr, r.CuryDocBal