Overview

This report shows all insurance-open claims (defined as those having an insurance balance) which are currently in a queued status that contain a vaccine product code, its charge, and whether this seems legitimate depending on the VFC status of the administered vaccine. For information on adding insurance payers, see Add an Insurance Payer.

A VFC vaccine status of 5 is considered a private vaccine. It is considered a VFC vaccine otherwise. Depending on your state VFC eligibilities, these assumptions may not hold true for your practice.

The DETERMINATION field will read Probably OK if the vaccine is not VFC eligible and the price is greater than a penny, or if the vaccine is VFC eligible and the charge is less than or equal to a penny. However, if a VFC eligible vaccine is being charged more than a penny, the DETERMINATION field will read Change.

Suggested use case:

While claims are queued, before sending them, run this report and filter DETERMINATION to Change to find potentially-mischarged VFC vaccines.

Caveats:

• Insurance-open claims show those that have a nonzero insurance balance.
• Currently in a queued status means those claims that are currently set to status Q1.
• Contain a vaccine product code means that there is a CPT code between 90500 and 91999 in the claim.
• The VFC vaccine status is the VFC code number attached to the vaccine at the point of administration.  This may or may not be the same as the current VFC status of a patient, which is set in the Chart’s Demographic/Basic Info section.

SQL Code: Firebird

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

Copy

with ci as (select  invoiceno, max(asofdate) as most_recent from claim_status where asofdate >= cast('today' as date)-30 group by 1),
inq as (select ci.*, claim_status.hl7_category from ci inner join claim_status on ci.invoiceno = claim_status.invoiceno and ci.most_recent = claim_status.asofdate where hl7_category = 'Q1')

select inq.invoiceno, round(claims1.ins_balance,2) as claim_ins_balance,  most_recent, hl7_category, patno, cptcode, a.mod1, a.mod2,  a.date1,  a.charge, v.vfc1,
case when vfc1 <>5 and charge >0.01 then 'Change'  
when vfc1 =5 and charge<=0.01 then 'Change'
else 'Probably ok' end as determination
  from inq
inner join claims1 on claims1.invoiceno = inq.invoiceno
inner join (select cptcode,mod1, mod2, charge, date1, invoiceno from archive_transactions where cptcode between '90500' and '91999' and archive_flag <=1) a on a.invoiceno=inq.invoiceno
inner join vaccine1 v on v.patno = claims1.patno and v.vacdate = a.date1 and v.offcode = a.cptcode
where ins_balance>=0.01
order by 1

SQL Code: MySQL

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

Copy

with ci as (select  invoiceno, max(asofdate) as most_recent from claim_status where asofdate >date_add(curdate(), interval -30 day) group by 1),
inq as (select ci.*, claim_status.hl7_category from ci inner join claim_status on ci.invoiceno = claim_status.invoiceno and ci.most_recent = claim_status.asofdate where hl7_category = 'Q1')

select inq.invoiceno, round(claims1.ins_balance,2) as claim_ins_balance,  most_recent, hl7_category, claims1.patno, cptcode, a.mod1, a.mod2,  a.date1,  a.charge, v.vfc1,
case when vfc1 <>5 and a.charge >0.01 then 'Change'  
when vfc1 =5 and a.charge<=0.01 then 'Change'
else 'Probably ok' end as determination
  from inq
inner join claims1 on claims1.invoiceno = inq.invoiceno
inner join (select cptcode,mod1, mod2, charge, date1, invoiceno from archive_transactions where cptcode between '90500' and '91999' and archive_flag <=1) a on a.invoiceno=inq.invoiceno
inner join vaccine1 v on v.patno = claims1.patno and v.vacdate = a.date1 and v.offcode = a.cptcode
where ins_balance>=0.01
order by 1