We are currently updating the OP Help Center content for the release of OP 14.19 or OP 19. OP 19 is a member of the certified OP 14 family of products (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: Charges and Payments with Notes

About

This report shows all CPTs and charges, payments and adjustments, claim notes and adjustment codes, diagnoses, and age of patient at the time of service for a date of service range that you specify.

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

Caveats

  • The column MONTHS_OLD gives the patient’s age in months on the date of service.
  • INVOICENO is the claim number associated with the charge.
  • PENDING is the balance on the claim (either patient-side or insurance-side) once there has been at least one payment or adjustment on the line item.  A PENDING of 0 means the claim has been completely paid/adjusted and is closed.

Code

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

select at1.patno, birthdat, (datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,date1))) as months_old, at1.invoiceno, at1.date1, at1.cptcode, at1.mod1, at1.mod2, at1.ins_carrier_code, at1.ins_carrier_code_other, at1.charge, at1.units, at1.trnsxno, at1.pos, at1.dxcode1, at1.dxcode2, at1.dxcode3, at1.dxcode4, at2.p as ins_paid, at2.c as pt_paid,  at2.p +  at2.c as total_paid,at2.ins_adj, at2.pt_adj, round(at1.charge-at2.p-at2.c-at2.ins_adj-at2.pt_adj,2) as pending,
claim_note , ins_adj_codes

from
(select date1, patno, invoiceno, cptcode,mod1, mod2,  ins_carrier_code, ins_carrier_code_other, charge, trnsxno, pos, units, dxcode1, dxcode2, dxcode3, dxcode4 from archive_transactions where archive_flag = 1 and date1 between :Start_date and :enddate and cptcode not in ('1','2','3','4','PLA')) at1
inner join register on register.patno = at1.patno
left outer join
(select round(sum(payment),2) as p, round(sum(copayrecd),2) as c,  sum(adjustment) as ins_adj, sum(copayadjustment) as pt_adj, txnopaid, list(descript,',') as ins_adj_codes from
(select copayrecd, payment, adjustment, copayadjustment, txnopaid, c.descript from archive_transactions
left outer join (select code_id, descript from code_table where group_id = 1) c on c.code_id = archive_transactions.adj_code

where archive_flag =1 )
group by txnopaid) at2
on at2.txnopaid = at1.trnsxno
left outer join claims1 on claims1.invoiceno = at1.invoiceno
order by patno, invoiceno