Payments Detail Report	#url.pname#

SELECT s.Amount as paid, s.writeoffamount as wOff, p.Invoice_Id, pa.Patient_ID, pa.Last_Name_M0040, pa.First_Name_M0040, pa.Status, (select distinct (DATE_FORMAT(Visit_Date,'%Y-%m-%d')) From #Request.prefix_db_agency#.ScheduleItems where Invoice_ID = p.Invoice_Id limit 1) AS Visit_Date, (select distinct (DATE_FORMAT(Visit_Date,'%m/%d')) From #Request.prefix_db_agency#.ScheduleItems where Invoice_ID = p.Invoice_Id limit 1) AS vd, (select distinct(Visit_Type) From #Request.prefix_db_agency#.ScheduleItems where Invoice_ID = p.Invoice_Id limit 1) AS Visit_Type, (select distinct(Total_EClaim) FROM #Request.prefix_db_agency#.ScheduleItems where Invoice_ID = p.Invoice_Id limit 1) AS Total_EClaim, (select distinct(Output_Type) FROM #Request.prefix_db_agency#.ScheduleItems where Invoice_ID = p.Invoice_Id limit 1) AS Output_Type, DATE_FORMAT(pa.DOB_M0066,'%m/%d/%Y') AS DateOfBirth, DATE_FORMAT(p.DateSent,'%Y-%m-%d') AS DateSent, DATE_FORMAT(p.DateSent,'%m/%d') AS ds, p.InvoiceTotal, p.PayerBalance,DATEDIFF(CURDATE(), p.DateSent) AS age, pr.Payer_Id, pr.PayName, pr.Payer_Number FROM #Request.prefix_db_agency#.Payments s join #Request.prefix_db_agency#.Invoices p on s.invoice_id = p.invoice_id join #Request.prefix_db_agency#.patient pa on pa.patient_id = p.patient_id join #Request.prefix_db_agency#.Payer pr on pr.payer_id = p.patientpayer_id WHERE p.Agency_Id = '#session.agencyid#' AND p.Deleted IS NULL OR p.Deleted = 0 AND p.InvoiceTotal > 0 AND p.patientpayer_id = '#url.payid#' AND p.DateSent >= #CreateODBCDate(url.df)# AND p.DateSent <= #CreateODBCDate(url.dt)# AND pa.Patient_ID = '#url.Pid#' ORDER BY pa.Last_Name_M0040, pa.First_Name_M0040, p.Invoice_Id DESC, Visit_Date ASC

Invoice	Date	Visit	Visit Type	Due	Settled
			Write-Off :		#DollarFormat(xOff)#
			Invoice Totals :	#DollarFormat(Replace(invtot, ",", "", "ALL"))#	#DollarFormat(s_invtot)#
			Patient Totals :	#DollarFormat(Replace(pattot, ",", "", "ALL"))#	#DollarFormat(s_pattot)#
Invoice	Date	Visit	Visit Type	Due	Settled
#Invoice_ID#	#ds#	#vd#	#Visit_Type#	#DollarFormat(InvoiceTotal)#	#DollarFormat(paid)#
			GRAND TOTALS :	#DollarFormat(tot_due)#	#DollarFormat(pamount)#