Deposit correlation & Reports?

[Tante]

Help! I use PNC as my bank and my payments do not match my bank. The Open dental reports show multiple listings for patients/ insurance / check number / amount. The bank has one check and one total amount. Is there a report that has a list of each check entered as a total amount with date? We also take EFTs from a couple insurances. We deposit paper checks in our office using Deposit Now. I am struggling finding a good solution to have things match up . Ideally I'd like to verify monthly or weekly if necessary. I need some organization help, as well as reports. Thanks in advance

[cmcgehee]

It sounds to me like you want a report like our internal Daily Payments report but that shows one row per check. I modified a query from our existing queries page http://opendentalsoft.com:1942/ODQueryL ... yList.aspx.

Code: Select all

/*Modified 687 Daily Payments Report, broken down like built in report*/
SET @StartDate='2016-08-01', @EndDate='2016-08-31';
(SELECT 'Insurance Check' AS PaymentType,cp.PatNum,c.CarrierName,cp.DateCP AS DatePay,cpy.CheckNum,FORMAT(SUM(cp.InsPayAmt),2) AS PaymentAmt
FROM claimproc cp
INNER JOIN claimpayment cpy ON cpy.ClaimPaymentNum=cp.ClaimPaymentNum
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier c ON c.CarrierNum=ip.CarrierNum
WHERE cp.DateCP BETWEEN @StartDate AND @EndDate
AND (cp.Status=1 OR cp.Status=4)
GROUP BY cpy.ClaimPaymentNum)
UNION ALL
(SELECT CONCAT('Patient Payment - ',d.ItemName) AS PaymentType,ps.PatNum,NULL AS CarrierName,ps.DatePay,pm.CheckNum,FORMAT(SUM(ps.SplitAmt),2) AS PaymentAmt
FROM payment pm
INNER JOIN definition d ON d.DefNum=pm.PayType
INNER JOIN paysplit ps ON ps.PayNum=pm.PayNum
WHERE ps.DatePay BETWEEN @StartDate AND @EndDate
GROUP BY pm.PayNum)
ORDER BY PaymentType,DatePay,CarrierName;

Let me know if this is what you're looking for.