SELECT (i.InvoiceTotal) as InvoiceTotal, (i.PayerBalance) as PayerBalance, i.Output,i.Seq_Amount, pay.Amount AS paid_amount, i.Seq_code,pay.Pay_Seq_Amount, (select sum(amount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id AND deleted != 1 ) as paid, DATEDIFF(CURDATE(), i.DateSent) AS age ,pr.Output AS PayerOutputType , pr.Output AS Output_Type, pr.Pay_frequency, 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, p.Status, CASE WHEN p.Status = 0 THEN 'Active' ELSE 'Inactive' END AS pat_status, a.F177 AS START, a.F178 AS END , a.F26 AS TYPE, i.ContractTotal, i.LUPA_contract, 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_old, 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) AS AR_total, DATE_FORMAT(p.Pt_DOB,'%m/%d/%Y') AS DateOfBirth 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 LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments a ON i.Assmt_ID = a.Assmt_ID AND a.Status = 0 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(URL.df)# AND pay.PaymentDate <= #CreateODBCDate(URL.dt)# AND pr.Pay_ID = '#URL.pr#' AND i.patient_id = '#URL.pid#' AND (pay.WriteOffType like 'PR%' OR (pr.Output = 'Medicare' AND pr.Pay_frequency = 'Episodic' AND REPLACE(REPLACE(LOWER(pay.WriteOffType),'-',''),' ','') = 'writeoff') OR (pr.Output != 'Medicare' AND EXISTS ( SELECT 1 FROM #Request.prefix_db_agency#.Visits_payments vp WHERE vp.Payment_ID = pay.Payment_ID AND vp.Status = 0 AND IFNULL(vp.adjust_contract_balance,'No') = 'Yes' ))) AND (((pay.WriteOffType like '%45%' OR pay.WriteOffType like '%253%' OR pay.WriteOffType like '%29%') 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' AND pr.Pay_frequency = 'Episodic' AND REPLACE(REPLACE(LOWER(pay.WriteOffType),'-',''),' ','') = 'writeoff') OR (pr.Output != 'Medicare' AND EXISTS ( SELECT 1 FROM #Request.prefix_db_agency#.Visits_payments vp WHERE vp.Payment_ID = pay.Payment_ID AND vp.Status = 0 AND IFNULL(vp.adjust_contract_balance,'No') = 'Yes' )) ) OR (((pay.WriteOffType like '%45%' OR pay.WriteOffType like '%253%' OR pay.WriteOffType like '%29%') AND pay.WriteOffType like 'CO%') OR pr.Output != 'Medicare' ) OR (pay.WriteOffType like '%70%' OR pr.Output != 'Medicare' )) AND p.Status IN (#Request.patient_status#) AND (pay.payment_type != 'Reversal Payment' OR pay.payment_type IS NULL) AND pr.Status = 0 AND pay.Deleted <> 1 AND pr.Pay_ID = i.patientpayer_id AND pay.Payment_ID IN (#session.export_payments_id#) AND pay.Payment_ID IN (0) GROUP BY pay.Payment_ID ORDER BY Patient_ID ASC,i.invoice_id ASC, Pay_Name asc SELECT CASE WHEN Visits_payments.write_offcode like 'PR%' OR (pr2.Output != 'Medicare' AND IFNULL(Visits_payments.adjust_contract_balance,'No') = 'Yes') THEN Visits_payments.write_offamount ELSE 0 END AS first_wamount, CASE WHEN Visits_payments.second_write_offcode like 'PR%' OR (pr2.Output != 'Medicare' AND IFNULL(Visits_payments.adjust_contract_balance,'No') = 'Yes') THEN Visits_payments.second_write_offamount ELSE 0 END AS second_wamount, CASE WHEN Visits_payments.third_write_offcode like 'PR%' OR (pr2.Output != 'Medicare' AND IFNULL(Visits_payments.adjust_contract_balance,'No') = 'Yes') THEN Visits_payments.third_write_offamount ELSE 0 END AS third_wamount, CASE WHEN Visits_payments.fourth_write_offcode like 'PR%' OR (pr2.Output != 'Medicare' AND IFNULL(Visits_payments.adjust_contract_balance,'No') = 'Yes') 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','29') 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','29') 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','29') 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','29') 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','29','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','29','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','29','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','29','70') THEN Visits_payments.fourth_write_offamount ELSE 0 END AS fourth_wamount, Visits_payments.*,Payments.Invoice_ID,pr2.Output AS PayerOutputType 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#.Invoices i2 ON i2.Invoice_ID = Payments.Invoice_ID JOIN #Request.prefix_db_agency#.pPayer pr2 ON pr2.Pay_ID = i2.patientpayer_id JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Schedule_ID = Visits_payments.Schedule_ID AND pSchedules.Status = 0 AND Payments.Deleted <> 1 WHERE Visits_payments.Status = 0 AND Visits_payments.Visit_Pay_ID IN (#session.export_visitpayments_id#) AND Visits_payments.Visit_Pay_ID IN (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%' OR (pr2.Output != 'Medicare' AND IFNULL(Visits_payments.adjust_contract_balance,'No') = 'Yes') ) AND (Visits_payments.write_offcode like 'CO%' AND (Visits_payments.write_offcodesecond IN ('45','253','29')) OR ( Visits_payments.second_write_offcode like 'CO%' AND Visits_payments.second_write_offcodesecond IN ('45','253','29')) OR (Visits_payments.third_write_offcode like 'CO%' AND Visits_payments.third_write_offcodesecond IN ('45','253','29')) OR (Visits_payments.fourth_write_offcode like 'CO%' AND Visits_payments.fourth_write_offcodesecond IN ('45','253','29')) ) 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','29')) OR ( Visits_payments.second_write_offcode like 'CO%' AND Visits_payments.second_write_offcodesecond IN ('45','253','29')) OR (Visits_payments.third_write_offcode like 'CO%' AND Visits_payments.third_write_offcodesecond IN ('45','253','29')) OR (Visits_payments.fourth_write_offcode like 'CO%' AND Visits_payments.fourth_write_offcodesecond IN ('45','253','29')) ) 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)
Export File
Back
AND WriteOffType NEQ '0.00'> SELECT * FROM getallpayments WHERE Invoice_ID = '#gets.Invoice_ID#' AND (first_wamount > 0 OR second_wamount > 0 OR third_wamount > 0 OR fourth_wamount > 0 )

» Please Click Here and choose "Save Target As..." to download the Exported data file and save it to your computer.

« Click Here to go back to WriteOff report Report.



No data could be found based on your search criteria.

Please try again.