SELECT (i.InvoiceTotal) as InvoiceTotal, (i.PayerBalance) as PayerBalance, DATEDIFF(CURDATE(), i.DateSent) AS age , i.invoice_id, (select Output_Type from #Request.prefix_db_agency#.ScheduleItems where invoice_id = i.invoice_id AND Total_EClaim > 0 AND Deleted <> 1 limit 1 ) as Output_Type, (select sum(Total_Eclaim) from #Request.prefix_db_agency#.ScheduleItems where invoice_id = i.invoice_id AND Total_EClaim > 0 AND Deleted <> 1 limit 1 ) as Total_Eclaim, pr.Payer_Id, pr.PayName, pr.Payer_Number , (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, p.Patient_ID,p.Last_Name_M0040,p.First_Name_M0040, a.Certification_Start as START, a.Certification_End as END, DATE_FORMAT(p.DOB_M0066,'%m/%d/%Y') AS DateOfBirth FROM #Request.prefix_db_agency#.Invoices i,#Request.prefix_db_agency#.Payer pr, #Request.prefix_db_agency#.patient p, #Request.prefix_db_agency#.Assessment_New a WHERE i.Agency_ID = '#session.agencyid#' AND i.Deleted <> 1 AND i.InvoiceTotal > 0 AND i.DateSent >= #CreateODBCDate(FORM.df)# AND i.DateSent <= #CreateODBCDate(FORM.dt)# AND pr.Payer_ID = '#FORM.pr#' AND ( (i.InvoiceTotal - (select sum(amount + writeoffamount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id) > 0) OR (select sum(amount + writeoffamount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id) is null ) AND p.patient_id = i.patient_id and i.Assessment_Tracker_ID = a.Assessment_Tracker_ID AND i.patient_id = '#FORM.pid#' AND pr.payer_id = i.patientpayer_id ORDER BY Patient_ID asc SELECT pr.Payer_Id, pr.PayName FROM #Request.prefix_db_agency#.Payer pr WHERE pr.Agency_ID = '#session.agencyid#' AND pr.PayerStatus = 'active' ORDER BY pr.PayName Select distinct(Patient_ID) as Patient_ID, Last_Name_M0040,First_Name_M0040 from GetI order by Last_Name_M0040 asc Select * from GetI where Patient_ID = #FORM.Patient_List# Select * from GetI Select * from GetI

Patient #Last_Name_M0040#, #First_Name_M0040#

Certification

Total

Paid

Write-Off

Current

30+

60+

90+

120+

Select * from Filter where Patient_ID = #in_Patient_ID#

#DateFormat(START, "mm/dd/yy")# - #DateFormat(END, "mm/dd/yy")#

#DollarFormat(InvAmt)#

#DollarFormat(AmtPaid)#

#DollarFormat(W_off)#

#DollarFormat(Current)#

#DollarFormat(A30)#

#DollarFormat(A60)#

#DollarFormat(A90)#

#DollarFormat(A120)#

Patient Total :

#DollarFormat(_Due)#

#DollarFormat(_Paid)#

#DollarFormat(_woff)#

#DollarFormat(_Current)#

#DollarFormat(_A30)#

#DollarFormat(_A60)#

#DollarFormat(_A90)#

#DollarFormat(_A120)#

Grand Total :

#DollarFormat(tot_Due)#

#DollarFormat(tot_Paid)#

#DollarFormat(tot_woff)#

#DollarFormat(tot_Current)#

#DollarFormat(tot_A30)#

#DollarFormat(tot_A60)#

#DollarFormat(tot_A90)#

#DollarFormat(tot_A120)#

There are no Outstanding Invoices associated with your Agency