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: VFC Age Validator

About

This report shows possible chart errors for active patients in terms of their assigned VFC status (1= Medicaid, 2=self pay, 3=AIAN, 4=underinsured).

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

Caveats

  • While most states’ VFC programs use these definitions, OP practices in universal purchase states or who use non-standard definitions of, for example “uninsured”, may find this report misleading.
  • Replace the comma-delimited list ('TCC','BLU','TNS','AGR') with your practice’s list of Medicaid plan codes.  Each code should be enclosed in single quotes and separated by commas.   If you only have one Medicaid plan PLN, your list will look like: (‘PLN’)
  • These will need to be replaced in all six instances in which they appear in the code.
  • Any patient with VFC 1,2,3,4 who is 19+ years old will show as “Too old for VFC.”
  • Any patient under 19 who is self-pay will show as “Self pay child - should be VFC 2.”
  • Any patient with a primary, secondary, or tertiary insurance in your Medicaid payer list who is under 19 and does not have VFC=1 will show as “Medicaid - should be VFC eligible.”
  • Any patient who has VFC=1 but does not show a primary, secondary, or tertiary insurance in your Medicaid payer list will show as “No Medicaid - not VFC eligible.”

Code

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

with c as
(select patno, fname,lname, birthdat, sickvno as vfc, ins_carrier_code, ins2_carrier_code, ins_carrier_code_3,
 floor(datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat),  dateadd(day,-extract (day from birthdat)+1,cast('today' as date)) )/12) as age
from register
where status_pat = 'ACTIVE' and patno >99 and lname <> 'TESTPATIENT')

select c.*, 'Too old for VFC' as problem from c where age >=19 and vfc in (1,2,3,4)
union
select c.*, 'Medicaid - should be VFC elig' as problem from c where age <19 and vfc not in (1) and
( ins_carrier_code in ('TCC','BLU','TNS','AGR') or  ins2_carrier_code in ('TCC','BLU','TNS','AGR') or  ins_carrier_code_3 in ('TCC','BLU','TNS','AGR'))
union
select c.*, 'Self pay child - should be VFC 2' as problem from c where age <19 and vfc <> 2 and
( ins_carrier_code in ('SP') or  ins2_carrier_code in ('SP') or  ins_carrier_code_3 in ('SP'))
union
select c.*, 'No Medicaid - not VFC elig' as problem from c where age <19 and vfc in (1) and
( ins_carrier_code not in ('TCC','BLU','TNS','AGR') and  ins2_carrier_code not in ('TCC','BLU','TNS','AGR') and  ins_carrier_code_3 not in ('TCC','BLU','TNS','AGR'))