UPDATE #Request.prefix_db_agency#.Invoices
SET claim_status = '#url.claimstatus#'
,Record_Modified_By = '#Session.EmployeeId#'
,Record_Modified_Date = #now()#
where Invoice_ID = '#url.Invoiceid#'
AND (IFNULL(Invoice_Lock,0) <> 1)
select * from #Request.prefix_db_agency#.pPayer WHERE Loc_ID = '#session.AgencyID#' and Status like '0'
SELECT p.Patient_ID, p.Pt_Last, p.Pt_First, p.Status
FROM #Request.prefix_db_agency#.pPatients p
WHERE p.Loc_ID = '#session.agencyid#'
ORDER BY Pt_Last
select i.Invoice_Id,i.errors, i.DateSent, i.MethodSent, i.Comment, i.InvoiceTotal, i.PayerBalance, i.Assmt_ID ,DATEDIFF(CURDATE(), i.DateSent) AS age ,
i.User_defined_Status,i.Output,
p.Patient_Id, p.Status, p.Pt_Last, p.Pt_First, p.Pt_Middle ,
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,
a.F177 AS STARTS, a.F178 AS ENDS , a.F26 AS TYPE, i.claim_status,i.Error_description,
pr.Pay_Name, pr.Pay_ID, pr.Pay_Contact, pr.Pay_Phone, pr.Pay_Phone AS Contact_Phone_Ext,
pr.Output AS PayerOutputType ,
(SELECT SUM(Amount) FROM #Request.prefix_db_agency#.Payments WHERE Invoice_Id = i.Invoice_Id) AS Paid ,
(SELECT SUM(WriteOffAmount) FROM #Request.prefix_db_agency#.Payments WHERE Invoice_Id = i.Invoice_Id) AS wOff
FROM #Request.prefix_db_agency#.Invoices i
join #Request.prefix_db_agency#.pPatients p on p.patient_id = i.patient_id
LEFT JOIN #Request.prefix_db_agency#.pAssessments a on i.Assmt_ID = a.Assmt_ID AND a.status = 0
join #Request.prefix_db_agency#.pSchedules s on i.Invoice_ID = s.Invoice_ID
join #Request.prefix_db_agency#.pPayer pr on i.PatientPayer_ID = pr.Pay_ID
WHERE
i.Agency_ID = #session.AgencyId#
AND i.Deleted <> 1
AND pr.Status = '0'
AND p.Status = '0'
AND a.Status = '0'
AND s.Status = 0
AND i.InvoiceTotal = '#form.amount_filter#'
AND SUBSTR(a.F176,1,10) >= #CreateODBCDate(form.df)#
AND SUBSTR( a.F177,1,10) <= #CreateODBCDate(form.dt)#
AND SUBSTR(i.DateSent,1,10) = #CreateODBCDate(form.db)#
AND DATEDIFF(CURDATE(), i.DateSent) >= '#form.age_filter#'
AND (p.Patient_ID = '#form.patient_filter#')
AND pr.Pay_Name LIKE '%#form.payer_filter#%'
AND pr.Pay_ID = #url.ID#
AND (i.claim_status IN ('P0','P1','Approved'))
AND (i.claim_status IN ('A1','Returned'))
AND (i.claim_status ='#url.claim_status#')
Group by i.Invoice_Id
ORDER BY Pt_Last, Pt_First
(SELECT Min(Visit_Date) as MIN,Max(Visit_Date) as MAX FROM #Request.prefix_db_agency#.pSchedules
where Assmt_ID = #Assmt_ID#
AND Invoice_ID = #Invoice_ID#
and Status = 0 ORDER BY Visit_Date)
#DateFormat(START,"mm/dd/yy")# - #DateFormat(END,"mm/dd/yy")#
#DateFormat(MIN,"mm/dd/yy")# - #DateFormat(MAX,"mm/dd/yy")#