#Open dental schedule code

WHERE appointment.AptDateTime LIKE AptStatus != 6 /*Planned*/ INNER JOIN patient p ON p.PatNum=B.PatNum LEFT JOIN payplan pplan ON pplan.PayPlanNum=ppc.PayPlanNum WHEN pplan.PlanNum=0 THEN ppc.Principal+ppc.Interest ELSE 0 END) #if debit and a patient plan, add principal and interest (CASE WHEN ppc.ChargeType != 0 THEN -ppc.Principal #if credit, subtract the principal SELECT 'PayPlan2' AS TranType, ppc.PatNum AS PatNum, *Version 2: Payment plan charges for those using line-item account for payplans*/ SELECT 'PayPlan1' AS TranType,pp.PatNum AS PatNum,pp.PayPlanDate AS TranDate, *Version 1: Payment plan principal for the entire office history for those using the original payplans accounting*/

WHERE cp.Status IN (1,4,5,7)/*received,supplemental,CapClaim, or CapComplete*/ (CASE WHEN cp.payplannum = 0 THEN -cp.InsPayAmt ELSE 0 END)-cp.Writeoff AS TranAmount SELECT 'InsPay' AS TranType,cp.PatNum AS PatNum,cp.DateCp AS TranDate, *Claim payments for the entire office history*/ SELECT 'Adj' AS TranType,a.PatNum AS PatNum,a.AdjDate AS TranDate, *Get the adjustment dates and amounts for the entire office history*/ (CASE WHEN IN (1,3) THEN (CASE WHEN ps.PayPlanNum=0 THEN -ps.SplitAmt ELSE 0 END) ELSE -ps.SplitAmt END) AS TranAmount

SELECT 'Pay' AS TranType,ps.PatNum AS PatNum,ps.DatePay AS TranDate, *Paysplits for the entire office history*/ Pl.ProcFee*(pl.UnitQty+pl.BaseUnits) AS TranAmount SELECT 'Fee' AS TranType,pl.PatNum AS PatNum,pl.ProcDate AS TranDate, *Get the completed procedure dates and charges for the entire office history*/ *Get Patient level charges and credits*/ *Get the family level charges, credits, and ins estimates*/ INNER JOIN patient ON appointment.PatNum=patient.PatNum SET ValueString FROM preference WHERE prefName LIKE 'PayPlansVersion'),1) ДЇORMAT(IFNULL(aging.PatBal,0),2) AS PatEstBalance *Change dates between the ' ' in format 'YYYY-mm-dd'*/ *9 Estimated balances for patients with an appointment on a specific day*/ The account balances for all patients with an appointment on a specific day. WHERE pl.ProcStatus=1 - Treatment Planned ,(pl.ProcFee * (pl.BaseUnits + pl.UnitQty)) AS ProcFee

,SUBSTRING(p.MiddleI, 2, LENGTH(p.MiddleI)-1) ,IF( - Display middle initial if we have one ,SUBSTRING(p.FName, 2, LENGTH(p.FName)-1) ,SUBSTRING(p.Preferred, 2, LENGTH(p.Preferred)-1) ,SUBSTRING(p.LName, 2, LENGTH(p.LName)-1)

#Open dental schedule code

*Query code written/modified: :MattG, :MattG*/Д¬ONCAT( - Build a name string like OD's default *2 All treatment planned procedures, ordered by patient*/ All treatment planned procedures, ordered by patient - See also #50 & #56