Finding a report: Payments made as part of payment plan

For users or potential users.
Post Reply
casadental
Posts: 3
Joined: Tue Nov 08, 2016 1:16 pm

Finding a report: Payments made as part of payment plan

Post by casadental » Tue Dec 20, 2016 4:57 pm

I am trying to find a report that will allow me to see a breakdown of what monthly payments were made as part of payment plans. Is there a Query code or a certain report that will show this?

User avatar
Arna
Posts: 444
Joined: Tue Jul 09, 2013 3:16 pm

Re: Finding a report: Payments made as part of payment plan

Post by Arna » Tue Dec 20, 2016 5:42 pm

This is surprisingly difficult for me to find. We may have to write a query to pull this information. http://opendentalsoft.com:1942/ODQueryR ... tForm.aspx
There is also no feature request to have a report that can return payments attached to payment plans. I would highly encourage you to submit a request and add the Feature Request number in this thread. The option to filter out payments attached to payment plans in the Daily Payments Report would be quite helpful to many offices, I'm sure.
Entropy isn't what it used to be...

Arna Meyer

rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

Re: Finding a report: Payments made as part of payment plan

Post by rhaber123 » Wed Dec 21, 2016 2:17 am

http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
This query need slight modification.
/*702 Income report from @FromDate to @ToDate day totalled, separated by specific payment type with totals*/

You can use the existing payment type called "Payment " when posting the monthly paid amount, and use the above modified query
I do not know how to write queries. Newbie at writing queries.

================================================================================================================================

I was able to modify the query, and replaced the payment type "Money order" by "Payment"

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
When you receive a monthly payment that is part of a payment plan, you should pick the option of "Payment" from the existing menu " Check - Cash - Credit card - Refund - Payment..."
Payment = Payment Plan
The query can find the monthly payment now, but we have another problem : we can not specify if it was a check payment or a credit card payment.......
:(
Probably you need OD to write a better query that would answer your questions
================================================================================================================================

Code: Select all

    /*702 modified - Income report from @FromDate to @ToDate day totalled, separated by specific payment type with totals*/
SET @FromDate='2016-12-01', @ToDate='2016-12-30';
SELECT A.PayDate AS 'Date', DATE_FORMAT(A.PayDate, "%W") AS 'Weekday',
Cash.PayAmt AS $PatIncCash_,
Checks.PayAmt AS $PatIncCheck_,
(COALESCE(VisaMc.PayAmt,0)+COALESCE(Discover.PayAmt,0)+COALESCE(AMEX.PayAmt,0)+COALESCE(CCAuto.PayAmt,0)) AS $PatIncCC_,
Payment.PayAmt AS $Payment_,
EFT.PayAmt AS $EFTPayment_,
CareCredit.PayAmt AS $CARECREDIT_,
InsIntRec.PayAmt AS $InsInterestReceived_,
InsInc.TransAmount AS $InsPayment_,
PatRefCheck.PayAmt AS $PATREFUNDCHECK_,
RefIns.PayAmt AS $REFINSURANCE_,
RefCC.PayAmt AS $REFCC_,
MedicaidPayRev.PayAmt AS $MEDICAIDPAYMENTREVERSAL_,
(COALESCE(Cash.PayAmt,0)+COALESCE(Checks.PayAmt,0)+COALESCE(VisaMc.PayAmt,0)+COALESCE(Discover.PayAmt,0)+COALESCE(AMEX.PayAmt,0)+COALESCE(CCAuto.PayAmt,0)+COALESCE(RefCC.PayAmt,0)+COALESCE(PatRefCheck.PayAmt,0)+COALESCE(CareCredit.PayAmt,0)+COALESCE(Payment.PayAmt,0)) AS $PatIncTotal_,/*Total of PatIncCash, PatIncCheck, VisaMc, Discover, AMEX, CCAuto, RefundCC, PatRefCheck, CareCredit, and Payment*/
(COALESCE(InsInc.TransAmount,0)+COALESCE(InsIntRec.PayAmt,0)+COALESCE(MedicaidPayRev.PayAmt,0)+COALESCE(RefIns.PayAmt,0)) AS $InsIncTotal_,/*Total of InsInterestReceived, RefundIns, MedicaidPayRev, and InsInc*/
(COALESCE(Cash.PayAmt,0)+COALESCE(Checks.PayAmt,0)+COALESCE(VisaMc.PayAmt,0)+COALESCE(Discover.PayAmt,0)+COALESCE(AMEX.PayAmt,0)+COALESCE(CCAuto.PayAmt,0)+COALESCE(Payment.PayAmt,0)+COALESCE(InsIntRec.PayAmt,0)+COALESCE(EFT.PayAmt,0)+COALESCE(CareCredit.PayAmt,0)+COALESCE(PatRefCheck.PayAmt,0)+COALESCE(RefIns.PayAmt,0)+COALESCE(RefCC.PayAmt,0)+COALESCE(MedicaidPayRev.PayAmt,0)+COALESCE(InsInc.TransAmount,0)) AS $TotalIncome_ /*Total of all columns*/
FROM
(
(SELECT PayDate FROM payment GROUP BY PayDate)
UNION 
(SELECT CheckDate AS PayDate FROM claimpayment GROUP BY PayDate)
) A 
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%Check%'
	GROUP BY PayDate
) Checks ON Checks.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%Cash%'
	GROUP BY PayDate
) Cash ON Cash.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%VISA/MC%'
	GROUP BY PayDate
) VisaMc ON VisaMc.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%Discover%'
	GROUP BY PayDate
) Discover ON Discover.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%AMEX%'
	GROUP BY PayDate
) Amex ON Amex.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%Credit Card%'
	GROUP BY PayDate
) CCAuto ON CCAuto.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%Payment%'
	GROUP BY PayDate
) Payment ON Payment.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%EFT%'
	GROUP BY PayDate
) EFT ON EFT.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%CARE CREDIT%'
	GROUP BY PayDate
) CareCredit ON CareCredit.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%PATIENT REFUND CHECK%'
	GROUP BY PayDate
) PatRefCheck ON PatRefCheck.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%REFUND INSURANCE%'
	GROUP BY PayDate
) RefIns ON RefIns.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%REFUND CC%'
	GROUP BY PayDate
) RefCC ON RefCC.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%MEDICAID PAYMENT REVERSAL%'
	GROUP BY PayDate
) MedicaidPayRev ON MedicaidPayRev.PayDate=A.PayDate
LEFT JOIN (
	SELECT PayDate,d.ItemName,SUM(p.PayAmt) AS PayAmt
	FROM payment p
	INNER JOIN definition d ON d.DefNum=p.PayType
	WHERE d.ItemName LIKE '%Insurance Interest Received%'
	GROUP BY PayDate
) InsIntRec ON InsIntRec.PayDate=A.PayDate
LEFT JOIN (
	SELECT cpay.CheckDate AS TransDate,SUM(cproc.InsPayAmt) AS TransAmount
	FROM claimpayment cpay,claimproc cproc
	WHERE cproc.ClaimPaymentNum=cpay.ClaimPaymentNum
	GROUP BY TransDate
) InsInc ON InsInc.TransDate=A.PayDate
WHERE A.PayDate BETWEEN (@FromDate) AND (@ToDate)
ORDER BY A.PayDate;          
Last edited by rhaber123 on Wed Dec 21, 2016 2:59 am, edited 3 times in total.

User avatar
Arna
Posts: 444
Joined: Tue Jul 09, 2013 3:16 pm

Re: Finding a report: Payments made as part of payment plan

Post by Arna » Wed Dec 21, 2016 9:14 am

The case where one payment is split into both regular and payment plans needs to be considered.
I'm sure we can develop a query, however I'd like to see this as part of the software at some stage. Payment plans are a pretty popular feature and it'd be nice to be able to analyze that data a little deeper.
Entropy isn't what it used to be...

Arna Meyer

Post Reply