Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
order_item_idPrimary key. Unique identifier for the supplier order item.id
supplier_order_idForeign key to the parent supplier order & supplier_orders_enriched table.supplier_order_id
is_deletedIndicates whether the order item record is deleted.
corporate_location_idCorporate location identifier derived from buyer corporate metadata or the supplier account metadata, used for location-level reporting.corporate_location_id
nameDisplay name of the ordered item.name
unique_idExternal unique identifier for the order item.unique_id
skuSKU of the ordered item.sku
unit_priceUnit price in dollars. Snowflake displays dollars, API returns cents.unit_price
quantityOrdered quantity.quantity
quantity_shippedQuantity shipped by the supplier.quantity_shipped
total_priceTotal line price in dollars. Snowflake displays dollars, API returns cents.total_price
statusCurrent status of the order item.status
dateDate the order was placed.date
supply_idForeign key to the supply record linking item to supplier.supply_id
item_idForeign key to the product item.item_id
primary_category_nameTop-level (parent) product category name.primary_category_name
secondary_category_nameLeaf-level product category name.secondary_category_name
manufacturer_nameName of the item manufacturer.manufacturer_name
manufacturer_noManufacturer part number.manufacturer_no
order_numberHuman-readable order number.order_number
unitsTotal units ordered (unit_measurement units * quantity).units
quantity_receivedTotal quantity received as recorded in receivables.quantity_received
counts_toward_budgetWhether this item counts toward corporate budget tracking.counts_toward_budget
missed_savingsCorporate net savings missed on this order, in dollars. Snowflake displays dollars, API returns cents.missed_savings
last_updatedThe timestamp when the supplier order item 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 order items data
    mirroring the procurement order items API.
*/

"
    )
}}

with order_item as (
    select * from {{ ref('supplier_order_item') }}
)

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

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

, supplys as (
    select * from {{ ref('sync_supply') }}
)

, items as (
    select * from {{ ref('products_item') }}
)

, receivable as (
    select
        receivable.buyer_id
        , receivable.supplier_id
        , item.sku
        , receivable.order_unique_id
        , sum(item.quantity) as quantity_received
        , max(greatest(
            coalesce(item.last_updated, '1900-01-01')
            , coalesce(receivable.last_updated, '1900-01-01')
        )) as last_updated
    from {{ ref('receivable_item') }} as item
    inner join {{ ref('receivable') }} as receivable
        on item.receivable_id = receivable.id
    where
        item.is_deleted = false
        and receivable.is_deleted = false
    group by
        receivable.buyer_id
        , receivable.supplier_id
        , item.sku
        , receivable.order_unique_id
)

select
    order_item.id as order_item_id
    , order_item.supplier_order_id
    , order_item.is_deleted
    , coalesce(buyer.location_id, supplier_order.location_id) as corporate_location_id
    , items.name
    , order_item.unique_id
    , order_item.sku
    , order_item.unadjusted_unit_price as unit_price
    , order_item.unadjusted_quantity as quantity
    , order_item.unadjusted_quantity_shipped as quantity_shipped
    , order_item.total_price
    , order_item.status
    , supplier_order.date
    , order_item.supply_id
    , items.id as item_id
    , items.primary_category_name
    , items.secondary_category_name
    , items.manufacturer_name
    , items.manufacturer_no
    , supplier_order.number as order_number
    , coalesce(items.unit_measurement_units, 1)
    * coalesce(order_item.unadjusted_quantity, 1)
        as units
    , coalesce(receivable.quantity_received, 0) as quantity_received
    , order_item.counts_toward_budget
    , coalesce(order_item.missed_savings, 0) as missed_savings
    , greatest(
        coalesce(order_item.last_updated, '1900-01-01')
        , coalesce(supplier_order.last_updated, '1900-01-01')
        , coalesce(supplys.last_updated, '1900-01-01')
        , coalesce(items.last_updated, '1900-01-01')
        , coalesce(buyer.last_updated, '1900-01-01')
        , coalesce(receivable.last_updated, '1900-01-01')
    ) as last_updated
    , order_item.vetcove_corporate_id
from order_item
inner join supplier_order
    on order_item.supplier_order_id = supplier_order.id
left join supplys
    on order_item.supply_id = supplys.id
left join items
    on supplys.item_id = items.id
left join buyer
    on supplier_order.buyer_id = buyer.id
left join receivable
    on
        order_item.sku = receivable.sku
        and supplier_order.supplier_id = receivable.supplier_id
        and supplier_order.buyer_id = receivable.buyer_id
        and supplier_order.unique_id = receivable.order_unique_id