Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
invoice_idPrimary key and invoice unique identifier.id
createdThe timestamp when the invoice record was created.
modifiedThe timestamp when the invoice record was last modified.
is_deletedIndicates whether the invoice record is deleted.
statusThe current status of the invoice (e.g., IN_PROGRESS, FINALIZED, LOCKED, IMPORTED, READY_FOR_PAYMENT).status
status_changed_atThe timestamp when the invoice status was last changed.
external_idThe ID of the invoice in the legacy PIMS system, if imported from there.
clinic_client_idForeign key to the client & client_enriched table. The identifier of the client associated with the invoice.clinic_client_id
clinic_idForeign key to the clinic & clinic_enriched table. The identifier of the clinic associated with the invoice.clinic_id
clinic_nameThe name of the clinic associated with the invoice.
created_by_user_idForeign key to the user table. The identifier of the user who created the invoice.
created_by_clinic_user_idForeign key to the clinic_user table. The identifier of the clinic user who created the invoice.
subtotalThe subtotal amount of the invoice, in dollars. Snowflake displays dollars, API returns cents.subtotal
discountThe discount applied to the invoice, in dollars. Snowflake displays dollars, API returns cents.discount
taxThe tax amount applied to the invoice, in dollars. Snowflake displays dollars, API returns cents.tax
totalThe total amount of the invoice, in dollars (original_total from source). Snowflake displays dollars, API returns cents.total
amount_paidThe total amount paid toward the invoice, in dollars.
amount_refundedThe total amount refunded on the invoice, in dollars.
net_totalThe net total of the invoice after refunds, in dollars.
remaining_balanceThe remaining balance due on the invoice, in dollars. Zero when the invoice is marked not rendered. Snowflake displays dollars, API returns cents.remaining_balance
marked_as_collections_atThe timestamp when the invoice was marked as sent to collections.marked_as_collections_at
marked_not_rendered_atThe timestamp when the invoice was marked as not rendered.marked_not_rendered_at
finalized_atThe timestamp when the invoice was finalized.finalized_at
finalized_by_user_idForeign key to the user table. The identifier of the user who finalized the invoice.
locked_atThe timestamp when the invoice was locked.locked_at
tax_rate_breakdownJSON breakdown of tax rates applied to the invoice.
legacy_display_numberThe legacy display number for the invoice, if applicable.legacy_display_number
date_postedThe date the invoice was posted.
client_notesNotes from the client associated with the invoice.
last_updatedThe timestamp when the invoice record was last updated in the source table. This is the most recent timestamp from all related tables (invoice, clinic).
vetcove_corporate_idThe unique identifier of the corporate group associated with this record.

​
Relationships

​
SQL Definition

/*
    This model provides a business-ready, denormalized view of invoice data
    pre-joined for easy consumption, mirroring invoice API.
*/

"
    )
}}

with invoice as (
    select * from {{ ref('invoice_v2') }}
)

, clinics as (
    select * from {{ ref('clinic') }}
)

select
    invoice.id as invoice_id
    , invoice.created
    , invoice.modified
    , invoice.is_deleted
    , invoice.status
    , invoice.status_changed_at
    , invoice.external_id
    , invoice.client_id as clinic_client_id
    , invoice.clinic_id
    , clinics.name as clinic_name
    , invoice.created_by_user_id
    , invoice.created_by_clinic_user_id
    , invoice.subtotal
    , invoice.discount
    , invoice.tax
    , invoice.original_total as total
    , invoice.amount_paid
    , invoice.amount_refunded
    , invoice.net_total
    , case
        when invoice.marked_not_rendered_at is not null then 0
        else invoice.original_total - invoice.amount_paid
    end as remaining_balance
    , invoice.marked_as_collections_at
    , invoice.marked_not_rendered_at
    , invoice.finalized_at
    , invoice.finalized_by_user_id
    , invoice.locked_at
    , invoice.tax_rate_breakdown
    , invoice.legacy_display_number
    , invoice.date_posted
    , invoice.client_notes
    , greatest(
        coalesce(invoice.last_updated, '1900-01-01')
        , coalesce(clinics.last_updated, '1900-01-01')
    ) as last_updated
    , invoice.vetcove_corporate_id
from invoice
left join clinics
    on invoice.clinic_id = clinics.id