SELECT i.Invoice_Id, i.DateSent, i.MethodSent, i.InvoiceTotal, i.PayerBalance, DATEDIFF(CURDATE(), i.DateSent) AS age,i.Internal_Control_Number ,p.Pt_Last, p.Pt_First, p.Pt_Middle,i.Seq_Amount, i.Seq_code,i.outlier ,pr.Pay_Name, pr.Pay_ID,pr.Output,pay.EFT_No ,(select sum(amount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id and deleted != 1 AND payment_type != 'Reversal Payment') as paid ,(select sum(writeoffamount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id and deleted != 1 AND WriteOffType not like 'PR%' AND payment_type != 'Reversal Payment') as wOff, ROUND(IFNULL(i.ContractTotal,0),2) AS AR_total_new, CASE WHEN LUPA_threshold = 1 THEN LUPA_contract ELSE ROUND(IFNULL(( CASE WHEN pr.Output = 'Medicare' THEN (CASE WHEN DATEDIFF(s.Visit_Date,a.F177) < 30 THEN a.F299 ELSE a.F298 END) ELSE i.ContractTotal END),0),2) END AS AR_total FROM #Request.prefix_db_agency#.Invoices i JOIN #Request.prefix_db_agency#.pSchedules s on s.Invoice_ID = i.Invoice_ID AND i.Assmt_ID = s.Assmt_ID JOIN #Request.prefix_db_agency#.pAssessments a on s.Assmt_ID = a.Assmt_ID AND i.Assmt_ID = a.Assmt_ID JOIN #Request.prefix_db_agency#.pPatients p on p.patient_id = s.patient_id AND i.patient_id = p.patient_id JOIN #Request.prefix_db_agency#.pPayer pr on i.PatientPayer_ID = pr.Pay_ID LEFT OUTER JOIN #Request.prefix_db_agency#.Payments pay ON i.invoice_id =pay.invoice_id AND pay.Payment_ID = '#url.Paymt_ID#' WHERE i.invoice_id = '#url.Invid#' AND i.Agency_ID = '#session.agencyid#' AND p.patient_id = i.patient_id AND pr.Pay_ID = i.patientpayer_id AND i.Deleted <> 1 AND pr.status = 0 AND p.Status IN (#Request.patient_status#) AND a.status = 0 AND s.status = 0 SELECT pSchedules.Schedule_ID, pSchedules.Total_Units, pSchedules.Invoice_ID,pSchedules.Visit_Date,pSchedules.Skill,Payments.WriteOffAmount, pSchedules.Total_Eclaim,Invoices.patientpayer_id, pRevenue_Code.Measure, pRevenue_Code.EClaim_Price , vp.Visit_Pay_ID, vp.Schedule_ID as vp_Schedule_ID ,vp.Payment_ID, IFNULL(vp.payment,0) AS payment, IFNULL(vp.write_offamount,0) AS first_wamount,IFNULL(vp.second_write_offamount,0) AS second_wamount, IFNULL(vp.third_write_offamount,0) AS third_wamount, IFNULL(vp.fourth_write_offamount,0) AS fourth_wamount, vp.write_offcode, vp.write_offcodesecond, vp.second_write_offcode,vp.second_write_offcodesecond, vp.third_write_offcode, vp.third_write_offcodesecond, vp.fourth_write_offcode, vp.fourth_write_offcodesecond FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.Invoices ON Invoices.Invoice_Id = pSchedules.Invoice_ID JOIN #Request.prefix_db_agency#.pRevenue_Code ON pRevenue_Code.Rev_CodeID = pSchedules.Rev_ID LEFT OUTER JOIN #Request.prefix_db_agency#.Visits_payments vp ON vp.Schedule_ID = pSchedules.Schedule_ID AND vp.Status = 0 JOIN #Request.prefix_db_agency#.Payments ON vp.Payment_ID = Payments.Payment_ID AND Payments.Deleted = 0 WHERE pSchedules.Status = 0 AND Invoices.Deleted <> 1 AND pSchedules.Invoice_ID = '#url.Invid#' AND Payments.Payment_ID = '#url.Paymt_ID#' GROUP BY vp.Visit_Pay_ID
Unbilled Invoice
#GetI.Pt_First# #GetI.Pt_Last#
#GetI.Pay_Name#
#GetI.Internal_Control_Number#
#GetI.EFT_No#
#GetI.Invoice_ID#
"> "> "> ">
Service Date Decription Charge Payment Adjustment Amount Adjustment Reason Payer Payment Due
#DateFormat(Visit_Date, 'mm/dd/yy')# #Skill# #DollarFormat(Total_Eclaim)# #DollarFormat(payment)# #DollarFormat(pt_row_total)# #adjust_reason# #GetI.Pay_Name# #DollarFormat(pt_row_total)#
Generate Invoice Total #DollarFormat(total_pt_row_total)#