| 12345678910111213141516171819 |
- 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
|
