SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA like '%agency_1150%' group by TABLE_SCHEMA SELECT DISTINCT i.Invoice_Id, i.DateSent, i.MethodSent, i.InvoiceTotal, i.PayerBalance, DATEDIFF(CURDATE(), i.DateSent) AS age ,p.Pt_Last, p.Pt_First, p.Pt_Middle,i.Seq_Amount, i.Seq_code,i.outlier ,pr.Pay_Name, pr.Pay_ID,pr.Output ,(select sum(amount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id AND deleted != 1 ) as paid ,(select sum(writeoffamount) from #Request.prefix_db_agency#.Payments where invoice_id = i.invoice_id and deleted != 1 AND WriteOffType!= "" AND WriteOffType not like 'PR%') as wOff, ROUND(IFNULL(i.ContractTotal,0),2) AS AR_total_new, 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, IFNULL((SELECT LUPA FROM #Request.prefix_db_lookup#.CW_Visit_Utilization WHERE HIPPS_Code = a.F300),0) AS recommnded_visit1, IFNULL((SELECT LUPA FROM #Request.prefix_db_lookup#.CW_Visit_Utilization WHERE HIPPS_Code = a.F297),0) AS recommnded_visit2, (SELECT count(Schedule_ID) FROM #Request.prefix_db_agency#.pSchedules WHERE pSchedules.invoice_id = i.invoice_id AND pSchedules.Status = 0 AND pSchedules.Missed = 0) AS total_visits FROM #Request.prefix_db_agency#.Invoices i LEFT OUTER JOIN #Request.prefix_db_agency#.pSchedules s on s.Invoice_ID = i.Invoice_ID AND i.Assmt_ID = s.Assmt_ID AND s.status = 0 JOIN #Request.prefix_db_agency#.pPatients p on i.patient_id = p.patient_id JOIN #Request.prefix_db_agency#.pPayer pr on i.PatientPayer_ID = pr.Pay_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments a on s.Assmt_ID = a.Assmt_ID AND i.Assmt_ID = a.Assmt_ID AND a.status = 0 WHERE i.invoice_id = '#url.inv#' AND i.Agency_ID = '#agency_id#' AND p.patient_id = i.patient_id AND pr.Pay_ID = i.patientpayer_id AND i.Deleted <> 1 AND pr.status = 0 AND p.Status IN (0,1,4) GROUP BY i.invoice_id SELECT * FROM #Request.prefix_db_agency#.pSchedules WHERE Status = 0 AND Invoice_ID = '#url.inv#' SELECT * FROM gettotalvisits WHERE Missed = 0 AND skill IN ( '#skillitem#') UPDATE #Request.prefix_db_agency#.Invoices SET LUPA_contract = #NumberFormat(Request.LUPA,'_____.__')#, LUPA_threshold = 1 WHERE Invoice_ID = #Invoice_ID# AND (IFNULL(Invoice_Lock,0) <> 1)