Financial Transactions

Transaction Detail Tables

The core of the financial transactions data model is the export_transaction table. This table will have most of the information you need to analyze your financial transactions. If you need to access more detailed data about one type of transaction, you can join the export_transaction table to one of the associated detail tables using an inner join:

1
SELECT *
2
FROM export_transaction
3
JOIN export_adjustment_details
4
  ON export_transaction.transaction_type = 'ADJUSTMENT'
5
  AND export_transaction.transaction_id = export_adjustment_details.transaction_id

Table Relationships

Each transaction type has its own detail table that can be joined based on the transaction type:

Informational Adjustments

Informational Adjustments are not tied to a financial transaction and do not affect balances — payers include these Claim Adjustment Reason Codes (CARCs) and Remittance Advice Remark Codes (RARCs) through remits (ERAs) to convey adjudication details. They can be associated directly with Service Lines or Encounters:

To learn more about CARCs and RARCs, see this Candid Support Center article: Understanding Remit Codes (CARCs & RARCs).

Batches

A batch is a monthly accounting period that users can adjust while it is open — the unit Candid’s End-of-Month (EOM) close operates on. Each batch runs from batch_start_date to batch_end_date, and closed_at is set once that month is closed (null for the open, current period).

Common Queries

Service Line Transaction Summary

To view a current transaction summary at the service line level, filtering out voids and voided transactions:

1
SELECT *
2
FROM export_transaction
3
JOIN export_charge_details
4
  ON export_transaction.transaction_id = export_charge_details.transaction_id
5
JOIN export_service_line
6
  ON export_transaction.service_line_id = export_service_line.service_line_id
7
WHERE export_transaction.voids_transaction_id IS NULL
8
  AND export_transaction.voided_by_transaction_id IS NULL

Roll Up Transactions by Batch

1
SELECT
2
  bd.batch_id,
3
  bd.batch_start_date,
4
  bd.batch_end_date,
5
  bd.closed_at,
6
  SUM(t.amount_cents) as total_amount_cents
7
FROM export_transaction t
8
JOIN export_batch_details bd
9
  ON t.batch_id = bd.batch_id
10
GROUP BY 1, 2, 3, 4