Important Content Update Message
We are currently updating the OP Help Center content for the release of OP 20. OP 20 (official version 20.0.x) is the certified, 2015 Edition, version of the Office Practicum software. This is displayed in your software (Help tab > About) and in the Help Center tab labeled Version 20.0. We appreciate your patience as we continue to update all of our content.

SQL: Vaccine Payments vs Price Paid

About

This report looks at what you were paid for each vaccine serum that has been billed and completely adjudicated.  It compares what you were paid to the cost of the vaccine, assuming you have put the cost per dose in FEE4 in the CPT code tables.

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

Caveats

  • This SQL only shows vaccine serum payments (not vaccine administration payments).
  • “FEE4” pulls from the CPT code tables.  This requires you to put your current cost per dose in fee schedule #4.  To use a different fee schedule, you can edit the SQL to read e.g. FEE14 or FEE19.
  • Only vaccine serum codes between 90620 and 90746 appear in this report.
  • Payments include both insurance-side and patient-side, and reflect actual payments (not just allowables.)
  • Only completed claims (i.e. no balance) are included.  That is, claims you just sent yesterday with vaccines won’t appear here.
  • Vaccines that you charged $0.00 or $0.01 for, i.e. VFC vaccines, are not listed here.  If you charged more than $0.01 on the serum code of a VFC vaccine (i.e. you are in a state that requires you to charge VFC admin fee on the VFC product code), you will get weird results.

Code

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

select patno, cptcode, date1, at3.ins_carrier_code, ins_carrier.ins_fullname, trnsxno, charge, p+c as paid, cost.fee4
from
(

select patno, cptcode,  date1, trnsxno, charge, sum(payment) as p, sum(copayrecd) as c, sum(adjustment) as a from
(
select at1.patno, at1.date1, at1.trnsxno, at1.cptcode,at1.charge, at2. payment, at2.copayrecd, at2.adjustment from archive_transactions at1
inner join archive_transactions at2 on at2.txnopaid = at1.trnsxno
where at1.date1 between :start_date and :end_date
and at1.cptcode between '90620' and '90746'
and at1.archive_flag = 1
and at2.archive_flag = 1
and at1.charge > '0.01'
)
group by  patno, cptcode, date1, trnsxno, charge
) aa
left outer join archive_transactions at3 on at3.trnsxno = aa.trnsxno
left outer join ins_carrier on ins_carrier.ins_carrier_code = at3.ins_carrier_code
left outer join
(
select cptcode, fee4 from cpt_codes where cptcode between '90620' and '90746'
order by cptcode ) cost on cost.cptcode = aa.cptcode


where (charge-p-c-a)<0.01
order by cptcode