select i.Invoice_Id, i.DateSent, i.MethodSent, i.Comment, i.InvoiceTotal, i.PayerBalance, i.Assessment_Tracker_Id ,i.Internal_Control_number,i.User_defined_Status, i.claim_status, DATEDIFF(CURDATE(), i.DateSent) AS age , p.Patient_Id, p.Status, p.Last_Name_M0040, p.First_Name_M0040, p.Middle_Initial_M0040 , a.Certification_Start AS START, a.Certification_End AS END , a.Assessment_Reason_M0100 AS TYPE, pr.PayName, pr.Payer_Id, pr.Output_Type AS PayerOutputType , (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 , (SELECT Invoice_Id FROM #Request.prefix_db_agency#.Invoices WHERE Assessment_Tracker_Id = i.Assessment_Tracker_Id AND InvoiceTotal = '0.00' and Deleted <> 1 LIMIT 1) AS FinalBilling , (SELECT DATEDIFF(CURDATE(), DateSent) FROM #Request.prefix_db_agency#.Invoices WHERE Assessment_Tracker_Id = i.Assessment_Tracker_Id AND InvoiceTotal = '0.00' and Deleted <> 1 LIMIT 1) AS FinalAge, (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 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 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 > '2016-01-01' 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 pr.payer_id = i.patientpayer_id ORDER BY Last_Name_M0040, First_Name_M0040, i.Invoice_Id
Deleted Invoices & Payments
Back


PATIENT NAME STATUS PAYER DATE BILLED BALANCE PAID WRITE-OFF TOTAL CERT PERIOD CLAIM NUMBER TOB
#Last_Name_M0040#, #First_Name_M0040# #Status# #Left(PayName,8)# #DateFormat(DateSent,"mm/dd/yy")# #DollarFormat(InvAmt - amtPaid - writeOff)# #DollarFormat(Paid)#$0.00 #DollarFormat(writeOff)#$0.00 #DollarFormat(InvAmt )# #DateFormat(START,"mm/dd/yy")#-
#DateFormat(end,"mm/dd/yy")#		 #Internal_Control_number#NIL 32|832|2