Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
supplier_order_idPrimary key. Unique identifier for the supplier order.id
createdThe timestamp when the supplier order record was created.
modifiedThe timestamp when the supplier order record was last modified.
is_deletedIndicates whether the supplier order record is deleted.
buyer_idForeign key to the buyer & buyer_enriched table. The identifier of the buyer associated with the supplier order.buyer_id
buyer_nameDisplay name of the buyer.buyer_name
supplier_idIdentifier of the supplier associated with the supplier order.supplier_id
supplier_short_nameShort display name of the supplier.supplier_short_name
supplier_nameFull name of the supplier.supplier_name
account_idIdentifier of the supplier account associated with the supplier order.account_id
account_unique_idExternal unique identifier for the account.account_unique_id
account_nameDisplay name of the account.account_name
statusCurrent status of the order.status
unique_idSupplier-provided unique identifier for the order.unique_id
order_numberHuman-readable order number.order_number
poPurchase order number.po
dateDate the order was placed.date
subtotalOrder 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
feesFees in dollars. Snowflake displays dollars, API returns cents.fees
taxTax amount in dollars. Snowflake displays dollars, API returns cents.tax
totalOrder total in dollars. Snowflake displays dollars, API returns cents.total
last_activityThe most recent receiving activity timestamp associated with the supplier order.last_activity
corporate_location_idCorporate location identifier derived from buyer corporate metadata or the supplier account metadata, used for location-level reporting.corporate_location_id
last_updatedThe timestamp when the supplier order 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 procurement order data
    mirroring the procurement order API.
*/

"
    )
}}

with supplier_order as (
    select * from {{ ref('supplier_order') }}
)

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

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

, receivable as (
    select
        buyer_id
        , order_unique_id
        , supplier_id
        , max(modified) as last_activity
        , max(last_updated) as last_updated
    from {{ ref('receivable') }}
    group by
        buyer_id
        , order_unique_id
        , supplier_id
)

select
    supplier_order.id as supplier_order_id
    , supplier_order.created
    , supplier_order.modified
    , supplier_order.is_deleted
    , supplier_order.buyer_id
    , buyer.name as buyer_name
    , supplier_order.supplier_id
    , supplier.short_name as supplier_short_name
    , supplier.name as supplier_name
    , supplier_order.account_id
    , supplier_order.account_unique_id
    , supplier_order.account_name
    , supplier_order.status
    , supplier_order.unique_id
    , supplier_order.number as order_number
    , supplier_order.po
    , supplier_order.date
    , supplier_order.subtotal
    , supplier_order.shipping
    , supplier_order.discount
    , supplier_order.fees
    , supplier_order.tax
    , supplier_order.total
    , receivable.last_activity
    , coalesce(buyer.location_id, supplier_order.location_id) as corporate_location_id
    , greatest(
        coalesce(supplier_order.last_updated, '1900-01-01')
        , coalesce(buyer.last_updated, '1900-01-01')
        , coalesce(supplier.last_updated, '1900-01-01')
        , coalesce(receivable.last_updated, '1900-01-01')
    ) as last_updated
    , supplier_order.vetcove_corporate_id
from supplier_order
left join buyer
    on supplier_order.buyer_id = buyer.id
left join supplier
    on supplier_order.supplier_id = supplier.id
left join receivable
    on
        supplier_order.buyer_id = receivable.buyer_id
        and supplier_order.unique_id = receivable.order_unique_id
        and supplier_order.supplier_id = receivable.supplier_id