📣 We have moved! All of the most up-to-date information on WebPT Products can be found in its new home on WebPT Discover.

RevFlow SQL: Top Business Uses

To help define the numerous use cases and metrics within WebPT Billing, here are the most common needs you may encounter. 

IN THIS ARTICLE

Unapplied Insurance Payments 

How are Unapplied Insurance Payments handled in RevFlow? Where can we report on them?
  • Insurance payments that are already applied but have become overages, which means they have been assigned back to the patient as "cash" or "unapplied" payments. These are currently reported on the RevFlow INSOVER Payment Summary report located on the Management menu.
  • SQL:
    • First, a charge record is created in BILLING_CHARGE with cpt_code =  ‘INSOVER’. The charge_amount =  0 but the payment_amount <> 0 and is applied to the charge_id
    • A payment record is created in PAYMENT_ADJUSTMENT
    • A credit record is created in PATIENT_CREDIT with patient_credit_type = 2
    • SQL: Unapplied insurance payments
      • SELECT dc.CHARGE_ID as "Charge Id",
     dc.PATIENT_ACCOUNT_NUMBER as "Patient Account Number",
     (date_trunc('day', dc.DATE_OF_SERVICE::date)) as "DOS",
     (date_trunc('day', dpa.deposit_date::date)) as "Payment_Adjustment Deposit",
     dc.CPT_CODE,
     dpa.PAYMENT_ADJUSTMENT_ID,
     dpa.PAYMENT_AMOUNT,
     pc.PATIENT_CREDIT_ID,
     pc.ORIGINAL_AMOUNT,
     pc.AMOUNT
FROM PUBLIC.SVW_BILLING_CHARGES dc
JOIN PUBLIC.SVW_PAYMENT_ADJUSTMENTS dpa on dc.CHARGE_ID = dpa.CHARGE_ID
JOIN PUBLIC.SVW_PATIENT_CREDIT pc on dpa.PATIENT_CREDIT_ID = pc.PATIENT_CREDIT_ID
WHERE dc.CPT_CODE = 'INSOVER'
  AND pc.PATIENT_CREDIT_TYPE = 2

Payments

What is the relationship between Patient Credits and Payments_Adjustments tables to make total payments?
  • Patient-initiated credits are first recorded in PATIENT_CREDIT, and are posted to the specific patient charge_id later in PAYMENT_ADJSUTMENT (opposite to question #1).  (see transfers below)
  • Patient_Credit to Payment_Adjustment
    • An initial PATIENT_CREDIT record (patient_credit_id) records the original_amount and amount, which will be the same at first. The original_amount is a static field, while the amount field serves as the remainder in the bucket for that patient_credit_id. The record may also contain a date of service to be applied to, although this isn't always used. When a payment is posted against a specific charge, the amount field is decremented by the value posted to the new PAYMENT_ADJUSTMENT record.  This new PAYMENT_ADJUSTMENT record contains a field patient_credit_id linking it back to the PATIENT_CREDIT record.
      • SQL: General queries to help explain PATIENT_CREDT vs. PAYMENT ADJUSTMENT tables
        • SELECT pa.PATIENT_CREDIT_ID,
pa.PAYMENT_AMOUNT,
pc.AMOUNT,
pc.ORIGINAL_AMOUNT
--Notice the pc.AMOUNT is now less than the PC.ORIGINAL_AMOUNT, this is because the transferred amount is pa.PAYMENT_AMOUNT.
-- The pc.ORIGINAL_AMOUNT is the static field on the PATIENT_CREDIT table
FROM PUBLIC.SVW_PAYMENT_ADJUSTMENTS pa
JOIN PUBLIC.SVW_PATIENT_CREDIT pc ON pa.PATIENT_CREDIT_ID = pc.PATIENT_CREDIT_ID
  • Payment_Adjustment to Patient_Credit
    • If an existing PAYMENT_ADJUSTMENT record is cpt_code = INSOVER, the amount is transferred back to the PATIENT_CREDIT table. This is linked to the PATIENT_CREDIT record again by the charge_id field in PATIENT_CREDIT and the patient_credit_id in D_PAYMENT_ADJUSTMENT.
      • SQL: Unapplied insurance payments
        • SELECT dc.CHARGE_ID as "Charge Id",
     dc.PATIENT_ACCOUNT_NUMBER as "Patient Account Number",
     (date_trunc('day', dc.DATE_OF_SERVICE::date)) as "DOS",
     (date_trunc('day', dpa.deposit_date::date)) as "Payment_Adjustment Deposit",
     dc.CPT_CODE,
     dpa.PAYMENT_ADJUSTMENT_ID,
     dpa.PAYMENT_AMOUNT,
     pc.PATIENT_CREDIT_ID,
     pc.ORIGINAL_AMOUNT,
     pc.AMOUNT
FROM PUBLIC.SVW_BILLING_CHARGES dc
JOIN PUBLIC.SVW_PAYMENT_ADJUSTMENTS dpa on dc.CHARGE_ID = dpa.CHARGE_ID
JOIN PUBLIC.SVW_PATIENT_CREDIT pc on dpa.PATIENT_CREDIT_ID = pc.PATIENT_CREDIT_ID
WHERE dc.CPT_CODE = 'INSOVER'
  AND pc.PATIENT_CREDIT_TYPE = 2

Insurance Payments

How can we report on the different insurance payments for the patient?
  • FROM_INS_CODE is the ID to focus on and joins to PRI_INS_CODE, etc.
  • SQL: Different Insurance breakout by Patient
    • SELECT adj.PAYMENT_ADJUSTMENT_ID
   , ch.PATIENT_ACCOUNT_NUMBER
   , adj.ADJUSTMENT_AMOUNT
   , adj.PAYMENT_AMOUNT
   , adj.FROM_INSURANCE_CODE
   , adj.FROM_INSURANCE_CLASS
   , ch.PRI_INS_CODE
   , ch.PRIMARY_INSURANCE_CODE
   , ch.PRIMARY_INSURANCE_CLASS
   , ch.SECONDARY_INSURANCE_CODE
   , ch.SECONDARY_INSURANCE_CLASS
   , ch.TERTIARY_INSURANCE_CODE
   , ch.TERTIARY_INSURANCE_CLASS
   , CASE WHEN ifnull(ADJ.FROM_INSURANCE_CODE,0) = CH.PRIMARY_INSURANCE_CODE and
ADJ.FROM_INSURANCE_CLASS = CH.PRIMARY_INSURANCE_CLASS THEN 'Primary'
      WHEN ifnull(ADJ.FROM_INSURANCE_CODE,0) = CH.SECONDARY_INSURANCE_CODE and
ADJ.FROM_INSURANCE_CLASS = CH.SECONDARY_INSURANCE_CLASS THEN 'Secondary'
      WHEN ifnull(ADJ.FROM_INSURANCE_CODE,0) = CH.TERTIARY_INSURANCE_CODE and
ADJ.FROM_INSURANCE_CLASS = CH.TERTIARY_INSURANCE_CLASS THEN 'Tertiary'
              ELSE 'Other' END AS WhichInsurance
FROM SVW_PAYMENT_ADJUSTMENTS adj
JOIN SVW_BILLING_CHARGES ch ON adj.CHARGE_ID = ch.CHARGE_ID

Refunds

Where are refunds logged and how can we report on them?
  • Refunds can be found in PATIENT_CREDIT as patient_credit_type =  7
  • SQL: Refunds by Patient
    • SELECT PATIENT_ACCOUNT_NUMBER, sum(ORIGINAL_AMOUNT) as "Total Refunds by Patient"
FROM PUBLIC.SVW_PATIENT_CREDIT
WHERE PATIENT_CREDIT_TYPE = 7
GROUP BY PATIENT_ACCOUNT_NUMBER

Balances

How can I view the difference between insurance and patient balance?
  • The balance is only assigned to insurance or patient at any given time for a charge
  • On a patient account basis, there could be both, given that some charges will have balances on either side. But on a charge level, the balance first belongs to the insurer then gets passed to patient.
  • These are calculated using the CUR_INS_CLASS and the CUR_BALANCE fields
  • SQL: Balances by Patient vs Insurance
    • SELECT CHARGE_ID,
sum(CASE ifnull(CUR_INS_CODE,0)
WHEN 0 THEN 0 ELSE CUR_BALANCE END) AS "Ins Balance",
sum(CASE ifnull(CUR_INS_CODE,0)
WHEN 0 THEN CUR_BALANCE ELSE 0 END) AS "Pat Balance"
FROM PUBLIC.SVW_BILLING_CHARGES
WHERE CPT_CODE NOT IN ('COPAY','INSOVER')
GROUP BY CHARGE_ID

EOBs/Denials

How do I report on EOBs and Denial counts?
  • The CLAIM_RESPONSE table contains all 835 data and denial data
  • SQL: 835 Data and Denials
    • Select cast(r.EOB_Key as varchar) as "EOB Key"
         , c.REVFLOW_COMPANY_NAME as "Company"
           , r.clinic_name as "Location"
        ,r.CHECK_EFT_NUM as "Check #"
      ,cast(r.Check_Amt as decimal(9,2)) as "Check Amt"
      ,case when r.payment_method_code='ACH' then 'EFT'
            when r.PAYMENT_METHOD_CODE='CHK' then 'CHECK'
            when r.PAYMENT_METHOD_CODE='FWT' then 'EFT'
            when r.PAYMENT_METHOD_CODE='NON' then 'NON-PAYMENT' end as "Payment Method"
      ,r.Payor_Id as "Payor ID"
        , r.Payor as "Payor Name"
        ,r.EOB_Date::date as "EOB Date"
      ,r.Release_Date::date as "Released Date"
      ,r.Processed_Date::date as "Processed Date"
      ,au.first_name || ' ' || au.last_name as "Released By"
    ,count(distinct (case when denied.TotalAllowed=0 and denied.TotalBilled>0 then
        acctno||date_part(year,dos::date)||date_part(month,dos::date)||date_part(day,dos::date)||date_part(year,claim_date::date)||date_part(month,claim_date::date)||date_part(day,claim_date::date)
        else NULL end)) as "Denials"
    ,count(distinct(case when li.EOB_KEY is not null then
        acctno||date_part(year,dos::date)||date_part(month,dos::date)||date_part(day,dos::date)||date_part(year,claim_date::date)||date_part(month,claim_date::date)||date_part(day,claim_date::date)
        else NULL end)) as "Claims Processed"
        ,au1.first_name || ' ' || au1.last_name as "Error Report Closed By"
        ,r.errors_worked_date::date as "Error Report Closed Date"
        , case when r.STATUS='S' and pa.EOB_KEY is not null then 'Released'
               when r.STATUS='S' and pa.EOB_KEY is null then 'Closed'
               when r.STATUS='P' then 'Previously Processed'
               when r.STATUS='R' then 'Processing' end as "Release Status"
    From public.SVW_REVFLOW_eob_release r
    left join public.SVW_REVFLOW_EOB_RELEASE_LINE_ITEM li on r.EOB_KEY = li.EOB_KEY
    left join (SELECT EOB_KEY
         , acctno||date_part(year,dos::date)||date_part(month,dos::date)||date_part(day,dos::date)||date_part(year,claim_date::date)||date_part(month,claim_date::date)||date_part(day,claim_date::date) AS Claim_key
         , sum(ALLOW_AMT) as TotalAllowed
         , sum(BILLED_AMT) as TotalBilled
    FROM PUBLIC.SVW_REVFLOW_EOB_RELEASE_LINE_ITEM
    group by EOB_KEY
         , acctno||date_part(year,dos::date)||date_part(month,dos::date)||date_part(day,dos::date)||date_part(year,claim_date::date)||date_part(month,claim_date::date)||date_part(day,claim_date::date)
        ) denied on li.EOB_KEY = denied.EOB_KEY and denied.Claim_key = li.acctno||date_part(year,li.dos::date)||date_part(month,li.dos::date)||date_part(day,li.dos::date)||date_part(year,li.claim_date::date)||date_part(month,li.claim_date::date)||date_part(day,li.claim_date::date)
    left join (select distinct GPMS_CODE, REVFLOW_COMPANY_ID, REVFLOW_COMPANY_NAME from public.SVW_CLINICs) C On C.REVFLOW_COMPANY_ID = R.COMPANY_ID
    left join (select distinct EOB_KEY from PUBLIC.SVW_REVFLOW_D_PAYMENT_ADJUSTMENT) pa on r.EOB_KEY = pa.EOB_KEY
    left join public.SVW_REVFLOW_A_User AU On AU.user_id = r.release_by
    left join public.SVW_REVFLOW_A_User AU1 On AU1.user_id = r.errors_worked_by
            Where r.release_Date is not NULL
      group by cast(r.EOB_Key as varchar), c.REVFLOW_COMPANY_NAME, r.clinic_name, r.check_eft_num,
            cast(r.Check_Amt as decimal(9,2)), case when r.payment_method_code='ACH' then 'EFT'
            when r.PAYMENT_METHOD_CODE='CHK' then 'CHECK'
            when r.PAYMENT_METHOD_CODE='FWT' then 'EFT'
            when r.PAYMENT_METHOD_CODE='NON' then 'NON-PAYMENT' end, r.payor_id, r.payor, r.eob_date::date,
            r.STATUS,
            r.release_date::date, r.processed_date::date,
            au.first_name || ' ' || au.last_name,
            au1.first_name || ' ' || au1.last_name,
            r.errors_worked_date::date
            , case when r.STATUS='S' and pa.EOB_KEY is not null then 'Released'
               when r.STATUS='S' and pa.EOB_KEY is null then 'Closed'
               when r.STATUS='P' then 'Previously Processed'
               when r.STATUS='R' then 'Processing' end;

First Billed Date

How do I join CLAIM and BILLING_CHARGE to get the first billed date (i.e claim_date)?
  • The claim_date is the closest to first billed date that the WebPT Billing system has and it can be found on the CLAIM table
  • SQL: Linking first billed date (i.e claim_date) to BILLING_CHARGE
    • SELECT dc.CHARGE_ID, c.CLAIM_DATE as "First Billed Date"
FROM PUBLIC.SVW_BILLING_CHARGES dc
JOIN PUBLIC.SVW_CLAIM c ON dc.CHARGE_ID = c.CHARGE_ID

Voided Charges

How can I find voided charges?
  • Modifier 1 will be set to ‘NC’ and the charge will be made inactive
  • SQL: Finding voided charges
    • select * from PUBLIC.SVW_REVFLOW_D_CHARGE
where proc_mod1 = 'NC'

Patient Notes

How do I report on patient notes?
  • SQL: Patient Notes Report
    • select pn.patient_id, p.last_name, p.first_name, pn.note, pn.created_date
from PUBLIC.SVW_REVFLOW_D_PATIENT_NOTE pn
join PUBLIC.SVW_PATIENT p on pn.patient_id = p.patient_id
join PUBLIC.SVW_D_NOTE_CODE nc on pn.note_code_id = nc.note_code_id

Payment Notes

How do I report on payment notes?
  • SQL: Payment Notes Report
    • SELECT  py.deposit_date
,py.batch_num
,py.created_date AS "Posting Date"
,SUM(ISNULL(py.payment_amt,0)) AS "Total Posted"
,u.last_name
,u.first_name AS "Posted By"
from (
  SELECT py.deposit_date, ISNULL(py.batch_num,'') AS batch_num, py.created_date, py.payment_amt, py.company_id, py.created_by
  FROM PUBLIC.SVW_REVFLOW_D_PAYMENT_ADJUSTMENT py
  WHERE py.is_active = 1
   AND py.deposit_date between @fdate and @tdate
  
  UNION ALL --include the patient credits into the report   
  
  SELECT py.deposit_date, py.Batch_num, py.created_date, py.original_amount, py.company_id, py.created_by   
  FROM PUBLIC.SVW_REVFLOW_D_PATIENT_CREDIT py
  WHERE py.is_active = 1
   AND py.deposit_date between @fdate and @tdate
 ) py
  LEFT JOIN PUBLIC.SVW_REVFLOW_A_USER u ON u.user_id = py.created_by
 GROUP BY py.deposit_date, py.batch_num, py.created_date, u.last_name, u.first_name
Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.