Insurance Estimate wrong in Aging Report

[irfan]

On my aging reports the InsEst shows our total fees minus patient portion, but does not include writeoffs anticipated. The Insurance estimate in the patient account is fine- it shows a writeoff and insurance portion, but in the aging report it does not account for the writeoff. This is making my aging report show much higher than it should. Is there a way to show the anticipated write off?

example: Patient account shows $400 fees, $50 patient portion paid already, $125 writeoff, $225 Insurance portion estimated.

the aging report would show $350 for InsEst, not $225.

[jordansparks]

It's feature request #396.

[atd]

I have a query I use to accomplish this until feature request #396 is complete. You may want to modify it to remove the clinic filtering:

DROP TABLE IF EXISTS tempins;

CREATE TABLE tempins(
Claim mediumint unsigned NOT NULL,
Guarantor mediumint unsigned NOT NULL,
Writeoff float(2) NOT NULL,
Estimate float(2) NOT NULL,
PRIMARY KEY (Claim));

INSERT INTO tempins
SELECT claimproc.ClaimProcNum, patient.Guarantor, SUM(claimproc.WriteOff), SUM(claimproc.InsPayEst+claimproc.InsPayAmt-(claimproc.InsPayEst*claimproc.Status))
FROM preference, claimproc
LEFT JOIN patient ON claimproc.PatNum=patient.PatNum
WHERE preference.PrefName='DateLastAging'
AND ((claimproc.Status=0 AND claimproc.ProcDate<=preference.ValueString) OR (claimproc.Status=1 AND
claimproc.ProcDate<=preference.ValueString AND claimproc.DateCP>preference.ValueString)) AND patient.ClinicNum=1
GROUP BY patient.Guarantor;

SELECT CONCAT(p.LName,', ',p.FName,' ',p.MiddleI) as 'Guarantor'
,p.Bal_0_30 as '$0-30 Days .',p.Bal_31_60 as '$31-60 Days .', p.Bal_61_90 as '$61-90 Days .',p.BalOver90 as '$> 90 Days .'
,(CASE WHEN p.BalTotal<'-.005' THEN p.BalTotal ELSE (p.Bal_0_30 + p.Bal_31_60 + p.Bal_61_90 + p.BalOver90) END) as '$Total .', tempins.Writeoff as '$Writeoff .', tempins.Estimate AS '$InsEst .',
((CASE WHEN p.BalTotal<'-.005' THEN p.BalTotal ELSE (p.Bal_0_30 + p.Bal_31_60 + p.Bal_61_90 + p.BalOver90) END)-(CASE WHEN tempins.Writeoff IS NULL THEN 0 ELSE tempins.Writeoff END)-(CASE WHEN tempins.Estimate IS NULL THEN 0 ELSE tempins.Estimate END)) as '$Patient .'
FROM patient p
LEFT JOIN tempins ON tempins.Guarantor=p.PatNum
WHERE (p.Bal_0_30 > '.005' OR p.Bal_31_60 > '.005' OR p.Bal_61_90 > '.005' OR p.BalOver90 > '.005' OR p.BalTotal < '-.005') AND p.ClinicNum=1
GROUP BY p.PatNum /*this is the guarantor, not the patient*/
ORDER BY p.LName,p.FName;

DROP TABLE tempins;