line item payments

[teamhowey]

On patient accounts (especially family accounts), for every procedure billed out, is there a way to show the amount billed out, what has been paid for that procedure, and what is still outstanding for that procedure? Instead of a chronilogical list of events, ie a list of procedures billed out and then a list of payments below. This will show where balances are derived from.

[jordansparks]

Nathan wrote a report a few months ago that does that, grouped by date. If OD did it rigorously, it would be true open item accounting. We have considered adding the "report" as an audit page or some sort of alternate view in the Account module.

[teamhowey]

Is this report available?

[teamhowey]

What about a report grouped by claim number? Or atleast a view of account by claim number?

[nathansparks]

Well, it is grouped by procedure date and by patient in the family (you must provide the guarantors PatNum, which you can show on title bar of Open Dental) so grouping by claim is not needed, usually claims are for one procedure date and always for one patient, try this and comment back if it is not what you need.
Nathan

/*455 Special Statement grouped by procedure date*/
-- Special Statement shows transactions grouped by procedure date, helps to understand a families account
SET @GPatNum='7018', @Detail=False /*Set to True or False to show procedure level rows*/;
-- Run by putting in Guarantor's Patient Number and setting @Detail to True if you want procedure level detail
-- it would be very unusual, but you could limit to a given date range if you like, and would NOT bring balance forward
SET @FromDate='1900-01-01' , @ToDate='2099-12-31';
-- Accounts for payment plans and capitation (HMOs) now too
-- Show transactions by service date, then sum by date
DROP TABLE IF EXISTS tmp1, tmp2, tmp3;
Set @Pos=0;/*counter for manipulation later*/
CREATE TABLE tmp1(FName VARCHAR(15),TranType VARCHAR(11),ProcDate DATE,Description VARCHAR(20),PatNum BIGINT,Fee DOUBLE,WriteOff DOUBLE,InsPayAmt DOUBLE,OutIns DOUBLE,Adjust DOUBLE,PatPaid DOUBLE,PatPort DOUBLE,ToBalance DOUBLE,RunningBal DOUBLE);
-- insert all completed procedures and any associated insurance claimprocs
INSERT INTO tmp1
SELECT p.FName, 'Charge' AS TranType,pl.ProcDate,(CASE WHEN pl.ToothNum<1 THEN pc.ProcCode ELSE CONCAT(pc.ProcCode, '-',pl.ToothNum) END) AS Description,
pl.PatNum, pl.ProcFee*(pl.UnitQty+pl.BaseUnits) Fee,
SUM(cp.Writeoff),SUM(cp.InsPayAmt), 0 AS OutIns, 0 AS Adjust,
IFNULL((SELECT SUM(ps.SplitAmt) FROM paysplit ps WHERE ps.ProcNum=pl.ProcNum AND ps.PayPlanNum=0),0) AS PatPaid,
NULL AS 'PatPort', NULL AS 'ToBalance', NULL AS RunningBal
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum AND cp.Status IN(1,4, 5, 7) /* received, supplemental, capclaim,capreceived*/
WHERE pl.ProcStatus=2 AND p.Guarantor=@GPatNum /*(SELECT g.Guarantor FROM patient g WHERE g.PatNum=@GPatNum)*/
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
ORDER BY PatNum, ProcDate;
-- insurance payments by total
INSERT INTO tmp1
SELECT p.FName, 'Other Payments' AS TranType, cp.ProcDate, 'Ins Pay by Total' AS Description, cp.PatNum, 0 AS Fee, SUM(cp.Writeoff) AS Writeoff, SUM(cp.InsPayAmt) AS InsPayAmt,0 AS OutIns, 0 AS Adjust, 0 AS PatPaid, NULL AS 'PatPort', NULL AS ToBalance, NULL AS RunningBal
FROM claimproc cp
INNER JOIN patient p ON cp.PatNum=p.PatNum
WHERE cp.Status IN(1,4) /*rec, suppl*/ AND p.Guarantor=@GPatNum /*(SELECT g.Guarantor FROM patient g WHERE g.PatNum=@GPatNum)*/ AND cp.ProcNum=0
AND cp.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY cp.PatNum, cp.ProcDate;
INSERT INTO tmp1
-- Adjustments
SELECT FName, 'DaySumAdj' AS TranType, a.ProcDate, 'Adjustments' AS Description, a.PatNum, 0 AS FEE, 0 AS Writeoff, 0 AS InsPayAmt,0 AS OutIns, SUM(AdjAmt) AS Adjust,0 AS 'PatPaid', NULL AS 'PatPort', NULL AS ToBalance, NULL AS RunningBal
FROM adjustment a
INNER JOIN patient p on p.PatNum=a.PatNum
WHERE p.Guarantor=@GPatNum /*(SELECT g.Guarantor FROM patient g WHERE g.PatNum=@GPatNum)*/
AND a.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY a.PatNum, a.ProcDate
UNION ALL
-- Remaining Patient Payments
SELECT FName, 'DaySumPay' AS TranType, ps.ProcDate, 'Pat Pay by Total' AS Description, p.PatNum, 0 AS FEE, 0 AS Writeoff, 0 AS InsPayAmt,0 AS OutIns, 0 AS Adjust, SUM(SplitAmt) AS PatPaid, NULL AS 'PatPort', NULL AS ToBalance, NULL AS RunningBal
FROM paysplit ps
INNER JOIN patient p on p.PatNum=ps.PatNum
WHERE p.Guarantor=@GPatNum AND ps.ProcNum=0 AND ps.PayPlanNum=0
AND ps.ProcDate BETWEEN @FromDate AND @ToDate
/*(SELECT g.Guarantor FROM patient g WHERE g.PatNum=@GPatNum)*/
GROUP BY ps.PatNum, ps.ProcDate
UNION ALL
-- Outstanding Claims
SELECT p.FName, 'DayOutIns' AS TranType, cp.ProcDate, 'Day Outstand Ins' AS Description, cp.PatNum, 0 AS Fee, 0 AS Writeoff,0 AS InsPayAmt,(SUM(cp.Writeoff) + SUM(cp.InsPayEst)) AS OutIns, 0 AS Adjust, 0 AS PatPaid, NULL AS 'PatPort', NULL AS ToBalance, NULL AS RunningBal
FROM claimproc cp
INNER JOIN patient p ON cp.PatNum=p.PatNum
WHERE cp.Status IN(0) /*sent*/ AND p.Guarantor=@GPatNum /*(SELECT g.Guarantor FROM patient g WHERE g.PatNum=@GPatNum)*/
AND cp.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY cp.PatNum, cp.ProcDate
UNION ALL
/*Payment Plans*/
SELECT p.FName, 'PPComplete' AS TranType, pp.PayPlanDate as ProcDate, 'Payment Plan' AS Description, p.PatNum, 0 AS Fee, 0 AS Writeoff,0 AS InsPayAmt,0 AS OutIns,-pp.CompletedAmt AS Adjust, 0 AS PatPaid, NULL AS 'PatPort', NULL AS ToBalance, NULL AS RunningBal
FROM payplan pp
INNER JOIN patient p ON pp.PatNum=p.PatNum
WHERE p.Guarantor=@GPatNum
GROUP BY pp.PatNum, pp.PayPlanDate;
-- Add totals by date
CREATE TABLE tmp2
SELECT * FROM tmp1
UNION ALL
SELECT FName, 'Total' AS TranType, ProcDate, ' Total For Date' AS Description, PatNum, SUM(Fee) AS Fee, SUM(Writeoff) AS Writeoff, SUM(InsPayAmt) AS InsPayAmt, SUM(OutIns) AS OutIns,SUM(Adjust) AS Adjust, SUM(PatPaid) AS PatPaid, NULL AS 'PatPort', NULL AS ToBalance, NULL AS RunningBal
FROM tmp1 GROUP BY PatNum, ProcDate;
UPDATE tmp2
SET ToBalance=(IFNULL(Fee,0)-IFNULL(Writeoff,0)-IFNULL(InsPayAmt,0)-IFNULL(PatPaid,0)+IFNULL(Adjust,0))
WHERE TranType = 'Total' AND (IFNULL(Fee,0)-IFNULL(Writeoff,0)-IFNULL(InsPayAmt,0)-IFNULL(PatPaid,0)+IFNULL(Adjust,0))<>0;
UPDATE tmp2
SET PatPort=(IFNULL(Fee,0)-IFNULL(Writeoff,0)-IFNULL(InsPayAmt,0)-IFNULL(OutIns,0)) WHERE TranType = 'Total' AND Fee<>0;
DROP TABLE IF EXISTS tmp1;
-- Now do running totals, using duplicate table
CREATE TABLE tmp1 SELECT * FROM tmp2;
UPDATE tmp2 SET RunningBal=FORMAT((SELECT SUM(tmp1.ToBalance) FROM tmp1 WHERE tmp1.ProcDate<=tmp2.ProcDate AND tmp1.PatNum=tmp2.PatNum AND tmp1.Description=' Total For Date'),2)
WHERE tmp2.Description=' Total For Date';
-- Total by Patient, use fake date to indicate we want it blank later, must be large date to sort to bottom
INSERT INTO tmp2
SELECT '' AS FName, 'Total Ind' AS TranType, '3000-01-01' AS ProcDate, CONCAT(' Tot ', FName) AS Description, PatNum,
SUM(Fee) AS Fee, SUM(Writeoff) AS Writeoff,
SUM(InsPayAmt) AS InsPayAmt, SUM(OutIns) AS OutIns,SUM(Adjust) AS Adjust, SUM(PatPaid) AS PatPaid,
SUM(PatPort) AS PatPort, SUM(ToBalance) AS ToBalance, SUM(ToBalance) AS RunningBal
FROM tmp1 WHERE tmp1.Description=' Total For Date'
GROUP BY PatNum;
-- add Blank Row after each Patient in Family
INSERT INTO tmp2
SELECT '' AS FName, 'Total IndB' AS TranType, '3000-01-01' AS ProcDate, '' AS Description, PatNum,
NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
FROM tmp1 WHERE tmp1.Description=' Total For Date'
GROUP BY PatNum;
-- add family total
INSERT INTO tmp2
SELECT 'Family' AS FName, 'Total Fam' AS TranType, '3000-01-01' AS ProcDate, ' Total OverAll' AS Description, '999999999999' AS PatNum,
SUM(Fee) AS Fee, SUM(Writeoff) AS Writeoff,
SUM(InsPayAmt) AS InsPayAmt, SUM(OutIns) AS OutIns,SUM(Adjust) AS Adjust, SUM(PatPaid) AS PatPaid,
SUM(PatPort) AS PatPort, SUM(ToBalance) AS ToBalance, SUM(ToBalance) AS RunningBal
FROM tmp1 WHERE tmp1.Description=(' Total For Date');
-- now add position record to allow us to eliminate date repetition in detail version
CREATE TABLE tmp3
SELECT @pos:=@pos+1 AS RecNum,FName,TranType,ProcDate,Description,PatNum,Fee,WriteOff,InsPayAmt,OutIns,Adjust,PatPaid,PatPort,ToBalance,RunningBal
FROM tmp2
ORDER BY PatNum, ProcDate, TranType;
DROP TABLE IF EXISTS tmp2;
-- copy back to temp2, we will need two tables ot avoid self reference during update
CREATE TABLE tmp2 SELECT * FROM tmp3;
-- Now choose which results to display, we add a column 'Detail' to allow a virtual if statement
ALTER TABLE tmp3 ADD COLUMN Detail BOOL;
UPDATE tmp3 SET Detail=@Detail;
UPDATE tmp3, tmp2 SET tmp3.ProcDate='3000-01-01' WHERE tmp3.RecNum=tmp2.RecNum+1 AND tmp3.ProcDate=tmp2.ProcDate AND Detail=True;
DELETE FROM tmp3 WHERE TranType NOT LIKE 'Total%' AND Detail=False;
-- output results, hiding useless columns, doing some formatting
SELECT /*RecNum, */
FName AS Name,
/*TranType,*/
(CASE WHEN ProcDate='3000-01-01' THEN '' ELSE DATE_FORMAT(ProcDate, '%m/%d/%Y') END) AS Date,
Description,
/*PatNum,*/
FORMAT(Fee,2) AS Fee,
FORMAT(WriteOff,2) AS WriteOff,
FORMAT(InsPayAmt,2) AS InsPaid,
FORMAT(OutIns,2) AS OutIns,
FORMAT(Adjust,2) AS Adjust,
FORMAT(PatPaid,2) AS PatPaid,
FORMAT(PatPort,2) AS PatPort,
(CASE WHEN TranType='Total Fam' THEN ' >'
WHEN TranType='Total Ind' THEN ' >'
ELSE FORMAT(ToBalance,2) END) AS ToBalance,
(CASE WHEN TranType='Total Fam' THEN CONCAT('Tot: $', FORMAT((RunningBal),2))
WHEN TranType='Total Ind' THEN FORMAT(IFNULL(RunningBal,0),2)
WHEN ISNULL(ToBalance) THEN NULL
ELSE FORMAT(RunningBal,2) END) AS RunningBal FROM tmp3
;
DROP TABLE IF EXISTS tmp1, tmp2, tmp3;