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
how to reconcile payments and outstanding
-
- Posts: 129
- Joined: Sat Jun 12, 2010 3:18 pm
Re: how to reconcile payments and outstanding
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;
-
- Posts: 1
- Joined: Sat Mar 12, 2022 7:27 pm
Re: how to reconcile payments and outstanding
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!
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!
Re: how to reconcile payments and outstanding
Running this query in Open Dental's User Query window should give you a totals row at the bottom.
-
- Posts: 129
- Joined: Sat Jun 12, 2010 3:18 pm
Re: how to reconcile payments and outstanding
I just ran it, it does not give totals, but can be exported to Spreadsheet to get totals.