Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
inventory_idPart of composite primary key. Inventory unique identifier.id
inventory_is_deletedWhether the global inventory record is deleted.
codeThe unique inventory code for this global inventory item.code
kindThe kind/type of inventory item (e.g., STANDARD, CUSTOM, CUSTOM_ROLLUP).kind
inventory_category_idForeign key to the inventory_category table.category_id
global_preferences_nameThe name of the inventory item from global inventory.name
global_preferences_unit_of_measureThe unit of measure from global inventory.unit_of_measure
organization_preference_idForeign key to the inventory_corporate_preference table. The unique identifier for the organization/group inventory preference record. Null if no org preference exists.
organization_preferences_is_deletedWhether the organization/group inventory preference record is deleted. Null if no org preference exists.
organization_preferences_is_enabledWhether the inventory item is enabled at the organization/group level. Null if no organization preference exists for this inventory code.organization_preferences.is_enabled
organization_preferences_nameOrganization-level override name for the inventory item.organization_preferences.name
organization_preferences_unit_of_measureOrganization-level override for unit of measure.organization_preferences.unit_of_measure
organization_preferences_gradeThe grade classification set at the organization level.organization_preferences.grade
organization_preferences_codeOrganization-specific code for the inventory item.
effective_preferences_is_enabledFalse if no organization preference exists, otherwise the organization is_enabled value.effective_preferences.is_enabled
effective_preferences_nameResolved using hierarchy with nullif for empty strings: organization → global inventory name.effective_preferences.name
effective_preferences_unit_of_measureResolved using hierarchy: organization → global.effective_preferences.unit_of_measure
effective_preferences_gradeResolved from organization preferences only.effective_preferences.grade
last_updatedThe timestamp when this inventory 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.

​
SQL Definition

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

    One row per inventory code & vetcove corporate id.
*/

"
    )
}}

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

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

select
    -- Top-level identifiers
    inventories.id as inventory_id
    , inventories.is_deleted as inventory_is_deleted
    , inventories.code
    , inventories.kind
    , inventories.inventory_category_id

    -- Global preferences (from inventory)
    , inventories.name as global_preferences_name
    , inventories.unit_of_measure as global_preferences_unit_of_measure

    -- Organization preferences (nullable - null when no org pref exists)
    , group_preferences.id as organization_preference_id
    , group_preferences.is_deleted as organization_preferences_is_deleted
    , 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

    -- Effective preferences (resolved per API logic)
    , coalesce(group_preferences.is_enabled, false) as effective_preferences_is_enabled
    , coalesce(
        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
    -- Metadata
    , greatest(
        coalesce(inventories.last_updated, '1900-01-01')
        , coalesce(group_preferences.last_updated, '1900-01-01')
    ) as last_updated
    , coalesce(
        group_preferences.vetcove_corporate_id, inventories.vetcove_corporate_id
    ) as vetcove_corporate_id
from inventories
left join group_preferences
    on inventories.id = group_preferences.inventory_id