SELECT DISTINCT
i.Invoice_ID, i.DateSent, i.MethodSent, i.InvoiceTotal, i.PayerBalance, DATEDIFF(CURDATE(), i.DateSent) AS age,i.Comment,
i.Seq_Amount,i.Seq_code,i.ContractTotal,i.outlier,
i.LUPA_threshold,
CASE WHEN LUPA_threshold = 1 THEN LUPA_contract
ELSE i.ContractTotal
END AS AR_total,
p.Pt_Last, p.Pt_First, p.Pt_Middle, 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
AND PaymentDate >= #CreateODBCDate(url.df)#
AND PaymentDate <= #CreateODBCDate(url.dt)#
) 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 PaymentDate >= #CreateODBCDate(url.df)#
AND PaymentDate <= #CreateODBCDate(url.dt)#
) as wOff
FROM
#Request.prefix_db_agency#.Invoices i, #Request.prefix_db_agency#.pSchedules si, #Request.prefix_db_agency#.pPatients p, #Request.prefix_db_agency#.pPayer pr
WHERE i.invoice_id = '#url.inv#'
AND i.Agency_ID = '#session.agencyid#'
AND si.Assmt_ID = i.Assmt_ID
AND p.patient_id = i.patient_id
AND pr.Pay_ID = i.patientpayer_id
AND i.Deleted = 0
AND p.Status IN (#Request.patient_status#)
AND si.Status = 0
SELECT pSchedules.*, pRevenue_Code.Measure, pRevenue_Code.EClaim_Price
FROM #Request.prefix_db_agency#.pSchedules
JOIN #Request.prefix_db_agency#.Invoices ON Invoices.Invoice_Id = pSchedules.Invoice_ID
JOIN #Request.prefix_db_agency#.pRevenue_Code ON pRevenue_Code.Rev_CodeID = pSchedules.Rev_ID
WHERE pSchedules.Status = 0 AND Invoices.Deleted <> 1
AND pSchedules.Invoice_ID = '#url.inv#'
GROUP BY pSchedules.Schedule_ID