Inventory Code

Organization-level inventory code enriched model providing global preferences, organization preferences, and effective preferences.

• Each row represents an inventory & vetcove_corporate_id.
• Compatible with the Vetcove API endpoint(s): /inventory-codes/
• API endpoint(s): https://connect.vetcove.com/ext/v2/inventory-codes/
• Snowflake view: phd_enriched.inventory_code_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, CUSTOM, CUSTOM_ROLLUP).	kind
inventory_category_id	Foreign key to the inventory_category table.	category_id
global_preferences_name	The name of the inventory item from global inventory.	name
global_preferences_unit_of_measure	The unit of measure from global inventory.	unit_of_measure
organization_preference_id	Foreign 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_deleted	Whether the organization/group inventory preference record is deleted. Null if no org preference exists.
organization_preferences_is_enabled	Whether 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_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.
effective_preferences_is_enabled	False if no organization preference exists, otherwise the organization is_enabled value.	effective_preferences.is_enabled
effective_preferences_name	Resolved using hierarchy with nullif for empty strings: organization → global inventory name.	effective_preferences.name
effective_preferences_unit_of_measure	Resolved using hierarchy: organization → global.	effective_preferences.unit_of_measure
effective_preferences_grade	Resolved from organization preferences only.	effective_preferences.grade
last_updated	The timestamp when this inventory record was last updated. This is the most recent timestamp from all related tables.
vetcove_corporate_id	The unique identifier of the corporate group associated with this record.

SQL Definition

View SQL

/*
    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