SELECT (s.Payment_ID) as Payment_ID, IFNULL(s.Payment_Lock,0) AS PaymentLock, (select sum(amount) from #Request.prefix_db_agency#.Payments where invoice_id = p.invoice_id) as paid, (s.WriteOffAmount) as WriteOffAmount, s.PatientPayer_ID ,(select sum(writeoffamount) from #Request.prefix_db_agency#.Payments where invoice_id = p.invoice_id) as wOff, p.PayerBalance, pa.Patient_ID, pa.Last_Name_M0040, a.Certification_Start,a.Certification_End, pa.First_Name_M0040, pa.Status, DATE_FORMAT(pa.DOB_M0066,'%m/%d/%Y') AS DateOfBirth, p.Invoice_Id, DATE_FORMAT(p.DateSent,'%Y-%m-%d') AS DateSent, MAX(DATE_FORMAT(s.PaymentDate,'%Y-%m-%d')) AS PaymentDate, DATE_FORMAT(p.DateSent,'%m/%d') AS ds, (p.InvoiceTotal) as InvoiceTotal, si.Total_EClaim,si.EClaimed_Final, si.Output_Type, p.PayerBalance, pr.Payer_Id, pr.PayName, pr.Payer_Number, (SELECT Invoice_Id FROM #Request.prefix_db_agency#.Invoices WHERE Assessment_Tracker_Id = p.Assessment_Tracker_Id AND InvoiceTotal = '0.00' and Deleted <> 1 LIMIT 1) AS FinalBilling from #Request.prefix_db_agency#.Payments s left outer join #Request.prefix_db_agency#.ScheduleItems si on s.Invoice_ID = si.Invoice_ID left outer join #Request.prefix_db_agency#.Invoices p on p.Invoice_ID = si.Invoice_ID left outer join #Request.prefix_db_agency#.patient pa on pa.patient_id = p.patient_id left outer join #Request.prefix_db_agency#.Payer pr on pr.payer_id = p.patientpayer_id left outer join #Request.prefix_db_agency#.Assessment_New a on si.Assessment_ID = a.Assessment_ID WHERE p.Agency_Id = '#session.agencyid#' AND p.Deleted IS NULL OR p.Deleted = 0 AND si.Payer_id = '#url.pr#' AND p.DateSent >= #CreateODBCDate(url.df)# AND p.DateSent <= #CreateODBCDate(url.dt)# AND Amount > 0 AND si.Agency_ID = '#session.AgencyID#' AND pa.Patient_ID = '#url.Pid#' AND p.Invoice_Id='#url.Invid#' And s.Payment_ID= '#url.prid#' Group BY p.Invoice_Id ORDER BY Last_Name_M0040, First_Name_M0040 ASC
Update Payment details
Patient #Last_Name_M0040# #First_Name_M0040#
Invoice Id #invoice_id#
PaymentDate
Certification #DateFormat(Certification_Start,'mm/dd/yyyy')#-
#DateFormat(Certification_End,'mm/dd/yyyy')#
Due
Paid
Write-Off
Lock checked="checked" /> Lock