how to reconcile payments and outstanding

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

how to reconcile payments and outstanding

Post by babysilvertooth » Mon Jan 30, 2017 7:55 am

NOt sure if the subject is worded right:
I have an associate. Trying to find a report that would pull any procedures he has done but has not been paid yet (insurance or patient paymemts), trying to go back a few years and reconcile things.Thanks

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

Re: how to reconcile payments and outstanding

Post by cmcgehee » Mon Jan 30, 2017 3:41 pm

I made a quick modification to query 771. This query will show procedures that have a fee greater than 0 where the sum of the insurance payments and patient payments is 0. Note that you need to be splitting patient payments to procedures for any query like this to work.

Code: Select all

/*Modified 771 Daily procedures report with amount paid to date for each procedure by treating provider. 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='%%'.
Modified to only show procedures that have not been paid on.*/
SET @FromDate='2016-12-01',@ToDate='2016-12-15',@Provider='%MP%';
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
WHERE pl.ProcFee>0
GROUP BY pl.ProcNum
HAVING $InsPayAmt_ + $PatPayAmt_ < 0.005
ORDER BY pl.ProcDate,p.LName,p.FName;
Chris McGehee
Open Dental Software
http://www.opendental.com

WandererEd
Posts: 1
Joined: Sat Mar 12, 2022 7:27 pm

Re: how to reconcile payments and outstanding

Post by WandererEd » Sat Mar 12, 2022 7:30 pm

Hey Chris!

I am cruising for a report and came across this reply. Query 771 gives me everything I need, except a sum total at the bottom.

I noticed you modified 771 in the past. How easy would it be to modify again? I'm happy to pay for this info.

Thanks!

joes
Posts: 239
Joined: Tue Aug 13, 2019 12:41 pm

Re: how to reconcile payments and outstanding

Post by joes » Mon Mar 14, 2022 8:23 am

Running this query in Open Dental's User Query window should give you a totals row at the bottom.
Joe Sullivan
Open Dental Software
http://www.opendental.com

babysilvertooth
Posts: 129
Joined: Sat Jun 12, 2010 3:18 pm

Re: how to reconcile payments and outstanding

Post by babysilvertooth » Thu May 12, 2022 8:00 am

I just ran it, it does not give totals, but can be exported to Spreadsheet to get totals.

Post Reply