SELECT (i.InvoiceTotal) as InvoiceTotal, (i.PayerBalance) as PayerBalance, i.Output,i.Seq_Amount, pay.Amount AS paid_amount, DATEDIFF(CURDATE(), i.DateSent) AS age ,pr.Output AS PayerOutputType , pr.Output AS Output_Type, (select sum(amount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id AND deleted != 1 ) as paid, i.invoice_id, (select sum(Total_Eclaim) from #Request.prefix_db_agency#.pSchedules where invoice_id = i.invoice_id AND Total_EClaim > 0 AND Status = 0 limit 1 ) as Total_Eclaim, pr.Pay_ID, pr.Pay_Name, pr.Pay_Contact, pr.Pay_Phone, pr.Pay_Phone AS Contact_Phone_Ext, pay.writeoffamount, pay.Payment_ID, case when pay.WriteOffType ='' then '-' else pay.WriteOffType end as WriteOffType, pay.PaymentDate, case when pay.Comments ='' then '-' else pay.Comments end as Comments, (select sum(writeoffamount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id AND deleted <> 1) as wOff, p.Patient_ID,p.Pt_Last,p.Pt_First, a.F177 AS START, a.F178 AS END , a.F26 AS TYPE, DATE_FORMAT(p.Pt_DOB,'%m/%d/%Y') AS DateOfBirth, i.ContractTotal, 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#.pPayer pr ON pr.Pay_ID = i.patientpayer_id JOIN #Request.prefix_db_agency#.pPatients p ON p.patient_id = i.patient_id JOIN #Request.prefix_db_agency#.pAssessments a ON i.Assmt_ID = a.Assmt_ID JOIN #Request.prefix_db_agency#.Payments pay ON i.invoice_id =pay.invoice_id LEFT OUTER JOIN #Request.prefix_db_agency#.pSchedules s ON i.invoice_id =s.invoice_id AND s.Status = 0 AND s.Missed = 0 WHERE i.Agency_ID = '#session.agencyid#' AND i.Deleted <> 1 AND i.InvoiceTotal > 0 AND pay.PaymentDate >= #CreateODBCDate(FORM.df)# AND pay.PaymentDate <= #CreateODBCDate(FORM.dt)# AND pr.Pay_ID = '#FORM.pr#' AND i.patient_id = '#FORM.pid#' AND (pay.WriteOffType like 'PR%' OR pr.Output != 'Medicare' ) AND (((pay.WriteOffType like '%45%' OR pay.WriteOffType like '%253%') AND pay.WriteOffType like 'CO%') OR pr.Output != 'Medicare' ) AND (pay.WriteOffType like '%70%' OR pr.Output != 'Medicare' ) AND ((pay.WriteOffType like 'PR%' OR pr.Output != 'Medicare' ) OR (((pay.WriteOffType like '%45%' OR pay.WriteOffType like '%253%') AND pay.WriteOffType like 'CO%') OR pr.Output != 'Medicare' ) OR (pay.WriteOffType like '%70%' OR pr.Output != 'Medicare' )) AND (pay.payment_type != 'Reversal Payment' OR pay.payment_type IS NULL) AND (pay.Approve = 0 OR pay.Approve IS NULL) AND p.Status IN (#Request.patient_status#) AND pr.Status = 0 AND a.Status = 0 AND pay.Deleted <> 1 AND s.pat_Invoice_ID = 0 GROUP BY pay.Payment_ID ORDER BY Patient_ID ASC,i.invoice_id ASC, Pay_Name asc SELECT pr.Pay_ID, pr.Pay_Name FROM #Request.prefix_db_agency#.pPayer pr WHERE pr.Loc_ID = '#session.agencyid#' AND pr.Status = '0' ORDER BY pr.Pay_Name SELECT p.Patient_ID, p.Pt_Last, p.Pt_First, p.Status FROM #Request.prefix_db_agency#.pPatients p WHERE p.Loc_ID = '#session.agencyid#' AND Status IN (#Request.patient_status#) Group by p.Patient_ID ORDER BY p.Pt_Last, p.Pt_First Select distinct(Patient_ID) as Patient_ID, Pt_Last,Pt_First from GetI order by Patient_ID asc SELECT CASE WHEN Visits_payments.write_offcode like 'PR%' THEN Visits_payments.write_offamount ELSE 0 END AS first_wamount, CASE WHEN Visits_payments.second_write_offcode like 'PR%' THEN Visits_payments.second_write_offamount ELSE 0 END AS second_wamount, CASE WHEN Visits_payments.third_write_offcode like 'PR%' THEN Visits_payments.third_write_offamount ELSE 0 END AS third_wamount, CASE WHEN Visits_payments.fourth_write_offcode like 'PR%' THEN Visits_payments.fourth_write_offamount ELSE 0 END AS fourth_wamount, CASE WHEN Visits_payments.write_offcode like 'CO%' AND Visits_payments.write_offcodesecond IN ('45','253') THEN Visits_payments.write_offamount ELSE 0 END AS first_wamount, CASE WHEN Visits_payments.second_write_offcode like 'CO%' AND Visits_payments.second_write_offcodesecond IN ('45','253') THEN Visits_payments.second_write_offamount ELSE 0 END AS second_wamount, CASE WHEN Visits_payments.third_write_offcode like 'CO%' AND Visits_payments.third_write_offcodesecond IN ('45','253') THEN Visits_payments.third_write_offamount ELSE 0 END AS third_wamount, CASE WHEN Visits_payments.fourth_write_offcode like 'CO%' AND Visits_payments.fourth_write_offcodesecond IN ('45','253') THEN Visits_payments.fourth_write_offamount ELSE 0 END AS fourth_wamount, CASE WHEN Visits_payments.write_offcode like 'CO%' AND Visits_payments.write_offcodesecond IN ('70') THEN Visits_payments.write_offamount ELSE 0 END AS first_wamount, CASE WHEN Visits_payments.second_write_offcode like 'CO%' AND Visits_payments.second_write_offcodesecond IN ('70') THEN Visits_payments.second_write_offamount ELSE 0 END AS second_wamount, CASE WHEN Visits_payments.third_write_offcode like 'CO%' AND Visits_payments.third_write_offcodesecond IN ('70') THEN Visits_payments.third_write_offamount ELSE 0 END AS third_wamount, CASE WHEN Visits_payments.fourth_write_offcode like 'CO%' AND Visits_payments.fourth_write_offcodesecond IN ('70') THEN Visits_payments.fourth_write_offamount ELSE 0 END AS fourth_wamount, CASE WHEN Visits_payments.write_offcode like 'PR%' OR Visits_payments.write_offcodesecond IN ('45','253','70') THEN Visits_payments.write_offamount ELSE 0 END AS first_wamount, CASE WHEN Visits_payments.second_write_offcode like 'PR%' OR Visits_payments.second_write_offcodesecond IN ('45','253','70') THEN Visits_payments.second_write_offamount ELSE 0 END AS second_wamount, CASE WHEN Visits_payments.third_write_offcode like 'PR%' OR Visits_payments.third_write_offcodesecond IN ('45','253','70') THEN Visits_payments.third_write_offamount ELSE 0 END AS third_wamount, CASE WHEN Visits_payments.fourth_write_offcode like 'PR%' OR Visits_payments.fourth_write_offcodesecond IN ('45','253','70') THEN Visits_payments.fourth_write_offamount ELSE 0 END AS fourth_wamount, Visits_payments.*,Payments.Invoice_ID,Payments.Approve FROM #Request.prefix_db_agency#.Visits_payments JOIN #Request.prefix_db_agency#.Payments ON Payments.Payment_ID = Visits_payments.Payment_ID JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Schedule_ID = Visits_payments.Schedule_ID AND pSchedules.Status = 0 AND Payments.Deleted <> 1 AND pSchedules.pat_Invoice_ID = 0 WHERE Visits_payments.Status = 0 AND Visits_payments.Payment_ID IN (#ValueList(GetI.Payment_ID)#) AND Visits_payments.Payment_ID = 0 AND (Visits_payments.write_offcode like 'PR%' OR Visits_payments.second_write_offcode like 'PR%' OR Visits_payments.third_write_offcode like 'PR%' OR Visits_payments.fourth_write_offcode like 'PR%' ) AND (Visits_payments.write_offcode like 'CO%' AND (Visits_payments.write_offcodesecond IN ('45','253')) OR ( Visits_payments.second_write_offcode like 'CO%' AND Visits_payments.second_write_offcodesecond IN ('45','253')) OR (Visits_payments.third_write_offcode like 'CO%' AND Visits_payments.third_write_offcodesecond IN ('45','253')) OR (Visits_payments.fourth_write_offcode like 'CO%' AND Visits_payments.fourth_write_offcodesecond IN ('45','253')) ) AND ( Visits_payments.write_offcodesecond like '70%' OR Visits_payments.second_write_offcodesecond like '70%' OR Visits_payments.third_write_offcodesecond like '70%' OR Visits_payments.fourth_write_offcodesecond like '70%') AND ((Visits_payments.write_offcode like 'PR%' OR Visits_payments.second_write_offcode like 'PR%' OR Visits_payments.third_write_offcode like 'PR%' OR Visits_payments.fourth_write_offcode like 'PR%' ) OR (Visits_payments.write_offcode like 'CO%' AND (Visits_payments.write_offcodesecond IN ('45','253')) OR ( Visits_payments.second_write_offcode like 'CO%' AND Visits_payments.second_write_offcodesecond IN ('45','253')) OR (Visits_payments.third_write_offcode like 'CO%' AND Visits_payments.third_write_offcodesecond IN ('45','253')) OR (Visits_payments.fourth_write_offcode like 'CO%' AND Visits_payments.fourth_write_offcodesecond IN ('45','253')) ) OR ( Visits_payments.write_offcodesecond like '70%' OR Visits_payments.second_write_offcodesecond like '70%' OR Visits_payments.third_write_offcodesecond like '70%' OR Visits_payments.fourth_write_offcodesecond like '70%')) AND (Visits_payments.write_offamount > 0 OR Visits_payments.second_write_offamount > 0 OR Visits_payments.third_write_offamount > 0 OR Visits_payments.fourth_write_offamount > 0 ) AND (Payments.payment_type != 'Reversal Payment' OR Payments.payment_type IS NULL)
Unbilled Invoices
Back Export Report
Date Format: mm/dd/yyyy
Date Format: mm/dd/yyyy
Select * from GetI where Patient_ID = #FORM.Patient_List# Select * from GetI Select * from GetI
Unbilled Invoices
CO 253 - ' & #DollarFormat(Seq_Amount)# > SELECT * FROM getallpayments WHERE Payment_ID = '#Payment_ID#' AND (first_wamount > 0 OR second_wamount > 0 OR third_wamount > 0 OR fourth_wamount > 0 ) AND (Approve = 0 OR Approve IS NULL)
Patient Name Payer Invoice Certification PR Amount
#Pt_Last#, #Pt_First# #Pay_Name# #invoice_id# #DateFormat(START, "mm/dd/yy")# - #DateFormat(END, "mm/dd/yy")# #DollarFormat(patient_total)# #DollarFormat(W_off)#
#Filter.Pt_Last#, #Filter.Pt_First# #Filter.Pay_Name# #Filter.invoice_id# #DateFormat(Filter.START, "mm/dd/yy")# - #DateFormat(Filter.END, "mm/dd/yy")# #DollarFormat(pt_row_total)#
PR Total: #DollarFormat(tot_woff)#