Financial Transactions
Every financial event on your claims. export_transaction has one row per charge, payment, adjustment or balance transfer, with its amount_cents. The detail tables add type-specific attributes to those rows.
Data Share is refreshed nightly, not in real time, so it can’t give an exact current patient balance. For invoicing, you must use the Patient Invoicing API. Candid can’t support invoicing issues caused by using Data Share instead.
Tables
Detail tables
Join export_transaction to the detail table for its type on transaction_id:
A patient payment or refund in export_patient_monetary_transaction can create several payment transactions. Join it to export_payment_details on monetary_transaction_id.
Voids
A void never changes the original transaction’s amount. Instead, a new row reverses it:
voids_transaction_idis set on the reversing transaction and points at the one it reverses.voided_by_transaction_idis set on the original transaction once it’s reversed.
The original row still changes when it’s voided, so don’t treat export_transaction as append-only. Sync it on _cds_row_version, which moves whenever a row’s delivered data change.
For live totals, exclude both sides of every void:
Rolling up amounts
Sum amount_cents, excluding voids, at the grain you need: charge_id, service_line_id or encounter_id. Charges are positive. Payments are usually negative, and reversals and refunds are positive.
- Claim-level transactions (
target_type = 'CLAIM') have nocharge_idorservice_line_id. Only anencounter_idrollup includes them. - Group by
counter_party_typeto see how much sits with the payer, the patient or a non-insurance payer. Balance transfers move amounts between them and net to zero.
Other tables
- Expected revenue:
export_service_line_expected_revenueholds expected revenue per service line, priced against your fee schedules. Entries are never edited: a change writes a void entry and a new entry. There’s one row per entry and charge transaction, so de-duplicate onexpected_revenue_idbefore summingamount_cents. - Informational adjustments: CARC and RARC codes that payers send on remits to explain adjudication. They don’t affect balances and aren’t transactions. They link to
encounter_id,service_line_idandcharge_id. See Understanding Remit Codes. - Batches: a batch is a monthly accounting period (
batch_start_datetobatch_end_date), the unit Candid’s month-end close works on. Users can still adjust a batch while it’s open.closed_atis null for the open, current period. Not every transaction has abatch_id.
Common queries
Balance per service line
The void filter sits in the ON clause, so service lines with no live transactions still appear, with a null balance.
Totals by batch
Voids aren’t excluded here: a void is booked in the batch where it happened, so each period’s total includes both sides.