I believe this sort of summary would require at least 5-6 separate APIs/calls coupled with a significant amount of app side logic. I think it's more for a completed visit when you consider payplans.
I've been able to hack a solution together via short queries but it's quite complicated and potentially fragile, duplicating (and likely getting wrong) a lot of logic found within Open Dental or some of the existing APIs.
Any advice on a more efficient way to tackle this? Or, perhaps it's possible to beef up the primary API objects, like Patients and Appointments, to help improve client side complexity and performance? For example, the following would go along way for my and may other use cases.
Here are my hacked together short queries... the first to get a list of visits and their summaries, the second to get financial details for a specific visit.
Get Patient Visits
Code: Select all
{
"SqlCommand": "select apt.patnum ,apt.aptnum ,apt.nextaptnum ,papt.aptnum as plannedAptNum ,papt.itemorder as plannedPriority ,apt.appointmenttypenum as AptTypeNum ,CASE WHEN apt.appointmenttypenum = 0 THEN 'Treatment' ELSE atype.appointmenttypename END as AptTypeName ,apt.aptstatus ,CASE WHEN apt.aptstatus = 1 THEN 'Booked' WHEN apt.aptstatus = 2 THEN 'Done' WHEN apt.aptstatus = 3 THEN 'Cancelled' WHEN apt.aptstatus = 5 THEN 'Missed' WHEN apt.aptstatus = 6 THEN 'Planned' END as aptstatusDisplay ,apt.confirmed ,(select itemname from definition where defnum = apt.confirmed) as confirmedDisplay ,apt.unschedstatus ,(select itemname from definition where defnum = apt.unschedstatus) as unschedstatusDisplay ,apt.isnewpatient ,apt.ishygiene ,apt.op ,apt.provnum as dentist ,(select concat(fname, ' ', lname, ' ',suffix) from provider where provnum = apt.provnum) as dentistDisplay ,apt.provhyg as hygienist ,ifnull((select concat(fname, ' ', lname, ' ',suffix) from provider where provnum = apt.provhyg ),'') as hygienistDisplay ,CASE WHEN apt.aptstatus = 6 OR (apt.aptstatus = 3 AND apt.nextaptnum > 0) THEN date_add(curdate(), interval 99 year) ELSE apt.aptdatetime END as aptdatetime ,apt.pattern ,LENGTH(apt.pattern)*5 as duration from appointment apt INNER JOIN procedurelog pl on pl.aptnum = apt.aptnum left join appointmenttype atype on atype.appointmenttypenum = apt.appointmenttypenum LEFT JOIN plannedappt papt on apt.nextaptnum = papt.aptnum OR apt.aptnum = papt.aptnum LEFT JOIN (SELECT s.aptnum,s.nextaptnum,s.aptstatus FROM appointment s INNER JOIN plannedappt ON plannedappt.aptnum = s.nextaptnum WHERE s.aptstatus <> 6) capt ON capt.nextaptnum = apt.aptnum where apt.aptstatus in (1,2,3,5,6) and capt.aptnum is null and apt.patnum = <pmsId> group by apt.aptnum order by aptdatetime asc, papt.itemorder asc"
}
Code: Select all
{
"SqlCommand": "select apt.patnum ,apt.aptnum ,apt.nextaptnum ,papt.aptnum as plannedAptNum ,papt.itemorder as plannedPriority ,apt.appointmenttypenum as AptTypeNum ,atype.appointmenttypename as AptTypeName ,apt.aptstatus ,CASE WHEN apt.aptstatus = 1 THEN 'Booked' WHEN apt.aptstatus = 2 THEN 'Done' WHEN apt.aptstatus = 3 THEN 'Cancelled' WHEN apt.aptstatus = 5 THEN 'Missed' WHEN apt.aptstatus = 6 THEN 'Planned' END as aptstatusDisplay ,apt.confirmed ,(select itemname from definition where defnum = apt.confirmed) as confirmedDisplay ,apt.unschedstatus ,(select itemname from definition where defnum = apt.unschedstatus) as unschedstatusDisplay ,apt.isnewpatient ,apt.ishygiene ,apt.op ,apt.provnum as dentist ,(select concat(fname, ' ', lname, ' ',suffix) from provider where provnum = apt.provnum) as dentistDisplay ,apt.provhyg as hygienist ,ifnull((select concat(fname, ' ', lname, ' ',suffix) from provider where provnum = apt.provhyg ),'') as hygienistDisplay ,CASE WHEN apt.aptstatus = 6 THEN date_add(curdate(), interval 99 year) ELSE apt.aptdatetime END as aptdatetime ,apt.pattern ,LENGTH(apt.pattern)*5 as duration ,apt.insplan1 ,(SELECT CONCAT(CASE WHEN d.itemname is null THEN c.carriername ELSE d.itemname END, CASE WHEN ip.groupnum <> '' THEN CONCAT(' (',ip.groupnum,')') ELSE '' END) FROM insplan ip INNER JOIN carrier c ON c.carriernum = ip.carriernum LEFT JOIN definition d ON d.defnum = c.carriergroupname WHERE ip.plannum = apt.insplan1) as insurance_plan1 ,apt.insplan2 ,(SELECT CONCAT(CASE WHEN d.itemname is null THEN c.carriername ELSE d.itemname END, CASE WHEN ip.groupnum <> '' THEN CONCAT(' (',ip.groupnum,')') ELSE '' END) FROM insplan ip INNER JOIN carrier c ON c.carriernum = ip.carriernum LEFT JOIN definition d ON d.defnum = c.carriergroupname WHERE ip.plannum = apt.insplan2) as insurance_plan2 ,SUM(pq_p.ProcFee) as TotalFees /* -- get estimate totals (for posterity) */ ,SUM(pq_p.DeductibleEst) + IFNULL(claimvisit.Deductible,0) as DeductibleEst ,IF(SUM(pq_p.insuranceEst) + IFNULL(claimvisit.insuranceEst,0) > SUM(pq_p.ProcFee), SUM(pq_p.ProcFee), SUM(pq_p.insuranceEst) + IFNULL(claimvisit.insuranceEst,0)) as InsuranceEst ,SUM(pq_p.WriteoffEst) + IFNULL(claimvisit.WriteOffEst,0) as WriteOffEst ,IF( ROUND(SUM(pq_p.ProcFee) - IFNULL(claimvisit.WriteOffEst,0) - SUM(pq_p.WriteoffEst) - IF(SUM(pq_p.insuranceEst) + IFNULL(claimvisit.insuranceEst,0) > SUM(pq_p.ProcFee), SUM(pq_p.ProcFee), SUM(pq_p.insuranceEst) + IFNULL(claimvisit.insuranceEst,0)),2) < 0 , 0 , ROUND(SUM(pq_p.ProcFee) - IFNULL(claimvisit.WriteOffEst,0) - SUM(pq_p.WriteoffEst) - IF(SUM(pq_p.insuranceEst) + IFNULL(claimvisit.insuranceEst,0) > SUM(pq_p.ProcFee), SUM(pq_p.ProcFee), SUM(pq_p.insuranceEst) + IFNULL(claimvisit.insuranceEst,0)),2) ) as CoPayEst /* -- get actuals */ ,SUM(pq_p.Deductible) + IFNULL(claimvisit.Deductible,0) as Deductible ,SUM(pq_p.insuranceCoverage) + IFNULL(claimvisit.InsuranceCoverage,0) as InsuranceCoverage ,SUM(pq_p.Writeoff) + IFNULL(claimvisit.WriteOff,0) as WriteOff ,ROUND(SUM(pq_p.ProcFee) - SUM(pq_p.Writeoff) - IFNULL(claimvisit.WriteOff,0) - IFNULL(claimvisit.InsuranceCoverage,0) - SUM(pq_p.insuranceCoverage),2) as CoPay /* -- get any adjustments and patient payments */ ,SUM(pq_p.Adjustments) + SUM(distinct IFNULL(adjvisit.visitadjamt,0)) as Adjustments ,SUM(pq_p.PatPaid) as patientPaid ,CASE WHEN apt.aptstatus = 2 THEN SUM(IFNULL(pq_p.InsurancePaid,0)) + IFNULL(claimvisit.InsurancePaid,0) ELSE 0 END as insurancePaidtoRinse ,CASE WHEN apt.aptstatus = 2 THEN SUM(IFNULL(pq_p.InsuranceOutstanding,0)) + IFNULL(claimvisit.InsOutstanding,0) ELSE 0 END as insurancePaidtoPatient_DeltaDental ,ROUND(SUM(pq_p.patientOwes) - (IFNULL(claimvisit.WriteOff,0) - IFNULL(claimvisit.InsuranceCoverage,0)) + SUM(distinct IFNULL(adjvisit.visitadjamt,0)),2) as patientOwes ,ROUND(SUM(CASE WHEN apt.aptdatetime < pq_p.datedue THEN (pq_p.ProcFee - pq_p.Writeoff - pq_p.insuranceCoverage) ELSE (pq_p.ProcFee - pq_p.baseest) END),2) as recallEst /* -- old get patient owes ,ROUND(SUM(pq_p.ProcFee) - SUM(pq_p.Writeoff) - IFNULL(claimvisit.WriteOff,0) - SUM(pq_p.insuranceCoverage) - IFNULL(claimvisit.InsuranceCoverage,0) - SUM(pq_p.PatPaid) + SUM(pq_p.Adjustments) + SUM(distinct IFNULL(adjvisit.visitadjamt,0)),2) as PatientOwesOld */ ,COUNT(DISTINCT CASE WHEN pq_p.ClaimStatus <> '' THEN pq_p.ClaimStatus END) as claim_count ,TRIM(LEADING ',' FROM CASE WHEN GROUP_CONCAT(DISTINCT pq_p.NoBillIns) = 1 THEN 'No Bill' ELSE (CASE WHEN COUNT(DISTINCT pq_p.ClaimStatus) > 1 THEN GROUP_CONCAT(DISTINCT CASE WHEN pq_p.ClaimStatus <> 'Estimate' THEN pq_p.ClaimStatus END) ELSE CONCAT(IFNULL(claimvisit.ClaimStatus, ''), IFNULL(GROUP_CONCAT(DISTINCT pq_p.ClaimStatus),'')) END) END) as claim_status ,TRIM(LEADING ',' FROM CONCAT(IFNULL(claimvisit.InsNote, ''), IFNULL(GROUP_CONCAT(DISTINCT pq_p.InsNote),''))) as InsNote ,CASE WHEN apt.aptstatus = 2 THEN SUM(IFNULL(pq_p.InsurancePaid,0)) + IFNULL(claimvisit.InsurancePaid,0) ELSE 0 END as InsurancePaid ,CASE WHEN apt.aptstatus = 2 THEN SUM(IFNULL(pq_p.InsuranceOutstanding,0)) + IFNULL(claimvisit.InsOutstanding,0) ELSE 0 END as InsuranceOutstanding_DeltaDental /* -- get patient owes check columns */ ,CASE WHEN apt.aptstatus = 2 THEN SUM(IFNULL(pq_p.InsuranceToRinse,0)) + IFNULL(claimvisit.InsuranceToRinse,0) ELSE 0 END as InsuranceToRinse ,SUM(pq_p.PatCCPayments) as PatCCPayments ,SUM(pq_p.PayPlan) as PayPlanPrincipal ,group_concat(DISTINCT pq_p.datedue) as datedue ,SUM(pq_p.baseest) as baseest ,CONCAT('[',GROUP_CONCAT('\"',IFNULL(pq_p.getCodeDesc,''),'\"'),']') as aptProcCodeArray ,CONCAT('[', GROUP_CONCAT(CONCAT( '{\"procCode\": \"',pq_p.ProcCode ,'\", \"procDesc\": \"', IFNULL(pq_p.getCodeDesc,'') ,'\", \"ProcPriority\": \"', IFNULL(pq_p.ProcPriority,99) ,'\", \"ProcLocation\": \"', IFNULL(pq_p.ProcLocation,'') ,'\", \"ProcCondition\": \"', IFNULL(pq_p.ProcCondition,'') ,'\", \"NoBillIns\": \"', IFNULL(pq_p.NoBillIns,0) ,'\"}')),']') as aptProcCodeObject from appointment apt left join appointmenttype atype on atype.appointmenttypenum = apt.appointmenttypenum LEFT JOIN plannedappt papt on apt.nextaptnum = papt.aptnum OR apt.aptnum = papt.aptnum LEFT JOIN (SELECT s.aptnum,s.nextaptnum,s.aptstatus FROM appointment s INNER JOIN plannedappt ON plannedappt.aptnum = s.nextaptnum WHERE s.aptstatus <> 6) capt ON capt.nextaptnum = apt.aptnum /*##### START JOINs >>> GET VISIT financials ######*/ /* ==== get visit level adjustments */ LEFT JOIN (SELECT patnum, procdate, SUM(COALESCE(adjamt,0)) as visitadjamt FROM adjustment WHERE procnum = 0 GROUP BY adjnum) adjvisit ON adjvisit.procdate = DATE(apt.aptdatetime) AND adjvisit.patnum = apt.patnum /* ==== get procedure level financial details */ INNER JOIN ( SELECT plog.patnum as ProcPatNum ,CASE WHEN plog.aptnum = '' THEN plog.plannedaptnum ELSE plog.aptnum END as ProcAptNum ,plog.procstatus as ProcStatus ,code.proccode as ProcCode ,code.descript as CodeDescipt ,code.laymanterm as LaymanTerm ,CASE WHEN code.laymanterm = '' THEN code.descript ELSE code.laymanterm END as getCodeDesc ,CASE WHEN code.treatarea = 1 THEN CONCAT(plog.toothnum, ': ', plog.surf) WHEN code.treatarea = 2 THEN plog.toothnum WHEN code.treatarea = 3 THEN '' WHEN code.treatarea = 4 AND code.areaalsotoothrange = '' THEN plog.surf WHEN code.treatarea = 4 AND code.areaalsotoothrange <> '' THEN CONCAT(plog.surf, ': ', plog.toothrange) WHEN code.treatarea = 5 THEN plog.surf WHEN code.treatarea = 6 THEN plog.surf WHEN code.treatarea = 7 THEN plog.toothrange ELSE '' END as ProcLocation ,(SELECT itemname FROM definition WHERE defnum = plog.dx) as ProcCondition ,(SELECT itemname FROM definition WHERE defnum = plog.priority) as ProcPriority ,plog.procfee as ProcFee /* get the pre-visit estimated costs */ ,ROUND(IF(IFNULL(claimproc.InsuranceEst,0) >= plog.procfee, 0, IFNULL(claimproc.DeductibleEst,0)),2) as deductibleEst ,ROUND(IF(IFNULL(claimproc.InsuranceEst,0) >= plog.procfee, plog.procfee , IFNULL(claimproc.InsuranceEst,0)),2) as insuranceEst /* WriteoffsEst, in this case, is a sum of all estimated discounts */ ,plog.DiscountPlanAmt as discountPlanAmt ,plog.Discount as discount ,(CASE WHEN plog.DiscountPlanAmt = 0 THEN ROUND(IF(IFNULL(claimproc.InsuranceEst,0) >= plog.procfee, 0, IFNULL(claimproc.WriteoffEst,0)),2) ELSE plog.DiscountPlanAmt END) + plog.Discount as writeoffEst ,plog.procfee - ((CASE WHEN plog.DiscountPlanAmt = 0 THEN ROUND(IF(IFNULL(claimproc.InsuranceEst,0) >= plog.procfee, 0, IFNULL(claimproc.WriteoffEst,0)),2) ELSE plog.DiscountPlanAmt END) + plog.Discount) - ROUND(IF(IFNULL(claimproc.InsuranceEst,0) >= plog.procfee, plog.procfee , IFNULL(claimproc.InsuranceEst,0)),2) as patientEst /* get current costs. Considers previsit (estimate), post visit (claim sent/not received), and received/paid. Note that for discount plans/memberships, the savings is accounted for in the writeoff pre-visit and then adjustments after the procedure is marked complete */ ,ROUND(IFNULL(claimproc.Deductible,0),2) as deductible ,ROUND(IFNULL(claimproc.InsuranceCoverage,0),2) as insuranceCoverage ,CASE WHEN plog.DiscountPlanAmt > 0 AND plog.procstatus = 1 THEN plog.DiscountPlanAmt ELSE IFNULL(claimproc.Writeoff,0) END as writeoff ,ROUND(IFNULL(adjproc.adjamt,0),2) as adjustments /* Get patient payments or payment plans made/setup at the procedure level */ ,ROUND(IFNULL(patpayproc.patpay,0) + SUM(COALESCE(payplan.principal,0)),2) as PatPaid ,ROUND(SUM(COALESCE(payplan.principal,0)),2) as PayPlan ,ROUND(IFNULL(patpayproc.patpay,0)) as PatCCPayments /* Get patient owed amount at procedure level - this considers full claim lifecycle */ ,ROUND(plog.procfee - IFNULL(claimproc.InsuranceToRinse,0) - (CASE WHEN plog.DiscountPlanAmt > 0 AND plog.procstatus = 1 THEN plog.DiscountPlanAmt ELSE IFNULL(claimproc.Writeoff,0) END) + IFNULL(adjproc.adjamt,0) - (IFNULL(patpayproc.patpay,0) + SUM(COALESCE(payplan.principal,0))) - IFNULL(claimproc.insurancePayPlan,0),2) as patientOwes ,ROUND(IFNULL(claimproc.InsurancePaid,0),2) as InsurancePaid ,ROUND(IFNULL(claimproc.InsOutstanding,0),2) as InsuranceOutstanding /* Insurance Coverage is the estimated amount Rinse was expected to get. InsOutstanding is the amount expected less the amount paid. ,ROUND(IFNULL(claimproc.InsuranceCoverage,0) - IFNULL(claimproc.InsurancePaid,0),2) as InsOutstanding */ ,ROUND(IFNULL(claimproc.insurancePayPlan,0),2) as insurancePayPlan ,ROUND(IFNULL(claimproc.InsuranceToRinse,0),2) as insuranceToRinse ,IF(recall.datedue IS NULL,'', recall.datedue) as datedue ,ROUND(IFNULL(claimproc.baseest,0),2) as baseest ,IFNULL(claimproc.ClaimCount,0) as ClaimCount ,claimproc.ClaimStatus ,claimproc.InsNote ,claimproc.NoBillIns ,claimproc.CarrierGroupNum as CarrierGroup /* Get any insurance or membership display name, priority is membership, then carrier group, then carrier name */ ,CASE WHEN plog.DiscountPlanAmt > 0 THEN (SELECT dp.Description FROM DiscountPlanSub dps INNER JOIN discountplan dp ON dp.DiscountPlanNum = dps.DiscountPlanNum AND plog.procdate BETWEEN dps.DateEffective AND dps.DateTerm WHERE dps.patnum = plog.patnum) WHEN claimproc.CarrierGroupName IS NOT NULL THEN claimproc.CarrierGroupName ELSE claimproc.CarrierName END as carrierName FROM procedurelog plog INNER JOIN procedurecode code ON code.codenum = plog.codenum LEFT JOIN (select r.patnum ,r.recalltypenum ,rt.description ,rt.procedures ,r.datedue from recalltype rt inner join recall r on rt.recalltypenum = r.recalltypenum group by r.patnum, r.recalltypenum ) recall ON recall.patnum = plog.patnum AND find_in_set(code.proccode, recall.procedures) /* ==== get procedure level adjustments */ LEFT JOIN (SELECT procnum, SUM(COALESCE(adjamt,0)) as adjamt FROM adjustment WHERE procnum <> 0 GROUP BY procnum) adjproc ON adjproc.procnum = plog.procnum /* ==== get procedure level payplan */ LEFT JOIN (SELECT procnum, SUM(COALESCE(principal,0)) as principal FROM payplancharge WHERE chargetype = 1 GROUP BY procnum) payplan ON payplan.procnum = plog.procnum /* ==== get procedure level payments matched on procedure CODE */ LEFT JOIN (SELECT procnum, SUM(COALESCE(splitamt,0)) as patpay FROM paysplit WHERE procnum <> 0 GROUP BY procnum) patpayproc ON patpayproc.procnum = plog.procnum AND patpayproc.procnum <> IFNULL(payplan.procnum,0) /* ==== get procedure level payments matched on procedure DATE */ LEFT JOIN (SELECT patnum, procdate, SUM(COALESCE(splitamt,0)) as patpay FROM paysplit WHERE procnum = 0 GROUP BY patnum, procdate) patpayvisit ON patpayvisit.procdate = plog.procdate AND patpayvisit.patnum = plog.patnum /* ==== get procedure level claim info */ LEFT JOIN (SELECT c.procnum as ProcNum ,GROUP_CONCAT(DISTINCT carriergroupname) as CarrierGroupNum ,GROUP_CONCAT(DISTINCT defcar.ItemName) as CarrierGroupName ,GROUP_CONCAT(DISTINCT CarrierName) as CarrierName ,GROUP_CONCAT(DISTINCT c.status) as ClaimStatusCode ,GROUP_CONCAT(DISTINCT CASE WHEN c.status = 0 THEN 'Submitted' WHEN c.status = 1 THEN 'Received' WHEN c.status = 2 THEN 'PreAuth' WHEN c.status = 3 THEN 'Adjustment' WHEN c.status = 4 THEN 'Supplemental' WHEN c.status = 6 THEN 'Estimate' ELSE 'Other' END) as ClaimStatus /* --- get claim level estimates only (used for posterity) */ ,SUM(CASE WHEN c.dedestoverride <> -1 THEN COALESCE(c.dedestoverride,0) WHEN c.dedest <> -1 THEN COALESCE(c.dedest,0) ELSE c.dedapplied END) as DeductibleEst /* -- Use inspayest as the way to track outsanding amount from delta dental >> see InsOutstanding -- InsEstTotal (and override) to track the original insurance estimates */ ,SUM(COALESCE(CASE WHEN c.InsEstTotalOverride <> -1 THEN c.InsEstTotalOverride ELSE c.insesttotal END,0)) as insuranceEst ,SUM((COALESCE(CASE WHEN c.writeoffestoverride <> -1 THEN c.writeoffestoverride WHEN c.writeoffest <> -1 THEN c.writeoffest ELSE 0 END,0))) as WriteoffEst /* -- get check columns */ /* --- get claim level actuals */ ,SUM(CASE WHEN c.status IN (0,6) THEN (CASE WHEN c.dedestoverride <> -1 THEN COALESCE(c.dedestoverride,0) WHEN c.dedest <> -1 THEN COALESCE(c.dedest,0) ELSE 0 END) WHEN c.status = 1 THEN COALESCE(c.dedapplied,0) ELSE 0 END) as Deductible ,SUM(CASE WHEN c.status IN (0,6) THEN (CASE WHEN c.writeoffestoverride <> -1 THEN COALESCE(c.writeoffestoverride,0) WHEN c.writeoffest <> -1 THEN COALESCE(c.writeoffest ,0) ELSE c.writeoff END) WHEN c.status = 1 THEN COALESCE(c.writeoff,0) ELSE 0 END) as Writeoff ,SUM(CASE WHEN c.status IN (0,6) THEN (CASE WHEN c.InsEstTotalOverride <> -1 THEN COALESCE(c.InsEstTotalOverride,0) ELSE COALESCE(c.insesttotal,0) END) WHEN c.status = 1 THEN COALESCE(c.InsPayEst,0) ELSE 0 END) as InsuranceCoverage ,SUM(CASE WHEN c.status IN (0,6) THEN (CASE WHEN c.InsEstTotalOverride <> -1 THEN COALESCE(c.InsEstTotalOverride,0) ELSE COALESCE(c.insesttotal,0) END) WHEN c.status = 1 THEN COALESCE(c.inspayamt,0) ELSE 0 END) as InsuranceToRinse ,SUM((COALESCE(c.inspayamt,0))) as InsurancePaid ,SUM((COALESCE(c.baseest,0))) as baseest /* if insurance paid something then assume the claim is paid and no insurance is outstanding */ ,SUM(CASE WHEN c.status IN (0,6) THEN 0 ELSE (CASE WHEN c.inspayamt > 0 THEN 0 ELSE (COALESCE(c.InsPayEst,0) - COALESCE(c.inspayamt,0)) END) END) as InsOutstanding ,SUM(payplan.principal) as insurancePayPlan /* --- get claim level notes */ ,COUNT(DISTINCT c.claimnum) as ClaimCount ,GROUP_CONCAT(DISTINCT c.nobillins) as NoBillIns ,GROUP_CONCAT(DISTINCT CASE WHEN c.estimatenote <> '' THEN c.estimatenote END) as InsNote FROM claimproc c INNER JOIN procedurelog p ON p.procnum = c.procnum INNER JOIN insplan ip ON ip.plannum = c.plannum INNER JOIN carrier car ON car.carriernum = ip.carriernum LEFT JOIN (SELECT payplannum, SUM(COALESCE(principal,0)) as principal FROM payplancharge WHERE chargetype = 1 GROUP BY payplannum) payplan ON payplan.payplannum = c.payplannum LEFT JOIN definition defcar ON defcar.defnum = car.carriergroupname WHERE c.procnum <> 0 AND c.status NOT IN (2,9) AND p.procstatus IN (1,2) GROUP BY c.procnum) claimproc ON claimproc.ProcNum = plog.procnum WHERE plog.procstatus in (1,2) GROUP BY plog.procnum) pq_p ON pq_p.ProcAptNum = apt.aptnum /* ----- get any misc claims that are not assigned to a proccode -- metlife example */ LEFT JOIN ( SELECT c.patnum ,c.procdate ,c.procnum as ProcNum ,GROUP_CONCAT(DISTINCT carriergroupname) as CarrierGroupNum ,GROUP_CONCAT(DISTINCT CarrierName) as CarrierName ,GROUP_CONCAT(DISTINCT c.status) as ClaimStatusCode ,GROUP_CONCAT(DISTINCT CASE WHEN c.status = 0 THEN 'Submitted' WHEN c.status = 1 THEN 'Received' WHEN c.status = 2 THEN 'PreAuth' WHEN c.status = 3 THEN 'Adjustment' WHEN c.status = 4 THEN 'Supplemental' WHEN c.status = 6 THEN 'Estimate' ELSE 'Other' END) as ClaimStatus /* --- get claim level estimates only (used for posterity) */ ,SUM(CASE WHEN c.dedestoverride <> -1 THEN COALESCE(c.dedestoverride,0) WHEN c.dedest <> -1 THEN COALESCE(c.dedest,0) ELSE c.dedapplied END) as DeductibleEst /* -- Use inspayest as the way to track outsanding amount from delta dental >> see InsOutstanding -- InsEstTotal (and override) to track the original insurance estimates */ ,SUM(COALESCE(CASE WHEN c.InsEstTotalOverride <> -1 THEN c.InsEstTotalOverride ELSE c.insesttotal END,0)) as insuranceEst ,SUM((COALESCE(CASE WHEN c.writeoffestoverride <> -1 THEN c.writeoffestoverride WHEN c.writeoffest <> -1 THEN c.writeoffest ELSE 0 END,0))) as WriteoffEst /* -- get check columns */ /* --- get claim level actuals */ ,SUM(CASE WHEN c.status IN (0,6) THEN (CASE WHEN c.dedestoverride <> -1 THEN COALESCE(c.dedestoverride,0) WHEN c.dedest <> -1 THEN COALESCE(c.dedest,0) ELSE 0 END) WHEN c.status = 1 THEN COALESCE(c.dedapplied,0) ELSE 0 END) as Deductible ,SUM(CASE WHEN c.status IN (0,6) THEN (CASE WHEN c.writeoffestoverride <> -1 THEN COALESCE(c.writeoffestoverride,0) WHEN c.writeoffest <> -1 THEN COALESCE(c.writeoffest ,0) ELSE c.writeoff END) WHEN c.status = 1 THEN COALESCE(c.writeoff,0) ELSE 0 END) as Writeoff ,SUM(CASE WHEN c.status IN (0,6) THEN (CASE WHEN c.InsEstTotalOverride <> -1 THEN COALESCE(c.InsEstTotalOverride,0) ELSE COALESCE(c.insesttotal,0) END) WHEN c.status = 1 THEN COALESCE(c.InsPayEst,0) ELSE 0 END) as InsuranceCoverage ,SUM(CASE WHEN c.status IN (0,6) THEN (CASE WHEN c.InsEstTotalOverride <> -1 THEN COALESCE(c.InsEstTotalOverride,0) ELSE COALESCE(c.insesttotal,0) END) WHEN c.status = 1 THEN COALESCE(c.inspayamt,0) ELSE 0 END) as InsuranceToRinse ,SUM((COALESCE(c.inspayamt,0))) as InsurancePaid /* if insurance paid something then assume the claim is paid and no insurance is outstanding */ ,SUM(CASE WHEN c.inspayamt > 0 THEN 0 ELSE COALESCE(c.InsPayEst,0) - COALESCE(c.inspayamt,0) END) as InsOutstanding /* --- get claim level notes */ ,COUNT(DISTINCT c.claimnum) as ClaimCount ,GROUP_CONCAT(DISTINCT c.nobillins) as NoBillIns ,GROUP_CONCAT(DISTINCT CASE WHEN c.estimatenote <> '' THEN c.estimatenote END) as InsNote FROM claimproc c INNER JOIN insplan ip ON ip.plannum = c.plannum INNER JOIN carrier car ON car.carriernum = ip.carriernum WHERE c.procnum = 0 AND c.status NOT IN (2,3,9) GROUP BY c.patnum, c.procdate) claimvisit ON (claimvisit.patnum = apt.patnum AND DATE_FORMAT(claimvisit.procdate, '%Y-%d-%M')= DATE_FORMAT(apt.aptdatetime,'%Y-%d-%M')) /*##### END JOINS >>> GET VISIT FINANCIALS ######*/ where apt.aptstatus in (1,2,3,5,6) and capt.aptnum is null /*and apt.patnum = 1*/ and apt.aptnum = <aptnum> group by apt.aptnum order by aptdatetime asc, papt.itemorder asc"
}
