Accounts Receivable Detail Report	#url.pname#

SELECT p.Patient_ID, p.Last_Name_M0040, p.First_Name_M0040, DATE_FORMAT(p.DOB_M0066,'%m/%d/%Y') AS DateOfBirth ,DATE_FORMAT(si.Visit_Date,'%Y-%m-%d') AS Visit_Date, DATE_FORMAT(si.Visit_Date,'%m/%d') AS vd ,si.Visit_Type, si.Total_EClaim, si.Output_Type ,i.Invoice_Id, DATE_FORMAT(i.DateSent,'%Y-%m-%d') AS DateSent, DATE_FORMAT(i.DateSent,'%m/%d') AS ds, i.InvoiceTotal, i.PayerBalance ,DATEDIFF(CURDATE(), i.DateSent) AS age ,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 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.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 i.patientpayer_id = '#url.payid#' AND i.DateSent >= #CreateODBCDate(url.df)# AND i.DateSent <= #CreateODBCDate(url.dt)# 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 p.Patient_ID = '#url.Pid#' AND pr.payer_id = i.patientpayer_id ORDER BY p.Last_Name_M0040, p.First_Name_M0040, i.Invoice_Id DESC, Visit_Date ASC   DOB: #DateOfBirth# Print--->   DOB: #DateOfBirth# Print--->

Invoice	Date	Visit	Visit Type	Due	Settled	Current	30+	60+	90+	120+
			Write-Off :		#DollarFormat(xOff)#
			#PayName# Invoice Totals :	#DollarFormat(Replace(yDue, ",", "", "ALL"))#	#DollarFormat(yPaid)#	#DollarFormat(yCurr)#	#DollarFormat(y31)#	#DollarFormat(y61)#	#DollarFormat(y91)#	#DollarFormat(y121)#
			Patient Totals :	#DollarFormat(Replace(gDue, ",", "", "ALL"))#	#DollarFormat(gPaid)#	#DollarFormat(gCurr)#	#DollarFormat(g31)#	#DollarFormat(g61)#	#DollarFormat(g91)#	#DollarFormat(g121)#
Invoice	Date	Visit	Visit Type	Due	Settled	Current	30+	60+	90+	120+
#Invoice_ID#	#ds#	#vd#	#Visit_Type#	#DollarFormat(iDue)#	#DollarFormat(iPaid)#	#DollarFormat(iCurr)#	#DollarFormat(i31)#	#DollarFormat(i61)#	#DollarFormat(i91)#	#DollarFormat(i121)#
			Write-Off :		#DollarFormat(xOff)#
			#GetI.PayName# Invoice Totals :	#DollarFormat(Replace(yDue, ",", "", "ALL"))#	#DollarFormat(yPaid)#	#DollarFormat(yCurr)#	#DollarFormat(y31)#	#DollarFormat(y61)#	#DollarFormat(y91)#	#DollarFormat(y121)#
			Patient Totals :	#DollarFormat(gDue)#	#DollarFormat(gPaid)#	#DollarFormat(gCurr)#	#DollarFormat(g31)#	#DollarFormat(g61)#	#DollarFormat(g91)#	#DollarFormat(g121)#
			GRAND TOTALS :	#DollarFormat(tDue)#	#DollarFormat(tPaid)#	#DollarFormat(tCurr)#	#DollarFormat(t31)#	#DollarFormat(t61)#	#DollarFormat(t91)#	#DollarFormat(t121)#