Skip to navigation

export_service_line_expected_revenue

Expected revenue for each service line, priced against the organization’s configured fee schedules. Recorded as a ledger like export_transaction: entries are never edited, and a change writes a void entry plus a new entry. One row per (expected revenue entry, charge transaction) pair, so de-duplicate on expected_revenue_id before summing amount_cents.

Primary Key: service_line_expected_revenue_pk

Identifiers

service_line_expected_revenue_pk
STRINGRequired

Primary key: a hash of expected_revenue_id and charge_transaction_id, unique per row. Use it to upsert rows during incremental ingestion.

expected_revenue_id
STRING

ID of the expected revenue entry. Deduplicate by filtering to current charge_transaction_id by setting both voided_by_transaction_id and replaced_by_transaction_id to NULL.

charge_transaction_id
STRING

The charge transaction covered by this entry. Joins to export_transaction.transaction_id on charge rows, and to export_transaction.charge_transaction_id. NULL when the entry has no charge yet. Considered current when both voided_by_transaction_id and replaced_by_transaction_id are NULL.

service_line_id
STRING

The service line this entry prices. Joins to export_service_line.service_line_id and export_transaction.service_line_id.

service_line_charge_id
STRING

The parent charge. Joins to export_transaction.charge_id, which is how payments and adjustments tie back to their charge.

counter_party_id
STRING

The payer expected to pay, as a payer UUID. This matches how payers appear in export_transaction.counter_party_id. NULL when the patient’s primary insurance has no payer on file.

payer_plan_group_id
STRING

The payer plan group from the patient’s primary insurance at the time the entry was priced. NULL when the insurance has no plan group.

voids_expected_revenue_id
STRING

On a void entry, the ID of the entry it reverses. NULL on all other entries.

voided_by_expected_revenue_id
STRING

On an entry that has been reversed, the ID of the void entry. NULL if the entry has not been voided.

charge_payer_plan_group_id
STRING

The payer plan group recorded on the charge transaction.

voided_by_transaction_id
STRING

If the charge transaction has been voided, the ID of the voiding transaction.

replaced_by_transaction_id
STRING

If the charge transaction has been replaced (for example after a recode), the ID of the charge transaction that replaced it.

Organization

organization_id
STRING

Candid organization ID.

organization_name
STRING

Organization or company name.

external_organization_id
STRING

Organization’s External ID - the ID that the organization uses to map to their own upstream system

Details

counter_party_type
STRING

PAYER when counter_party_id is populated; otherwise NULL.

pricing_method_type
STRING

How the entry was priced. Currently always FEE_FOR_SERVICE. More pricing methods may be added in the future.

amount_cents
INTEGER

Expected amount in cents. Active entries are positive (or 0); void entries have the opposite sign of the entry they reverse. NULL means no expected amount could be determined. Deduplicate expected_revenue_id before aggregating.

transaction_timestamp
TIMESTAMP

When the entry was recorded in Candid (UTC). A void entry and the new entry that replaces it share the same timestamp.

batch_date
TIMESTAMP

Included for consistency with export_transaction. Currently always on the same date as transaction_timestamp.

charge_status
STRING

presubmission for charges created before the claim was submitted; billed for charges created at submission (and for self-pay).

is_deleted
BOOLEAN

Whether the service line was deleted in the Candid system. Deleted service lines remain in this table as tombstones so incremental consumers can remove them.

Timestamps

updated_at
TIMESTAMP

Time when the entry was last updated. Advances when the entry is voided, when a charge transaction is linked, and when the linked charge transaction is voided or replaced.

created_at
TIMESTAMP

When the entry was created.

deleted_at
TIMESTAMP

When the service line was deleted in the Candid system

Sync columns

_cds_row_version
TIMESTAMP

Time when this row’s delivered data last changed. Use this for incremental ingestion.

_cds_row_fingerprint
STRING

Hash of this row’s delivered data. Changes whenever any value in the row changes.