SELECT i.Invoice_Id, i.InvoiceTotal, DATEDIFF(CURDATE(), i.DateSent) AS age ,si.ScheduleItems_Id, si.Output_Type, si.Total_EClaim ,pr.Payer_Id, pr.PayName, pr.Payer_Number ,p.Patient_Id, p.Last_Name_M0040, p.First_Name_M0040 ,(select sum(amount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id) as paid ,(select sum(writeoffamount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id) as wOff FROM #Request.prefix_db_agency#.Invoices i, #Request.prefix_db_agency#.ScheduleItems si, #Request.prefix_db_agency#.Payer pr, #Request.prefix_db_agency#.patient p WHERE i.Agency_Id = '#session.agencyid#' AND (i.Deleted IS NULL OR i.Deleted = 0) AND i.InvoiceTotal > 0 AND i.DateSent >= #CreateODBCDate(form.df)# AND i.DateSent <= #CreateODBCDate(form.dt)# AND ( (i.InvoiceTotal - (select sum(amount + writeoffamount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id) > 0) OR (select sum(amount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id) is null ) AND si.invoice_id = i.invoice_id AND si.assessment_tracker_id = i.assessment_tracker_id AND (si.Deleted IS NULL or si.Deleted = 0) AND si.Total_EClaim > 0 AND p.patient_id = i.patient_id AND pr.payer_id = i.patientpayer_id ORDER BY age DESC, i.Invoice_Id ASC

Accounts Receivable

 Print
ArrayAppend(payers, #getI.Payer_Id#);
Current 30+ 60+ 90+ 120+
#DollarFormat(iCurr)# $0.00 #DollarFormat(i31)# $0.00 #DollarFormat(i61)# $0.00 #DollarFormat(i91)# $0.00 #DollarFormat(i121)# $0.00
Age: #ageTxt#...
Age Invoice Patient Payer $ Amount Type
#getI.age# #getI.Invoice_Id# #getI.Last_Name_M0040#, #getI.First_Name_M0040# #getI.PayName# #DollarFormat(InvAmt)# #getI.Output_Type#
  #DollarFormat(iTotal)#  
There are no Outstanding Invoices for your Agency