Payment on Production reports?

For users or potential users.
Post Reply
babysilvertooth
Posts: 129
Joined: Sat Jun 12, 2010 3:18 pm

Payment on Production reports?

Post by babysilvertooth » Tue Jan 10, 2017 9:48 am

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

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Payment on Production reports?

Post by cmcgehee » Tue Jan 10, 2017 1:06 pm

It sounds like query 771 might fit your needs.

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;
This report requires that you are splitting your patient payments and insurance payments to procedures within Open Dental.
Chris McGehee
Open Dental Software
http://www.opendental.com

Post Reply