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 WHERE p.Loc_ID = '#session.agencyid#' ORDER BY Pt_Last SELECT i.Invoice_Id,i.errors, i.DateSent, i.MethodSent, i.Comment, i.InvoiceTotal, i.PayerBalance, i.Assmt_ID , i.Seq_Amount,i.LUPA_contract,i.LUPA_threshold, DATEDIFF(CURDATE(), i.DateSent) AS age , i.User_defined_Status, p.Patient_Id, p.Status, p.Pt_Last, p.Pt_First, p.Pt_Middle ,p.Pt_Agy_ID, a.F177 AS STARTs, a.F178 AS ENDs , a.F26 AS TYPE, i.claim_status,i.Error_description, 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, pr.Pay_Name, pr.Pay_ID, pr.Pay_Contact, pr.Pay_Phone,pr.Pay_Phone AS Contact_Phone_Ext,pr.Pay_Phone AS PAY_PHONE_EXT, pr.Output AS PayerOutputType , IFNULL((SELECT SUM(Amount) FROM #Request.prefix_db_agency#.Payments WHERE Invoice_Id = i.Invoice_Id AND Deleted <> 1 ),0) AS Paid , IFNULL((SELECT SUM(WriteOffAmount) FROM #Request.prefix_db_agency#.Payments WHERE Invoice_Id = i.Invoice_Id AND Deleted <> 1 ),0) AS wOff, ROUND(IFNULL(i.ContractTotal,0),2) AS AR_total, 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_total2, CASE WHEN DATEDIFF(s.Visit_Date,a.F177) < 30 THEN 1 ELSE 2 END AS episode, (SELECT count(pSchedules.Schedule_ID) FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID WHERE pSchedules.Status = 0 AND pAssessments.Status =0 AND a.Assmt_ID = pSchedules.Assmt_ID AND pAssessments.F177 <= pSchedules.Visit_Date AND DATE_ADD(pAssessments.F177, INTERVAL 29 DAY) >= pSchedules.Visit_Date ) AS firstcert_sch_count, (SELECT count(pSchedules.Schedule_ID) FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID WHERE pSchedules.Status = 0 AND pAssessments.Status =0 AND a.Assmt_ID = pSchedules.Assmt_ID AND DATE_ADD(pAssessments.F177, INTERVAL 30 DAY) <= pSchedules.Visit_Date AND DATE_ADD(pAssessments.F177, INTERVAL 60 DAY) >= pSchedules.Visit_Date ) AS secondcert_sch_count, CW1.LUPA AS LUPA_First,CW2.LUPA AS LUPA_second FROM #Request.prefix_db_agency#.Invoices i JOIN #Request.prefix_db_agency#.pPatients p on p.patient_id = i.patient_id AND p.Status IN (#Request.patient_status#) 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 AND pr.Status = 0 JOIN #Request.prefix_db_agency#.pSchedules s on s.Invoice_ID = i.Invoice_ID AND s.Status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pPtPayer ON s.PtPayer_ID = pPtPayer.PtPayer_ID AND pPtPayer.Status = 0 LEFT OUTER JOIN #Request.prefix_db_lookup#.CW_Visit_Utilization CW1 ON a.F300 = CW1.HIPPS_Code LEFT OUTER JOIN #Request.prefix_db_lookup#.CW_Visit_Utilization CW2 ON a.F297 = CW2.HIPPS_Code WHERE i.Agency_Id = #session.AgencyId# AND i.Deleted <> 1 AND i.InvoiceTotal = '#form.amount_filter#' AND i.DateSent BETWEEN '#DateFormat(url.df,"yyyy-mm-dd")#' and '#DateFormat(url.dt,"yyyy-mm-dd")#' AND i.DateSent = '#DateFormat(Now(),"yyyy-mm-dd")#' 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 p.patient_id = i.patient_id AND i.Assmt_ID = a.Assmt_ID AND a.Assmt_ID = s.Assmt_ID and i.PatientPayer_ID = pr.Pay_ID AND pr.Pay_ID = #url.ID# AND (i.ContractTotal > 0 OR i.InvoiceTotal > 0) AND i.Output is not null Group by i.Invoice_Id ORDER BY Pt_Last, Pt_First

Daily Reporting Claims For #GetI.Pay_Name#

Back Print

Payer	Patient	MRN	Billing Period	Billed	Age	Invoice Total	Contract Total	Invoice ID
#Pay_Name#	#Pt_Last#, #Pt_First#	#Pt_Agy_ID#	(SELECT Min(Visit_Date) as MIN,Max(Visit_Date) as MAX FROM #Request.prefix_db_agency#.pSchedules where Assmt_ID = #Assmt_ID# AND Invoice_ID = #Invoice_ID# and status = 0 ORDER BY Visit_Date) #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)#	#DollarFormat(LUPA_contract)##DollarFormat(AR_total2)#	#Invoice_ID#

Grand Invoice Total #DollarFormat(Total_InvAmt)#
Grand Contract Total #DollarFormat(Total_Contract)#