SELECT
MAX(s.Payment_ID) as Payment_ID,s.PaymentDate,s.Record_Created_By,
(select sum(s.Amount) from #Request.prefix_db_agency#.Payments s
where s.invoice_id = p.invoice_id and s.Deleted <> 1
AND s.PaymentDate >= #CreateODBCDate(url.df)#
AND s.PaymentDate <= #CreateODBCDate(url.dt)#
) as paid,
(s.WriteOffAmount) as WriteOffAmount,
s.PatientPayer_ID ,
(select sum(writeoffamount) from #Request.prefix_db_agency#.Payments
where invoice_id = p.invoice_id and Deleted <> 1
AND s.PaymentDate >= #CreateODBCDate(url.df)#
AND s.PaymentDate <= #CreateODBCDate(url.dt)#
) as wOff,
p.PayerBalance,
(select s.PaymentDate from #Request.prefix_db_agency#.Payments s
WHERE s.invoice_id = p.invoice_id
AND s.PaymentDate >= #CreateODBCDate(url.df)#
AND s.PaymentDate <= #CreateODBCDate(url.dt)#
AND s.Amount > 0
and s.deleted <> 1 ORDER BY s.PaymentDate DESC LIMIT 0,1) as paid_date,
pa.Patient_ID, pa.Pt_Last,pa.Pt_Agy_ID,
a.F177 AS STARTS, a.F178 AS ENDS , a.F26 AS TYPE, pa.Pt_First, pa.Pt_Agy_ID, pa.Status,
CASE
WHEN DATEDIFF(p.DateSent,a.F177) < 30 THEN a.F177
WHEN DATEDIFF(p.DateSent,a.F177) >= 30 THEN DATE_ADD(a.F177, INTERVAL 30 DAY)
ELSE a.F177
END as START,
CASE
WHEN DATEDIFF(p.DateSent,a.F177) < 30 THEN DATE_ADD(a.F177, INTERVAL 29 DAY)
WHEN DATEDIFF(p.DateSent,a.F177) >= 30 THEN DATE_ADD(a.F177, INTERVAL 59 DAY)
ELSE a.F178
END as END,
DATE_FORMAT(pa.Pt_DOB,'%m/%d/%Y') AS DateOfBirth,
(SELECT Date_Deposit FROM #Request.prefix_db_agency#.pDeposit
WHERE pDeposit.Internal_control_number = p.Internal_control_number
AND pDeposit.Status = 0 ORDER BY Date_Deposit DESC LIMIT 0,1) AS Date_Deposit,
(SELECT Min(Visit_Date) FROM #Request.prefix_db_agency#.pSchedules
where Assmt_ID = p.Assmt_ID AND p.Invoice_ID = pSchedules.Invoice_ID and status = 0 ORDER BY Visit_Date) as MIN,
(SELECT Max(Visit_Date) FROM #Request.prefix_db_agency#.pSchedules
where Assmt_ID = p.Assmt_ID AND p.Invoice_ID = pSchedules.Invoice_ID and status = 0 ORDER BY Visit_Date) as MAX,
p.Invoice_Id, p.Comment, p.Output as POUT,
DATE_FORMAT(p.DateSent,'%Y-%m-%d') AS DateSent,
DATEDIFF(CURDATE(), p.DateSent) AS age,
MAX(DATE_FORMAT(s.PaymentDate,'%Y-%m-%d')) AS PaymentDate,
DATE_FORMAT(p.DateSent,'%m/%d') AS ds,
(p.InvoiceTotal) as InvoiceTotal,p.PayerBalance,
pr.Pay_ID, pr.Pay_Name, pr.Payer_No, p.Output AS PayerOutputType,
(SELECT Invoice_Id FROM #Request.prefix_db_agency#.Invoices WHERE Assmt_ID = p.Assmt_ID
AND InvoiceTotal = '0.00' and Deleted <> 1 LIMIT 1) AS FinalBilling
from
#Request.prefix_db_agency#.Payments s
JOIN #Request.prefix_db_agency#.Invoices p on p.Invoice_ID = s.Invoice_ID
JOIN #Request.prefix_db_agency#.pPatients pa on pa.patient_id = p.patient_id
JOIN #Request.prefix_db_agency#.pPayer pr on pr.Pay_ID = s.patientpayer_id
LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments a on p.Assmt_ID = a.Assmt_ID
WHERE p.Agency_Id = '#session.agencyid#'
AND pa.Status IN (#Request.patient_status#) AND (a.status = 0 OR p.Assmt_ID = 0)
AND pr.Pay_ID = '#url.ID#'
AND p.Output like '#url.Output#'
AND s.PaymentDate >= #CreateODBCDate(url.df)#
AND s.PaymentDate <= #CreateODBCDate(url.dt)#
AND pa.Patient_ID = '#url.patient_filter#'
AND Amount > 0
AND s.Deleted <> 1 AND p.Deleted <> 1
AND p.InvoiceTotal > 0
AND (s.Amount > 0 OR claim_status = 'Paid'
OR (p.InvoiceTotal - (select sum(amount + writeoffamount + Pay_Seq_Amount) from #Request.prefix_db_agency#.Payments
where invoice_id = p.invoice_id and Deleted =0)<= 0 )
)
AND p.Output is not null
Group BY p.Invoice_Id
ORDER BY Pt_Last, Pt_First ASC