SELECT
DISTINCT i.Invoice_Id,i.errors, i.DateSent, i.Output, i.MethodSent, i.Comment, i.InvoiceTotal, 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
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,
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) AS Paid ,
(SELECT SUM(WriteOffAmount) FROM #Request.prefix_db_agency#.Payments WHERE Invoice_Id = i.Invoice_Id AND Deleted <> 1) AS wOff ,
i.InvoiceTotal Total_Eclaim, i.Output as POUT,pr.Output AS PayerOutputType
FROM #Request.prefix_db_agency#.Invoices i
join #Request.prefix_db_agency#.pSchedules s on s.Invoice_ID = i.Invoice_ID AND s.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#.pPatients p on p.patient_id = i.patient_id
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 pr.Pay_ID = #url.ID#
AND i.claim_status ='Denied'
AND ((select IFNULL(SUM(amount),0) from #Request.prefix_db_agency#.Payments
where invoice_id = i.invoice_id and Deleted = 0 ) = 0 OR
i.claim_status ='' or i.claim_status ='Waiting'
or i.claim_status is null or i.claim_status ='cancelled')
AND i.claim_status ='Accepted'
AND pr.Status = '0'
AND p.Status IN (#Request.patient_status#)
AND a.Status = '0'
AND i.Output is not null
AND i.Deleted <> 1
AND i.InvoiceTotal > 0
AND ( (i.InvoiceTotal - i.Seq_Amount -
(select sum(amount + writeoffamount) from #Request.prefix_db_agency#.Payments
where invoice_id = i.invoice_id and Deleted =0) > 0)
OR (select sum(amount + writeoffamount)
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.PayName LIKE '%#url.payer_filter#%'AND p.Patient_ID = '#url.patient_filter#'
Group by i.Invoice_Id
ORDER BY Pay_Name ASC, Pt_Last, Pt_First ,MIN DESC