Skip to navigation

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.

Do not use for patient invoicing

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

TableOne row perPrimary keyShape
export_transactionCharge, payment, adjustment, or balance transfertransaction_idLedger
export_charge_detailsCharge transactiontransaction_idDetail
export_payment_detailsPayment transactiontransaction_idDetail
export_adjustment_detailsBalance-impacting adjustmenttransaction_idDetail
export_balance_transfer_detailsBalance transfer transactiontransaction_idDetail
export_informational_adjustment_detailsAdjustment code on a remit (not a transaction)adjustment_detail_idDetail
export_batch_detailsMonthly accounting periodbatch_idCurrent state
export_patient_monetary_transactionPatient payment or refundmonetary_transaction_idCurrent state
export_service_line_expected_revenueExpected revenue entry and charge transaction pairservice_line_expected_revenue_pkLedger

Detail tables

Join export_transaction to the detail table for its type on transaction_id:

transaction_typeDetail table
CHARGEexport_charge_details
PAYMENTexport_payment_details
ADJUSTMENTexport_adjustment_details
BALANCE_TRANSFERexport_balance_transfer_details
SELECT *
FROM export_transaction t
JOIN export_adjustment_details ad
ON t.transaction_type = 'ADJUSTMENT'
AND t.transaction_id = ad.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_id is set on the reversing transaction and points at the one it reverses.
  • voided_by_transaction_id is 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:

WHERE voids_transaction_id IS NULL
AND voided_by_transaction_id IS NULL

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 no charge_id or service_line_id. Only an encounter_id rollup includes them.
  • Group by counter_party_type to 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_revenue holds 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 on expected_revenue_id before summing amount_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_id and charge_id. See Understanding Remit Codes.
  • Batches: a batch is a monthly accounting period (batch_start_date to batch_end_date), the unit Candid’s month-end close works on. Users can still adjust a batch while it’s open. closed_at is null for the open, current period. Not every transaction has a batch_id.

Common queries

Balance per service line

SELECT
sl.service_line_id,
sl.procedure_code,
SUM(t.amount_cents) AS balance_cents
FROM export_service_line sl
LEFT JOIN export_transaction t
ON t.service_line_id = sl.service_line_id
AND t.voids_transaction_id IS NULL
AND t.voided_by_transaction_id IS NULL
GROUP BY 1, 2

The void filter sits in the ON clause, so service lines with no live transactions still appear, with a null balance.

Totals by batch

SELECT
bd.batch_id,
bd.batch_start_date,
bd.batch_end_date,
bd.closed_at,
SUM(t.amount_cents) AS total_amount_cents
FROM export_transaction t
JOIN export_batch_details bd
ON t.batch_id = bd.batch_id
GROUP BY 1, 2, 3, 4

Voids aren’t excluded here: a void is booked in the batch where it happened, so each period’s total includes both sides.