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: Expected Revenue - Charges and Contracted Rates

About

This SQL lets you pick a date range (say, this week) and takes all your CPT codes for charges rendered, matches them to your contracted allowables, and tells you the expected amount you'll receive, based on your insurance contracts.

e.g. Today my provider saw just two patients and charged each of them 99213.  Both patients have insurance company XYZ. Let’s say we charge units of 99213 at $100 each.  Also, let's say my contracted rate is $75 for 99213 for XYZ. This report would show you a total of $200 in charges with $150 of that in expected payment.

For more information on the OP Contracts feature:

Sample images of the report run in the Database Viewer are shown below:

Totals only:

Drill down:


Caveats

  • It only shows charges that have been converted to a claim and daysheeted.  (The claims don't have to be sent yet.)
  • This will only show CPT codes that meet the following conditions:
  • CPT Codes have an associated payer contract
  • The payer contract has a fee for that CPT code loaded.  

If you are NOT using the Contracts feature (or you're using it, but you only have a few CPTs loaded) the charges and payments will either not show up properly, or not show up at all.

Code (Totals Only)

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

select sum(charge) as total_charge, sum(contracted_allowable) as expected_pmt from 
(

select cptcode,units, ins_carrier_code, charge, date1 as dos, (units *  payment_amount) as contracted_allowable from archive_transactions
inner join ins_carrier on ins_carrier.ins_carrier_code = archive_transactions.ins_carrier_code
inner  join ins_carrier_contract on ins_carrier_contract.ins_carrier_id = ins_carrier.carrier_id
inner join ins_contract_detail on ( ins_contract_detail.ins_contract_id = ins_carrier_contract.ins_contract_id) and (archive_Transactions.cptcode = ins_contract_detail.hcpcs)


where date1 between :start_date and :end_date and cptcode not in ('1','2','3','4') and archive_transactions.archive_flag = 1
and date1>=ins_carrier_contract.effective_date
and (ins_carrier_contract.expiration_date is null or date1<=ins_carrier_contract.expiration_date)
)

Code (Drill down)

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

select sum(charge) as total_charge, sum(contracted_allowable) as expected_pmt from 
(

select cptcode,units, ins_carrier_code, charge, date1 as dos, (units *  payment_amount) as contracted_allowable from archive_transactions
inner join ins_carrier on ins_carrier.ins_carrier_code = archive_transactions.ins_carrier_code
inner  join ins_carrier_contract on ins_carrier_contract.ins_carrier_id = ins_carrier.carrier_id
inner join ins_contract_detail on ( ins_contract_detail.ins_contract_id = ins_carrier_contract.ins_contract_id) and (archive_Transactions.cptcode = ins_contract_detail.hcpcs)


where date1 between :start_date and :end_date and cptcode not in ('1','2','3','4') and archive_transactions.archive_flag = 1
and date1>=ins_carrier_contract.effective_date
and (ins_carrier_contract.expiration_date is null or date1<=ins_carrier_contract.expiration_date)
)