Inventory Code Clinic

Clinic-level inventory code enriched model providing global preferences, organization preferences, and effective preferences as well as calculated quantities (e.g. stock_quantity, in_transit_quantity).

• Each row represents an inventory and clinic where an organization preference exists.
• Compatible with the Vetcove API endpoint(s): /clinics/{clinic_id}/inventory-codes/
• API endpoint(s): https://connect.vetcove.com/ext/v2/clinics/{clinic_id}/inventory-codes/
• Snowflake view: phd_enriched.inventory_code_clinic_enriched

Columns

Column	Description	API Field Name
inventory_id	Part of composite primary key. Inventory unique identifier.	id
inventory_is_deleted	Whether the global inventory record is deleted.
code	The unique inventory code for this global inventory item.	code
kind	The kind/type of inventory item (e.g., STANDARD).	kind
inventory_category_id	Foreign key to the inventory category table.	category_id
clinic_id	Part of composite primary key. Foreign key to the clinic table.
global_preferences_name	The name of the inventory item from global inventory (Vetcove’s default).	name
global_preferences_unit_of_measure	The unit of measure from global inventory (Vetcove’s default).	unit_of_measure
organization_preference_id	The unique identifier for the organization/group inventory preference record. Always present at clinic level.
organization_preferences_is_enabled	Whether the inventory item is enabled at the organization/group level. Always present at clinic level (inner join to organization preferences).	organization_preferences.is_enabled
organization_preferences_name	Organization-level override name for the inventory item.	organization_preferences.name
organization_preferences_unit_of_measure	Organization-level override for unit of measure.	organization_preferences.unit_of_measure
organization_preferences_grade	The grade classification set at the organization level.	organization_preferences.grade
organization_preferences_code	Organization-specific code for the inventory item.	organization_preferences.code
organization_preferences_is_deleted	Whether the organization/group inventory preference record is deleted.
clinic_preference_id	The unique identifier for the clinic inventory preference record. Null if no clinic preference exists.
clinic_preferences_status	The status of the inventory at the clinic level (e.g., IN_STOCK, OUT_OF_STOCK).	clinic_preferences.status
clinic_preferences_is_enabled	Indicates whether the inventory item is enabled at the clinic level.	clinic_preferences.is_enabled
clinic_preferences_name	Optional clinic-specific override name for the inventory item.	clinic_preferences.name
clinic_preferences_min_quantity	Minimum quantity threshold set at the clinic level.	clinic_preferences.min_quantity
clinic_preferences_reorder_point	Reorder point threshold set at the clinic level.	clinic_preferences.reorder_point
clinic_preferences_should_track_lot_number	Indicates whether lot number tracking is enabled for this inventory at the clinic.	clinic_preferences.should_track_lot_number
clinic_preferences_unit_price	The unit price for the inventory item at the clinic level. Snowflake displays dollars, API returns cents.	clinic_preferences.unit_price
clinic_preferences_last_unit_price_update	The timestamp when the unit price was last updated.	clinic_preferences.last_unit_price_update
clinic_preference_is_deleted	Indicates whether the clinic preference record is deleted.
effective_preferences_is_enabled	Complex AND logic: Organization preference must be enabled, AND clinic preference must exist AND not be deleted AND be enabled. Returns false if any condition fails.	effective_preferences.is_enabled
effective_preferences_name	Resolved using hierarchy with nullif for empty strings: clinic → organization → global inventory name.	effective_preferences.name
effective_preferences_unit_of_measure	Resolved using hierarchy: organization → global (clinic does not have unit_of_measure).	effective_preferences.unit_of_measure
effective_preferences_grade	Resolved from organization preferences only (clinic and global don’t have grade).	effective_preferences.grade
effective_preferences_min_quantity	Clinic preference value, null if no clinic preference exists (not set at organization/global level).	effective_preferences.min_quantity
effective_preferences_reorder_point	Clinic preference value, null if no clinic preference exists (not set at organization/global level).	effective_preferences.reorder_point
effective_preferences_should_track_lot_number	Clinic preference value, defaults to false if null or no clinic preference (not set at organization/global level).	effective_preferences.should_track_lot_number
effective_preferences_unit_price	Clinic preference value, null if no clinic preference exists (only set at clinic level). Snowflake displays dollars, API returns cents.	effective_preferences.unit_price
stock_quantity	The current stock quantity for this inventory item at this clinic. Calculated as the sum of bucket stock_quantity for all non-deleted buckets.	stock_quantity
in_transit_quantity	The in-transit quantity for this inventory item at this clinic. Calculated from unreceived purchase order items, converted to global unit of measure.	in_transit_quantity
on_hold_quantity	The on-hold quantity for this inventory item at this clinic. Calculated from editable invoices, summed per bucket then aggregated per inventory.	on_hold_quantity
is_controlled	True if this inventory item is a controlled substance in this clinic’s context: federally controlled, or state-level controlled and the clinic’s primary address state is in the substance’s controlled_states list. False for non-US clinics or when no controlled substance is linked.	is_controlled
last_updated	The timestamp when this inventory record was last updated. The most recent timestamp from all related tables.
vetcove_corporate_id	The unique identifier of the corporate group associated with this record.

Relationships

• Clinic via clinic_id

SQL Definition

View SQL

/*
    Clinic-level inventory code enriched model.
    Mirrors the inventory clinic code API response
    structure with flattened fields for easy SQL querying.

    One row per inventory code & clinic where an organization
    preference exists.
*/

"
    )
}}

with inventories as (
    select * from {{ ref('inventory') }}
)

, preferences as (
    select * from {{ ref('inventory_clinic_preference') }}
)

, buckets as (
    select * from {{ ref('inventory_clinic_bucket') }}
)

, stock_by_inventory_clinic as (
    select
        preferences.inventory_id
        , preferences.clinic_id
        , max(buckets.last_updated) as last_updated
        , sum(buckets.stock_quantity) as stock_quantity
    from buckets
    inner join preferences
        on buckets.inventory_clinic_preference_id = preferences.id
    where
        buckets.is_deleted = false
    group by
        preferences.inventory_id
        , preferences.clinic_id
)

, group_preferences as (
    select * from {{ ref('inventory_corporate_preference') }}
)

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

, controlled_substances as (
    select * from {{ ref('controlled_substance') }}
)

, unit_conversions_raw as (
    select * from {{ ref('inventory_unit_conversion') }}
    where is_deleted = false
)

-- Normalize so both directions exist; allows a simple equi-join
-- instead of an OR condition in join.
, unit_conversions as (
    select
        id
        , global_inventory_id
        , source_unit_of_measure
        , target_unit_of_measure
        , source_qty
        , target_qty
        , last_updated
        , vetcove_corporate_id
    from unit_conversions_raw
    union all
    select
        id
        , global_inventory_id
        , target_unit_of_measure as source_unit_of_measure
        , source_unit_of_measure as target_unit_of_measure
        , target_qty as source_qty
        , source_qty as target_qty
        , last_updated
        , vetcove_corporate_id
    from unit_conversions_raw
)

select
    -- Top-level identifiers
    inventories.id as inventory_id
    , inventories.is_deleted as inventory_is_deleted
    , inventories.code
    , inventories.kind
    , inventories.inventory_category_id
    , preferences.clinic_id
    -- Global preferences (from inventory)
    , inventories.name as global_preferences_name
    , inventories.unit_of_measure as global_preferences_unit_of_measure
    -- Organization preferences (not nullable at clinic level)
    , group_preferences.id as organization_preference_id
    , group_preferences.is_enabled as organization_preferences_is_enabled
    , group_preferences.name as organization_preferences_name
    , group_preferences.unit_of_measure as organization_preferences_unit_of_measure
    , group_preferences.grade as organization_preferences_grade
    , group_preferences.code as organization_preferences_code
    , group_preferences.is_deleted as organization_preferences_is_deleted
    -- Clinic preferences (null when no clinic pref exists)
    , preferences.id as clinic_preference_id
    , preferences.status as clinic_preferences_status
    , preferences.is_enabled as clinic_preferences_is_enabled
    , preferences.name as clinic_preferences_name
    , case
        when
            group_preferences.unit_of_measure is null
            or unit_conversions.id is null
            or group_preferences.unit_of_measure = inventories.unit_of_measure
            then preferences.min_quantity
        else round(
            preferences.min_quantity
            * (
                unit_conversions.target_qty / nullif(unit_conversions.source_qty, 0)
            ), 4
        )
    end as clinic_preferences_min_quantity
    , case
        when
            group_preferences.unit_of_measure is null
            or unit_conversions.id is null
            or group_preferences.unit_of_measure = inventories.unit_of_measure
            then preferences.reorder_point
        else round(
            preferences.reorder_point
            * (
                unit_conversions.target_qty / nullif(unit_conversions.source_qty, 0)
            ), 4
        )
    end as clinic_preferences_reorder_point
    , preferences.should_track_lot_number as clinic_preferences_should_track_lot_number
    , case
        when
            preferences.unit_price is null then null
        when
            group_preferences.unit_of_measure is null
            or group_preferences.unit_of_measure = inventories.unit_of_measure
            or unit_conversions.id is null
            then preferences.unit_price
        else round(
            (
                preferences.unit_price * unit_conversions.source_qty
            )
            / nullif(unit_conversions.target_qty, 0
            )
            , 2
        )
    end as clinic_preferences_unit_price
    , preferences.last_unit_price_update as clinic_preferences_last_unit_price_update
    , preferences.is_deleted as clinic_preference_is_deleted
    -- Effective preferences
    , case
        when group_preferences.is_enabled = false then false
        when preferences.id is null then false
        when preferences.is_deleted = true then false
        else preferences.is_enabled
    end as effective_preferences_is_enabled
    , coalesce(
        nullif(preferences.name, '')
        , nullif(group_preferences.name, '')
        , inventories.name
    ) as effective_preferences_name
    , coalesce(
        group_preferences.unit_of_measure
        , inventories.unit_of_measure
    ) as effective_preferences_unit_of_measure
    , group_preferences.grade as effective_preferences_grade
    , clinic_preferences_min_quantity as effective_preferences_min_quantity
    , clinic_preferences_reorder_point as effective_preferences_reorder_point
    , coalesce(
        preferences.should_track_lot_number, false
    ) as effective_preferences_should_track_lot_number
    , clinic_preferences_unit_price as effective_preferences_unit_price
    -- Quantities (converted from global UOM to group UOM)
    , case
        when
            group_preferences.unit_of_measure is null
            or unit_conversions.id is null
            or group_preferences.unit_of_measure = inventories.unit_of_measure
            then coalesce(stock_by_inventory_clinic.stock_quantity, 0)
        else round(
            coalesce(stock_by_inventory_clinic.stock_quantity, 0
            )
            * (
                unit_conversions.target_qty / nullif(unit_conversions.source_qty, 0)
            ), 2
        )
    end as stock_quantity
    , case
        when
            group_preferences.unit_of_measure is null
            or unit_conversions.id is null
            or group_preferences.unit_of_measure = inventories.unit_of_measure
            then coalesce(preferences.in_transit_quantity, 0)
        else round(
            coalesce(preferences.in_transit_quantity, 0
            )
            * (
                unit_conversions.target_qty / nullif(unit_conversions.source_qty, 0)
            ), 2
        )
    end as in_transit_quantity
    , case
        when
            group_preferences.unit_of_measure is null
            or unit_conversions.id is null
            or group_preferences.unit_of_measure = inventories.unit_of_measure
            then coalesce(preferences.on_hold_quantity, 0)
        else round(
            coalesce(preferences.on_hold_quantity, 0
            )
            * (
                unit_conversions.target_qty / nullif(unit_conversions.source_qty, 0)
            ), 2
        )
    end as on_hold_quantity
    -- is_controlled:
    -- (federally controlled, or state-level controlled for clinic's address)
    , case
        -- null if not controlled substance exists
        when inventories.controlled_substance_id is null then false
        -- non-US clinics do not handle controlled substances
        when
            coalesce(clinics.primary_address_country, 'US') not in
            ('US', 'PR', 'GU', 'VI', 'AS', 'MP') then false
        -- if federally controlled then true
        when
            controlled_substances.is_federally_controlled = true then true
        -- if state-level controlled, check if state is in controlled_states
        when
            clinics.primary_address_state is not null
            and array_contains(
                upper(clinics.primary_address_state)::variant
                , controlled_substances.controlled_states
            ) then true
        else false
    end as is_controlled
    , greatest(
        coalesce(inventories.last_updated, '1900-01-01')
        , coalesce(preferences.last_updated, '1900-01-01')
        , coalesce(stock_by_inventory_clinic.last_updated, '1900-01-01')
        , coalesce(group_preferences.last_updated, '1900-01-01')
        , coalesce(clinics.last_updated, '1900-01-01')
        , coalesce(controlled_substances.last_updated, '1900-01-01')
        , coalesce(unit_conversions.last_updated, '1900-01-01')
    ) as last_updated
    , group_preferences.vetcove_corporate_id
from inventories
inner join group_preferences
    on inventories.id = group_preferences.inventory_id
inner join preferences
    on
        inventories.id = preferences.inventory_id
        and group_preferences.vetcove_corporate_id = preferences.vetcove_corporate_id
left join stock_by_inventory_clinic
    on
        inventories.id = stock_by_inventory_clinic.inventory_id
        and preferences.clinic_id = stock_by_inventory_clinic.clinic_id
left join clinics
    on preferences.clinic_id = clinics.id
left join controlled_substances
    on inventories.controlled_substance_id = controlled_substances.id
left join unit_conversions
    on
        inventories.id = unit_conversions.global_inventory_id
        and inventories.unit_of_measure = unit_conversions.source_unit_of_measure
        and group_preferences.unit_of_measure = unit_conversions.target_unit_of_measure