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#' AND (IFNULL(Invoice_Lock,0) <> 1) 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#' AND (IFNULL(Invoice_Lock,0) <> 1) 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 DISTINCT i.Invoice_Id,i.errors, i.DateSent, i.Output, i.MethodSent, i.Comment, i.InvoiceTotal, i.Seq_Amount, i.PayerBalance,i.claim_status, i.Internal_control_number, i.User_defined_Status, i.Assmt_ID ,DATEDIFF(CURDATE(), i.DateSent) AS age , p.Patient_Id, p.Status, p.Pt_Last, p.Pt_First, p.Pt_Middle , a.F177 AS STARTS, a.F178 AS ENDD , a.F26 AS TYPE, (SELECT Min(Visit_Date) FROM #Request.prefix_db_agency#.pSchedules where Invoice_ID = i.Invoice_ID and Deleted = 0 ORDER BY Visit_Date) as MIN, (SELECT Max(Visit_Date) FROM #Request.prefix_db_agency#.pSchedules where Invoice_ID = i.Invoice_ID and Deleted = 0 ORDER BY Visit_Date) as MAX, CASE WHEN DATEDIFF(s.Visit_Date,a.F177) < 30 THEN a.F177 WHEN DATEDIFF(s.Visit_Date,a.F177) >= 30 THEN DATE_ADD(a.F177, INTERVAL 30 DAY) ELSE a.F177 END as START, CASE WHEN DATEDIFF(s.Visit_Date,a.F177) < 30 THEN DATE_ADD(a.F177, INTERVAL 29 DAY) WHEN DATEDIFF(s.Visit_Date,a.F177) >= 30 THEN DATE_ADD(a.F177, INTERVAL 59 DAY) ELSE a.F178 END as END, /*(SELECT SUM(IFNULL(Visits_payments.write_offamount, 0)) FROM #Request.prefix_db_agency#.Visits_payments JOIN #Request.prefix_db_agency#.Payments ON Payments.Payment_ID = Visits_payments.Payment_ID AND Payments.Deleted = 0 WHERE Visits_payments.status = 0 AND Visits_payments.write_offcode != 'PR' AND Payments.Invoice_ID =i.Invoice_ID ) + (SELECT SUM(IFNULL(Visits_payments.second_write_offamount, 0)) FROM #Request.prefix_db_agency#.Visits_payments JOIN #Request.prefix_db_agency#.Payments ON Payments.Payment_ID = Visits_payments.Payment_ID AND Payments.Deleted = 0 AND s.Schedule_ID = Visits_payments.Schedule_ID WHERE Visits_payments.status = 0 AND Visits_payments.write_offcode != 'PR' AND Payments.Invoice_ID =i.Invoice_ID) + (SELECT SUM(IFNULL(Visits_payments.third_write_offamount, 0)) FROM #Request.prefix_db_agency#.Visits_payments JOIN #Request.prefix_db_agency#.Payments ON Payments.Payment_ID = Visits_payments.Payment_ID AND Payments.Deleted = 0 WHERE Visits_payments.status = 0 AND Visits_payments.write_offcode != 'PR' AND Payments.Invoice_ID =i.Invoice_ID) + (SELECT SUM(IFNULL(Visits_payments.fourth_write_offamount, 0)) FROM #Request.prefix_db_agency#.Visits_payments JOIN #Request.prefix_db_agency#.Payments ON Payments.Payment_ID = Visits_payments.Payment_ID AND Payments.Deleted = 0 WHERE Visits_payments.status = 0 AND Visits_payments.write_offcode != 'PR' AND Payments.Invoice_ID =i.Invoice_ID) AS total_write_offs,*/ /* ((SELECT SUM(IFNULL(Visits_payments.write_offamount, 0)) FROM #Request.prefix_db_agency#.Visits_payments WHERE Payment_ID IN (SELECT Payment_ID FROM agency_1150.Payments WHERE Payments.Deleted = 0 AND Payments.Invoice_ID = i.Invoice_ID ) AND Visits_payments.status = 0 AND Visits_payments.write_offcode != 'PR' ) + (SELECT SUM(IFNULL(Visits_payments.second_write_offamount, 0)) FROM #Request.prefix_db_agency#.Visits_payments WHERE Payment_ID IN (SELECT Payment_ID FROM agency_1150.Payments WHERE Payments.Deleted = 0 AND Payments.Invoice_ID = i.Invoice_ID ) AND Visits_payments.status = 0 AND Visits_payments.write_offcode != 'PR' ) + (SELECT SUM(IFNULL(Visits_payments.third_write_offamount, 0)) FROM #Request.prefix_db_agency#.Visits_payments WHERE Payment_ID IN (SELECT Payment_ID FROM agency_1150.Payments WHERE Payments.Deleted = 0 AND Payments.Invoice_ID = i.Invoice_ID ) AND Visits_payments.status = 0 AND Visits_payments.write_offcode != 'PR' ) + (SELECT SUM(IFNULL(Visits_payments.fourth_write_offamount, 0)) FROM #Request.prefix_db_agency#.Visits_payments WHERE Payment_ID IN (SELECT Payment_ID FROM agency_1150.Payments WHERE Payments.Deleted = 0 AND Payments.Invoice_ID = i.Invoice_ID ) AND Visits_payments.status = 0 AND Visits_payments.write_offcode != 'PR' )) AS total_write_offs,*/ 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 and deleted != 1 AND (payment_type != 'Reversal Payment' OR payment_type IS NULL)) 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' OR payment_type IS NULL)) as wOff, IFNULL((SELECT Pay_Seq_Amount FROM #Request.prefix_db_agency#.Payments WHERE Invoice_Id = i.Invoice_Id AND Deleted <> 1 AND PaymentDate >= #CreateODBCDate(url.df)# AND PaymentDate <= #CreateODBCDate(url.dt)# ORDER BY PaymentDate DESC LIMIT 0,1),0) AS Pay_Seq_Amount, IFNULL((SELECT Pay_Seq_Code FROM #Request.prefix_db_agency#.Payments WHERE Invoice_Id = i.Invoice_Id AND Deleted <> 1 AND PaymentDate >= #CreateODBCDate(url.df)# AND PaymentDate <= #CreateODBCDate(url.dt)# ORDER BY PaymentDate DESC LIMIT 0,1),0) AS Pay_Seq_Code, IFNULL((SELECT SUM(Pay_outlier) FROM #Request.prefix_db_agency#.Payments WHERE Invoice_Id = i.Invoice_Id AND Deleted <> 1 AND PaymentDate >= #CreateODBCDate(url.df)# AND PaymentDate <= #CreateODBCDate(url.dt)# ORDER BY PaymentDate DESC LIMIT 0,1),0) AS Pay_outlier, i.InvoiceTotal Total_Eclaim, i.Output as POUT FROM #Request.prefix_db_agency#.Invoices i join #Request.prefix_db_agency#.pSchedules s on s.Invoice_ID = i.Invoice_ID join #Request.prefix_db_agency#.pSchedules s on s.Invoice_ID = i.Invoice_ID join #Request.prefix_db_agency#.pPatients p on p.patient_id = i.patient_id LEFT join #Request.prefix_db_agency#.pAssessments a on i.Assmt_ID = a.Assmt_ID AND a.Status = '0' join #Request.prefix_db_agency#.pPayer pr on i.PatientPayer_ID = pr.Pay_ID WHERE i.Agency_ID = #session.AgencyId# 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 p.Status IN (#Request.patient_status#) AND i.Output is not null AND i.Deleted <> 1 AND (IFNULL(TRIM(i.claim_status),'') IN ('','Waiting','cancelled') OR i.claim_status IS NULL) AND (i.claim_status IN ('A2','Accepted')) AND pr.Status = '0' AND i.InvoiceTotal > 0 AND ( (i.InvoiceTotal - (select sum(amount + writeoffamount + Pay_Seq_Amount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id and Deleted =0) > 0 ) OR (select sum(amount + writeoffamount + Pay_Seq_Amount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id and Deleted =0) is null ) 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 SUM( IFNULL(Visits_payments.write_offamount, 0) + IFNULL(Visits_payments.second_write_offamount, 0) + IFNULL(Visits_payments.third_write_offamount, 0) + IFNULL(Visits_payments.fourth_write_offamount, 0) ) AS total_write_offs, Payments.Invoice_ID FROM #Request.prefix_db_agency#.Visits_payments JOIN #Request.prefix_db_agency#.Payments ON Payments.Payment_ID = Visits_payments.Payment_ID AND Payments.Deleted = 0 WHERE Visits_payments.status = 0 AND ( Visits_payments.write_offcode != 'PR' OR Visits_payments.second_write_offcode != 'PR' OR Visits_payments.third_write_offcode != 'PR' OR Visits_payments.fourth_write_offcode != 'PR' ) AND Payments.Invoice_ID IN (#ValueList(getI.Invoice_ID)#) GROUP BY Payments.Invoice_ID

Claims AwaitingProcessing claims for #GetI.Pay_Name# #GetI.Output#

Back Rebill Tracking &df=#url.df#&dt=#url.dt#&payer_filter=#url.payer_filter#&patient_filter=#url.patient_filter#&ACKNW=1"> Export Report

>

SELECT * FROM getinv_total WHERE Invoice_ID = '#getI.Invoice_ID#' Request.bal GT 0 OR (Request.bal EQ 0 AND url.Output EQ 'Medicare') > Output eq 'RAP') OR Output Neq 'RAP' > ) />

Patient	Assessment	Billing PeriodFirst-Last Visit	Billed	Age	Due	Pay	Rebill	Delete	Claim Status	Comments	ICN
#Pt_Last#, #Pt_First#	SOCRECERT	#DateFormat(MIN,"mm/dd/yy")# - #DateFormat(MAX,"mm/dd/yy")# #DateFormat(MIN,"mm/dd/yy")# - #DateFormat(MIN,"mm/dd/yy")##DateFormat(START,"mm/dd/yy")# - #DateFormat(END,"mm/dd/yy")##DateFormat(MIN,"mm/dd/yy")# - #DateFormat(MAX,"mm/dd/yy")#	#DateFormat(DateSent,"mm/dd/yy")#	#age# days	#DollarFormat(InvAmt)#	Pay	Rebill	Delete		#Comment# Request.bal - #Request.bal# Request.bal - #Request.bal# Request.bal - #Request.bal# InvAmt - #InvAmt# amtPaid - #amtPaid# writeOff - #writeOff# Seq_Amount - #Seq_Amount# total_writeoffs - #total_writeoffs#	#Internal_control_number#

TOTALS #DollarFormat(alltotals)#

No Claim Awaiting Payment were found