We are currently updating the OP Help Center content for the release of OP 19, which is a member of the certified OP 14 family of products. OP 19’s official version is 14.19.1, which you may see in your software (such as in Help > About) and in the Help Center tabs labeled 14.19. You may also notice that the version number in content and videos may not match the version of your software, and some procedural content may not match the workflow in your software. We appreciate your patience and understanding as we make these enhancements.

SQL: Find Individual Line Items Out of Balance

About

This SQL finds individual line items that are over/underpaid when the total claim is in balance. A practice whose accounts are correct (at least in this way) will get no results when this report is run.

A sample image of this SQL report run in the Database Viewer is shown below:

Below are two examples that show Individual Line Items Out of Balance.

Example 1

This example shows a patient balance of 3.11, but there is also an insurance credit of 3.11. Since both items are on the same claim (in fact, the same line item), it indicates that nothing is owed. This patient has no balance according to the front of the chart. However, buried deep in the line items, there is a patient balance. If you correct the claim (essentially removing the 2nd "4" line), everything will be corrected again.

Example 2

This example shows an item where the patient owes $10 on the 99213 line item, but has a credit of $10 on the 90772 line item.  Overall, the patient doesn't owe anything, but if you run a report for "What was I paid for 99213" you will receive bad results. Back out the patient payment on 90772, reapply it to 99213, and you're good.

The field "invoiceno" is the claim number. You can jump right to it if you enter $[claimnumber] like "$145384" in the "find a patient field."

Code

To highlight and copy the code below to your clipboard, simply click the Copy button.

with myAging as 
(
select patno,fname,lname, sum(copaybalance) as pat_owed, min(e_dor) as eff_pat_dor, guarantor_id
from
(
select patno, fname,lname, trnsxno, date1, cptcode, copaybalance, max(effective_dor) as e_dor, guarantor_id from
(
select at1.*, at2.date1 as dor, at2.cptcode as orig_cpt, case when at2.date1 is null then at1.date1 else at2.date1 end as effective_dor, fname,lname, guarantor_id from
 (select patno, invoiceno, trnsxno, date1, cptcode, copaybalance from archive_Transactions where archive_flag = 1 and cptcode not in ('1','2','4','RETCHK') and copaybalance >= 0.01)  at1
left outer join (Select * from archive_transactions where archive_flag = 1 and cptcode not in ('1','2')) at2 on at2.txnopaid = at1.trnsxno
inner join register on register.patno = at1.patno
order by patno
)
group by patno, fname,lname, trnsxno, date1, cptcode, copaybalance, guarantor_id
) group by patno,fname,lname, guarantor_id
) 
select myaging.*, register.copaybal from myaging
left outer join register on register.patno = myaging.patno
where copaybal < 0.01