UPDATE #Request.prefix_db_agency#.Invoices SET User_defined_Status = '#url.sta#' ,Record_Modified_By = '#Session.EmployeeId#' ,Record_Modified_Date = #now()# where Invoice_ID = '#url.Inv_id#' UPDATE #Request.prefix_db_agency#.Invoices SET claim_status = '#url.claimstatus#' ,Record_Modified_By = '#Session.EmployeeId#' ,Record_Modified_Date = #now()# where Invoice_ID = '#url.Invoiceid#' SELECT * from #Request.prefix_db_agency#.pPayer WHERE Loc_ID = '#session.AgencyID#' and Status = 0 SELECT p.Patient_ID, p.Pt_Last, p.Pt_First, p.Status FROM #Request.prefix_db_agency#.pPatients p, #Request.prefix_db_agency#.Invoices i WHERE p.Loc_ID = '#session.agencyid#' AND p.Status IN (#Request.patient_status#) AND i.Patient_ID = p.Patient_ID AND i.PatientPayer_ID = #Session.Payer_ID# Group by p.Patient_ID ORDER BY p.Pt_Last select i.Invoice_Id,i.errors, i.DateSent, i.Output, i.MethodSent, i.Comment,i.Error_description, i.InvoiceTotal, i.PayerBalance,i.claim_status, i.User_defined_Status, i.Assmt_ID, i.Internal_control_number, DATEDIFF(CURDATE(), i.DateSent) AS age , p.Patient_Id, p.Status, p.Pt_Last, p.Pt_First, p.Pt_Middle , a.F177 AS START, a.F178 AS END , a.F26 AS TYPE, (SELECT Min(Visit_Date) FROM #Request.prefix_db_agency#.pSchedules where ( NOA = i.Invoice_ID AND status = 0) and Deleted = 0 ORDER BY Visit_Date) as MIN, (SELECT Max(Visit_Date) FROM #Request.prefix_db_agency#.pSchedules where ( NOA = i.Invoice_ID AND status = 0) and Deleted = 0 ORDER BY Visit_Date) as MAX, pr.Pay_Name, pr.Pay_ID, pr.Pay_Contact, pr.Pay_Phone, pr.Pay_Phone AS Contact_Phone_Ext, pr.Output AS PayerOutputType , s.Visit_Date, (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 , i.InvoiceTotal Total_Eclaim, i.Output as POUT FROM #Request.prefix_db_agency#.Invoices i join #Request.prefix_db_agency#.pSchedules s on ( s.NOA = i.Invoice_ID) join #Request.prefix_db_agency#.pPatients p on p.patient_id = i.patient_id AND p.Status IN (0,1,4) join #Request.prefix_db_agency#.pAssessments a on i.Assmt_ID = a.Assmt_ID join #Request.prefix_db_agency#.pPayer pr on i.PatientPayer_ID = pr.Pay_ID WHERE i.Agency_ID = #session.AgencyId# AND ( s.NOA != 0) AND i.InvoiceTotal = '#form.amount_filter#' AND SUBSTR(i.DateSent,1,10) >= #CreateODBCDate(form.df)# AND SUBSTR(i.DateSent,1,10) <= #CreateODBCDate(form.dt)# AND SUBSTR(i.DateSent,1,10) = #CreateODBCDate(form.db)# AND DATEDIFF(CURDATE(), i.DateSent) >= '#form.age_filter#' AND (p.Patient_ID = '#form.patient_filter#') AND pr.Pay_Name LIKE '%#form.payer_filter#%' AND pr.Pay_ID = #url.ID# AND i.Output = '#url.Output#' AND i.Output = 'RAP' AND i.Deleted <> 1 AND ( IFNULL(TRIM(i.claim_status),'') IN ('','Waiting','cancelled') OR i.claim_status IS NULL OR i.claim_status IN ('F2','Denied','A3','A4','A6','A7','A8','Rejected','F3','Adjusted','A2','Accepted','P0','P1','Approved','Pending','F1','Paid','A1','Returned') ) AND pr.Status = '0' AND i.DateSent >= #CreateODBCDate(url.df)# AND i.DateSent <= #CreateODBCDate(url.dt)# AND py.Pay_Name LIKE '%#url.payer_filter#%' AND p.Patient_ID = '#url.patient_filter#' Group by i.Invoice_Id ORDER BY Pt_Last, Pt_First ,MIN DESC SELECT * FROM GetI WHERE claim_status IS NULL OR claim_status = '' OR claim_status = 'Waiting' OR claim_status = 'cancelled' SELECT * FROM GetI WHERE claim_status IN ('F2','Denied') SELECT * FROM GetI WHERE claim_status IN ('A3','A4','A6','A7','A8','Rejected') SELECT * FROM GetI WHERE claim_status IN ('F3','Adjusted') SELECT * FROM GetI WHERE claim_status IN ('A2','Accepted') SELECT * FROM GetI WHERE claim_status IN ('P0','P1','Approved','Pending') SELECT * FROM GetI WHERE claim_status IN ('F1','Paid') SELECT * FROM GetI WHERE claim_status IN ('A1','Returned')

NOAs Ready to Submit - 277 Status

Back Export Report

Waiting for Acknowledgment	Denied	Rejected	Adjusted	In Process	Pending	Paid	Received
#getWaiting.recordCount#	#getDenied.recordCount#	#getRejected.recordCount#	#getAdjusted.recordCount#	#getInProcess.recordCount#	#getPending.recordCount#	#getPaid.recordCount#	#getReceived.recordCount#