DOn't know if I am just not looking at the right spot. IF i want to pay an associate on collections, but I need to see which production the payments are against (ie isnurance checks in 2017 for procs done in 2016), or patient payments for procs done in the past, etc. Other than a straight P&I report, is there a report that would show which claims/procedures were paid when?
Thanks
Payment on Production reports?
Re: Payment on Production reports?
It sounds like query 771 might fit your needs.
This report requires that you are splitting your patient payments and insurance payments to procedures within Open Dental.
Code: Select all
/*771 Daily procedures report with amount paid to date for each procedure by treating provider. As of 7.2 only works if you split patient payments to procedures. Also shows total amount patient paid in date range. For all providers, leave variable like: @Provider='%%'.*/
SET @FromDate='2012-12-01',@ToDate='2013-12-15',@Provider='%Doc%';
SELECT p.PatNum,pc.ProcCode,pr.Abbr AS Prov,pl.ProcDate,pl.ToothNum,
pc.AbbrDesc,pl.ProcFee*(pl.UnitQty+pl.BaseUnits) AS ProcFee,
SUM(COALESCE(A.InsPayEst,0)) AS $InsPayEst_,
SUM(COALESCE(A.InsPayAmt,0)) AS $InsPayAmt_,
COALESCE(B.SplitAmt,0) AS $PatPayAmt_,
SUM(COALESCE(B.SplitAmt,0))+SUM(COALESCE(A.InsPayAmt,0)) AS $TotalPaid_,
COALESCE(pay.PayAmt,0) AS $PaidInDateRange_
FROM patient p
INNER JOIN procedurelog pl ON p.PatNum=pl.PatNum
AND pl.ProcStatus=2
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN provider pr ON pr.ProvNum=pl.ProvNum AND pr.Abbr LIKE @Provider
LEFT JOIN (
SELECT cp.ProcNum,SUM(cp.InsPayEst) AS InsPayEst,SUM(cp.InsPayAmt) AS InsPayAmt
FROM claimproc cp
WHERE cp.Status IN(6,0,1,4)
GROUP BY cp.ProcNum
) A ON A.ProcNum=pl.ProcNum
LEFT JOIN (
SELECT ps.ProcNum,SUM(ps.SplitAmt) AS SplitAmt
FROM paysplit ps
GROUP BY ps.ProcNum
) B ON B.ProcNum=pl.ProcNum
LEFT JOIN (
SELECT pay.PatNum, SUM(pay.PayAmt) AS PayAmt
FROM payment pay
WHERE pay.PayDate BETWEEN @FromDate AND @ToDate
GROUP BY pay.PatNum
) pay ON pay.PatNum=p.PatNum
GROUP BY pl.ProcNum
ORDER BY pl.ProcDate,p.LName,p.FName;