Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
invoice_idPrimary key. Unique identifier for the invoice.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.
buyer_idForeign key to the buyer & buyer_enriched table. The identifier of the buyer associated with the invoice.buyer_id
buyer_nameDisplay name of the buyer.buyer_name
order_numberHuman-readable order number or order reference associated with the invoice.order_number
supplier_idIdentifier of the supplier associated with the invoice.supplier_id
supplier_short_nameAbbreviated display name of the supplier.supplier_short_name
supplier_nameFull name of the supplier.supplier_name
invoice_supplier_idIdentifier of the third-party invoice supplier associated with the invoice.invoice_supplier_id
unique_idSupplier-provided unique identifier for the invoice.unique_id
display_numberHuman-readable display number for the invoice.display_number
termsPayment terms recorded on the invoice.terms
note_messageHuman-readable note explaining invoice status or follow-up context.note_message
invoice_typeInvoice classification stored on the invoice record.invoice_type
kindInvoice kind classification.kind
dateInvoice date.date
shipping_dateShipment date associated with the invoice, when present.shipping_date
due_datePayment due date for the invoice.due_date
is_paidIndicates whether the invoice has been marked as paid.is_paid
is_okay_to_payIndicates whether the invoice has been marked as okay to pay.is_okay_to_pay
has_location_setIndicates whether the invoice is associated with a corporate or supplier-account location.has_location_set
statusCurrent status of the invoice.status
subtotalInvoice subtotal in dollars. Snowflake displays dollars, API returns cents.subtotal
shippingShipping amount in dollars. Snowflake displays dollars, API returns cents.shipping
discountDiscount amount in dollars. Snowflake displays dollars, API returns cents.discount
feesFee amount in dollars. Snowflake displays dollars, API returns cents.fees
taxTax amount in dollars. Snowflake displays dollars, API returns cents.tax
totalInvoice total in dollars. Snowflake displays dollars, API returns cents.total
item_countNumber of current invoice items associated with the invoice.item_count
diff_countNumber of structured difference entries recorded on the invoice.diff_count
last_activityThe timestamp of the last activity associated with the invoice.last_activity
shipping_address_nameShipping recipient name recorded on the invoice.shipping_address.name
shipping_address_oneThe first line of the shipping address.shipping_address.address_one
shipping_address_twoThe second line of the shipping address.shipping_address.address_two
shipping_cityThe shipping city recorded on the invoice.shipping_address.city
shipping_stateThe shipping state recorded on the invoice.shipping_address.state
shipping_postalcodeThe shipping postal code recorded on the invoice.shipping_address.postal_code
billing_address_nameBilling recipient name recorded on the invoice.billing_address.name
billing_address_oneThe first line of the billing address.billing_address.address_one
billing_address_twoThe second line of the billing address.billing_address.address_two
billing_cityThe billing city recorded on the invoice.billing_address.city
billing_stateThe billing state recorded on the invoice.billing_address.state
billing_postalcodeThe billing postal code recorded on the invoice.billing_address.postal_code
last_updatedThe timestamp when the invoice record was last updated. This is the most recent timestamp from all related tables.
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 supplier invoice data
    in b2b, using supplier orders as the closest invoice proxy available in raw b2b models.
*/

"
    )
}}

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

, buyer as (
    select * from {{ ref('buyer') }}
)

, supplier as (
    select * from {{ ref('supplier') }}
)

, invoice_items as (
    select
        invoice_id
        , count(*) as item_count
        , max(last_updated) as last_updated
    from {{ ref('invoice_items') }}
    where
        is_deleted = false
    group by invoice_id
)

select
    invoice.id as invoice_id
    , invoice.created
    , invoice.modified
    , invoice.is_deleted
    , invoice.buyer_id
    , buyer.name as buyer_name
    , invoice.order_number
    , invoice.supplier_id
    , supplier.short_name as supplier_short_name
    , supplier.name as supplier_name
    , invoice.invoice_supplier_id
    , invoice.unique_id
    , invoice.display_number
    , invoice.terms
    , invoice.note_message
    , invoice.invoice_type
    , invoice.kind
    , invoice.date
    , invoice.shipping_date
    , invoice.due_date
    , coalesce(invoice.is_paid, false) as is_paid
    , invoice.is_okay_to_pay
    , case
        when coalesce(buyer.location_id, invoice.location_id) is not null
            then true
        else false
    end as has_location_set
    , invoice.status
    , coalesce(invoice.subtotal, 0) as subtotal
    , coalesce(invoice.shipping, 0) as shipping
    , coalesce(invoice.discount, 0) as discount
    , coalesce(invoice.fees, 0) as fees
    , coalesce(invoice.tax, 0) as tax
    , coalesce(invoice.total, 0) as total
    , coalesce(invoice_items.item_count, 0) as item_count
    , coalesce(invoice.diff_count, 0) as diff_count
    , invoice.modified as last_activity
    , invoice.shipping_address_name
    , invoice.shipping_address_one
    , invoice.shipping_address_two
    , invoice.shipping_city
    , invoice.shipping_state
    , invoice.shipping_postalcode
    , invoice.billing_address_name
    , invoice.billing_address_one
    , invoice.billing_address_two
    , invoice.billing_city
    , invoice.billing_state
    , invoice.billing_postalcode
    , greatest(
        coalesce(invoice.last_updated, '1900-01-01')
        , coalesce(buyer.last_updated, '1900-01-01')
        , coalesce(supplier.last_updated, '1900-01-01')
        , coalesce(invoice_items.last_updated, '1900-01-01')
    ) as last_updated
    , invoice.vetcove_corporate_id
from invoice
left join buyer
    on invoice.buyer_id = buyer.id
left join supplier
    on invoice.supplier_id = supplier.id
left join invoice_items
    on invoice.id = invoice_items.invoice_id