SELECT Agency_ID FROM #Request.prefix_db_lookup#.Agency WHERE status = 'active' AND Agency_ID = ORDER BY Agency_ID SELECT i.Invoice_Id,i.errors, i.DateSent, i.MethodSent, i.Comment, i.InvoiceTotal, i.PayerBalance, i.Assmt_ID , i.Seq_Amount,pr.M0150, 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 pSchedules.Missed =0 AND pSchedules.invoice_id = i.invoice_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 pSchedules.invoice_id = i.invoice_id AND a.Assmt_ID = pSchedules.Assmt_ID AND pSchedules.Missed =0 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 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 = #agency_id# AND i.Deleted <> 1 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 (i.ContractTotal > 0 OR i.InvoiceTotal > 0) AND i.Output is not null AND i.Invoice_Id= '#url.inv#' AND ( i.LUPA_contract= '0' OR i.LUPA_contract IS NULL) AND (pr.Output = 'Medicare' ) Group by i.Invoice_Id ORDER BY Pt_Last, Pt_First SELECT * FROM GetI WHERE ((episode = 1 AND firstcert_sch_count < LUPA_First) OR (episode = 2 AND secondcert_sch_count < LUPA_second)) SELECT * FROM #Request.prefix_db_agency#.pSchedules WHERE Assmt_ID = #Assmt_ID# AND Status = 0 AND Visit_Date >= '#START#' AND Visit_Date <= '#END#' SELECT * FROM gettotalvisits WHERE skill IN ( '#skillitem#') AND Missed = 0 UPDATE #Request.prefix_db_agency#.Invoices SET LUPA_contract = #NumberFormat(LUPAcontract,'_____.__')# WHERE Invoice_ID = #Invoice_ID# UPDATE #Request.prefix_db_agency#.Invoices SET LUPA_contract = '0.00', LUPA_threshold = 0 WHERE Invoice_ID = '#GetI.Invoice_ID#'