Payment

One row per payment recorded in Elation Billing, from both insurance payers and patients. Each payment carries its amount, dates, type and method, and how much has been applied to charges. Insurance payments have no EHR equivalent, so patient_id is often empty. This table’s columns and relationships are shown in the Hosted Database schema.

claim_id is populated on patient payments only, and is always empty on payer payments. This is by design rather than a gap in the data: one payer payment routinely settles many claims at once, so Elation Billing attributes payer payments to claims through financial_transaction instead. Joining payment to claim on claim_id therefore returns patient payments alone. Use financial_transaction to attribute payer payments to claims.claimmd_era_id is populated on payer payments only, and reaches era_matched_adjustment one-to-many. A single ERA covers every charge the payer adjudicated, so one payment matches an average of roughly 26 adjustment rows and several thousand in the worst case. Aggregate era_matched_adjustment first and join the result, rather than joining row for row, or the payment amounts repeat once per adjustment line and any total built on them is inflated.

For reporting on but not limited to:

Total payments received by practice or date
Insurance vs patient payments (pay_type)
Applied vs unapplied payment amounts
Co-pays (is_copay)
Claim-level attribution of payer payments, through financial_transaction
The notes recorded against a payment, and who wrote them, by joining payment_note on payment_id

Payments by practice and month

select
    p.practice_id
  , pr.name as practice_name
  , date_trunc('month', p.payment_date) as payment_month
  , count(*) as payments
  , sum(p.amount) as total_amount
from payment p
  left join practice pr on pr.id = p.practice_id
where p.is_deleted = false
group by p.practice_id, pr.name, payment_month
order by payment_month desc;

Unapplied payment balances

select
    p.practice_id
  , pr.name as practice_name
  , p.id as payment_id
  , p.amount
  , p.applied_amount
  , p.unapplied_amount
from payment p
  left join practice pr on pr.id = p.practice_id
where p.is_deleted = false
  and p.unapplied_amount > 0
order by p.unapplied_amount desc;

Payments with their ERA adjustment totals

Summarizes the ERA behind each payer payment. The adjustment detail is rolled up to one row per ERA and practice before the join, which keeps the result at one row per payment and leaves amount safe to total. Only adjustment_amount is summed, because it is the one money column that is genuinely per-adjustment-line.

with era_totals as (
    select
        e.claimmd_era_id
      , e.practice_id
      , count(*) as adjustment_lines
      , count(distinct e.claim_id) as claims_on_era
      , count(distinct e.claimmd_charge_id) as charges_on_era
      , sum(e.adjustment_amount) as total_adjustments
    from era_matched_adjustment e
    group by e.claimmd_era_id, e.practice_id
)
select
    p.id as payment_id
  , p.practice_id
  , pr.name as practice_name
  , p.payment_date
  , p.amount
  , p.applied_amount
  , t.claims_on_era
  , t.charges_on_era
  , t.adjustment_lines
  , t.total_adjustments
from payment p
  left join era_totals t
    on t.claimmd_era_id = p.claimmd_era_id
   and t.practice_id = p.practice_id
  left join practice pr on pr.id = p.practice_id
where p.is_deleted = false
  and p.claimmd_era_id is not null
order by p.payment_date desc;

Payer payments attributed to claims

Splits each payer payment across the claims it settled, using financial_transaction as the bridge. This is the route to claim-level attribution for payer payments, since their claim_id is always empty. Note the type = 'payment' filter. financial_transaction also carries adjustment, allowed, and balance_transfer rows against the same claim, so totaling amount without filtering on type mixes write-offs and contracted-rate entries in with the money actually received, and the result can exceed the payment itself. patient_name is null wherever financial_transaction.patient_id has no matching patient record. A filter on patient_name excludes those rows.

select
    p.id as payment_id
  , p.payment_date
  , p.amount as payment_total
  , ft.claim_id
  , c.local_id as claim_local_id
  , concat(pat.first_name, ' ', pat.last_name) as patient_name
  , sum(ft.amount) as amount_applied_to_claim
from payment p
  join financial_transaction ft on ft.payment_id = p.id
  left join claim c on c.id = ft.claim_id
  left join patient pat on pat.id = ft.patient_id
where p.is_deleted = false
  and ft.is_deleted = false
  and ft.type = 'payment'
  and ft.claim_id is not null
group by p.id, p.payment_date, p.amount, ft.claim_id, c.local_id, patient_name
order by p.payment_date desc, amount_applied_to_claim desc;

If you have any questions about this topic please reach out to Elation Support Portal with the subject line HDB - <your_question>

Hosted Database

Overview

How Tos

Common Queries

Elation Billing

Frequently Asked Questions

Early Release

Changelog

Payments by practice and month

Unapplied payment balances

Payments with their ERA adjustment totals

Payer payments attributed to claims

​Payments by practice and month

​Unapplied payment balances

​Payments with their ERA adjustment totals

​Payer payments attributed to claims

Payments by practice and month

Unapplied payment balances

Payments with their ERA adjustment totals

Payer payments attributed to claims