Audit report

[Toothdr97]

I am being audited by the Ohio Department of Commerce for unclaimed funds. I need a report that lists Patient credit balances and date of last service. Can this be generated? If so, How?
Cathy

[cmcgehee]

This sounds like it could be what you need. Query 1039 from our examples page:

Code: Select all

/*1039 Current balance for families with a credit amount, billing type, last visit date, and next visit date*/
/*Last visit and Next visit are based off any member of the family*/
/*Query code written/modified:  03/11/2015*/
SELECT aging.Guarantor AS 'Pat#',
aging.Guarantor,
aging.Total AS '$TotalCreditBal_',
def.ItemName AS 'BillingType',
MAX(pl.ProcDate) AS 'DateLastVisit',
MIN(DATE(nextappt.AptDateTime)) AS 'DateNextVisit'
FROM (
	/*Get the family level charges, credits, and ins estimates*/
	SELECT g.PatNum AS Guarantor,g.LName,g.FName,g.BillingType,FORMAT(ROUND(SUM(B.PatBal),2),2) AS Total
	FROM (
		/*Get Patient level charges and credits*/
		SELECT RawPatTrans.PatNum,SUM(RawPatTrans.TranAmount) AS PatBal
		FROM (
			/*Get the completed procedure dates and charges for the entire office history*/
			SELECT 'Fee' AS TranType,pl.PatNum AS PatNum,pl.ProcDate AS TranDate,
			pl.ProcFee*(pl.UnitQty+pl.BaseUnits) AS TranAmount,pl.ClinicNum
			FROM procedurelog pl
			WHERE pl.ProcStatus=2
			UNION ALL
			/*Paysplits for the entire office history*/
			SELECT 'Pay' AS TranType,ps.PatNum AS PatNum,ps.DatePay AS TranDate,
			-ps.SplitAmt AS TranAmount,ps.ClinicNum
			FROM paysplit ps
			WHERE ps.PayPlanNum=0 /*Only splits not attached to payment plans*/
			UNION ALL
			/*Get the adjustment dates and amounts for the entire office history*/
			SELECT 'Adj' AS TranType,a.PatNum AS PatNum,a.AdjDate AS TranDate,
			a.AdjAmt AS TranAmount,a.ClinicNum
			FROM adjustment a
			WHERE a.AdjAmt!=0
			UNION ALL
			/*Claim payments for the entire office history*/
			SELECT 'InsPay' AS TranType,cp.PatNum AS PatNum,cp.DateCp AS TranDate,
			-cp.InsPayAmt-cp.Writeoff AS TranAmount,cp.ClinicNum
			FROM claimproc cp
			WHERE cp.Status IN (1,4,5,7)/*received,supplemental,CapClaim, or CapComplete*/
			UNION ALL
			/*Payment plan principal for the entire office history on or before the given date*/
			SELECT 'PayPlan' AS TranType,pp.PatNum AS PatNum,pp.PayPlanDate AS TranDate,
			-pp.CompletedAmt AS TranAmount,MAX(ppc.ClinicNum) AS ClinicNum /*MAX gets valid ClinicNum if one exists, otherwise 0*/
			FROM payplan pp
			INNER JOIN payplancharge ppc ON ppc.PayPlanNum=pp.PayPlanNum
			WHERE pp.CompletedAmt!=0
			GROUP BY pp.PayPlanNum
		) RawPatTrans
		WHERE TranDate<=CURDATE()
		GROUP BY RawPatTrans.PatNum
	) B
	LEFT JOIN (
		/*Get patient level InsPayEst and Writeoffs from unreceived claims.*/
		SELECT cp.PatNum,SUM(cp.InsPayEst+cp.Writeoff) AS InsEst
		FROM claimproc cp
		WHERE cp.PatNum!=0
		AND ((cp.Status=0 AND cp.ProcDate<=CURDATE()) OR (cp.Status=1 AND cp.DateCP>CURDATE()))
		AND cp.ProcDate<=CURDATE()
		GROUP BY cp.PatNum
	) C ON C.PatNum=B.PatNum
	INNER JOIN patient p ON p.PatNum=B.PatNum
	INNER JOIN patient g ON g.PatNum=p.Guarantor
	GROUP BY g.PatNum
	HAVING SUM(B.PatBal)<-0.005
) aging
INNER JOIN patient p ON aging.Guarantor=p.Guarantor
INNER JOIN procedurelog pl ON pl.PatNum=p.PatNum AND pl.ProcStatus=2
LEFT JOIN appointment nextappt ON nextappt.PatNum=p.PatNum AND nextappt.AptStatus IN (1,4) AND DATE(nextappt.AptDateTime)>CURDATE()
INNER JOIN definition def ON def.DefNum=aging.BillingType
GROUP BY aging.Guarantor
ORDER BY aging.LName,aging.FName

If you just need a slight modification to this, you can call in to Open Dental and we can modify it for you. If you think you need a completely new query, you can submit a request at http://opendentalsoft.com:1942/ODQueryR ... tForm.aspx.