Stock Handling and Calculation Specification

Created: 2025-11-25 15:00
Author: AI Assistant
Status: Complete Audit
Last Updated: 2025-11-25

---

Table of Contents

1. Overview
2. Core Data Model
3. StoreItem Model - Foundation
4. CatalogItem Model - Business Logic
5. Item Model - Configuration
6. Database Views
7. Stock Reporting Contexts
8. EDI/External Feed Processors
9. Internal CRM Services
10. Stock Scopes Reference
11. Key Business Rules
12. Configuration Points
13. Migration History

---

Overview

The WarmlyYours inventory system manages stock across multiple warehouses (US, Canada) with different reporting requirements for internal CRM users versus external channels (website, EDI partners, Google feeds).

Key Distinction: Internal vs External Stock

Context	Stock Calculation	Alternate Warehouse	Purpose
External (Public)	reported_stock()	Fractional (25% default)	Website, EDI, Google feeds
Internal (CRM)	qty_available	None (0%)	Cycle counts, pick items, orders, quotes

Important: Internal CRM users see only their warehouse’s real stock. Alternate warehouse stock is only added for external reporting to indicate cross-warehouse fulfillment capability.

---

Core Data Model

Entity Relationships

Item (1) ─────────────────────────────────────────────────────────────────────────────────────────┐
  │                                                                                                │
  ├── qty_warn_on_stock (low stock warning threshold)                                             │
  ├── legacy_qty_out_of_stock (DEPRECATED - do not use for new logic)                             │
  │                                                                                                │
  ▼                                                                                                │
StoreItem (N per Item, per Store/Location)                                                        │
  │                                                                                                │
  ├── qty_on_hand          (physical count in warehouse)                                          │
  ├── qty_committed        (reserved for pending orders)                                          │
  ├── qty_available        (calculated: qty_on_hand - qty_committed)                              │
  ├── permanent_qty_available (override for unlimited inventory items)                            │
  │                                                                                                │
  ▼                                                                                                │
CatalogItem (N per StoreItem, per Catalog)                                                        │
  │                                                                                                │
  ├── reserve_stock        (stock to withhold - subtracted from available for external reporting) │
  ├── alternate_warehouse_stock_reporting_max (cap on alternate warehouse stock to report)        │
  │                                                                                                │
  ▼                                                                                                │
Catalog                                                                                            │
  │                                                                                                │
  └── alternate_warehouse_stock_fraction (default: 25, percentage of alt warehouse to report)     │

---

StoreItem Model - Foundation

File: app/models/store_item.rb

Core Quantity Fields

Field	Type	Description
qty_on_hand	Integer	Physical inventory count in warehouse
qty_committed	Integer	Reserved for pending orders/deliveries
qty_available	Integer	Calculated field (trigger-maintained)
permanent_qty_available	Integer	Override for unlimited inventory (services, virtual items)

Key Methods

qty_available

# Computed on the fly and also maintained by database trigger
# Formula: COALESCE(permanent_qty_available, GREATEST(qty_on_hand - qty_committed, 0))
def qty_available
  if qty_on_hand_changed? || qty_committed_changed? || permanent_qty_available_changed?
    [permanent_qty_available || ((qty_on_hand || 0) - (qty_committed || 0)), 0].min
  else
    [permanent_qty_available, super].compact.min
  end
end

qty_on_hand_with_held

# Includes HELD location stock for the same item
def qty_on_hand_with_held
  r = qty_on_hand || 0
  r += qty_on_hand_in_location('HELD') || 0 if available_location?
  r
end

qty_available_outside_order

# Available quantity excluding what's already committed to a specific order
def qty_available_outside_order(order)
  committed_from_order = inventory_commits.joins(:line_item)
    .where(line_items: { resource_type: 'Order', resource_id: order.id })
    .sum(:quantity)
  qt_avail = permanent_qty_available || qty_on_hand
  (qt_avail - qty_committed) + committed_from_order
end

qty_backordered

# Sum of quantities on back orders
# Calculated via SQL join to orders in 'crm_back_order' state

on_order

# Returns array of purchase order data with expected delivery dates
def on_order
  Inventory::ItemOnOrder.new.process(store_id: store_id, item_id: item_id).on_order
end

next_available / next_available_with_depth_limit

# Calculates when stock will be available based on incoming purchase orders
# For kits: analyzes all components and returns the latest date with smallest buildable quantity
# Depth-limited version prevents infinite recursion in kit structures

Store Item Locations

Location	Purpose
AVAILABLE	Primary selling inventory
HELD	Temporarily held (included in some calculations)
SCRAP	Damaged/unsellable
OBSOLETE	Discontinued items
REFURB	Items being refurbished
TECH	Technical/testing
RESTOCK	Pending restock
CLAIM	Warranty claims
CLAIM_SUBMITTED	Claims submitted to supplier
SERVICEVAN	Service vehicle inventory
MARKETING	Marketing samples

---

CatalogItem Model - Business Logic

File: app/models/catalog_item.rb

Stock Configuration Fields

Field	Type	CRM Label	Description
reserve_stock	Integer	“Reserve Stock”	Stock to withhold/subtract from available before reporting
alternate_warehouse_stock_reporting_max	Integer	“Max Alternate Warehouse Stock”	Cap on alternate warehouse stock to include in reporting
min_stock_to_report	Integer	“Minimum Stock to Report”	Minimum floor - always report at least this amount

Note: These fields were renamed in November 2025 from min_reported_stock → reserve_stock and always_available → min_stock_to_report for clarity.

Key Methods

alternate_warehouse_stock_fraction

# Returns the fraction (as decimal) of alternate warehouse stock to report
# Uses catalog-level setting, defaults to 25% (0.25)
# This is for EXTERNAL reporting only (feeds, website, EDI)
def alternate_warehouse_stock_fraction
  (catalog&.alternate_warehouse_stock_fraction || 25) / 100.0
end

real_stock (Internal CRM Use)

# Returns REAL stock available for internal CRM use
# Typically called WITHOUT use_alternate_warehouse for internal purposes
# Use this for cycle counts, pick items, order management, quotes, etc.
def real_stock(use_store_item: nil, use_alternate_warehouse: false)
  use_store_item ||= store_item
  qty_available = use_store_item.qty_available.to_i
  if use_alternate_warehouse
    # This option exists but is NOT typically used for internal CRM
    alternate_warehouse_store_items.each do |alternate_si|
      qty_available += alternate_si.qty_available.to_i
    end
  end
  qty_available
end

Note: For internal CRM use, use_alternate_warehouse should be false (the default). CRM users need to see only their warehouse’s actual stock.

reported_stock (External Reporting)

# Stock for EXTERNAL reporting (feeds, website, EDI)
# Uses catalog's alternate_warehouse_stock_fraction and reserve_stock
# Discontinued and pending_discontinue items report 0
def reported_stock(use_store_item: nil, use_alternate_warehouse: false, safety_stock: nil)
  return 0 if discontinued? || pending_discontinue?

  safety_stock ||= reserve_stock.to_i
  use_store_item ||= store_item
  qty_available = use_store_item.qty_available.to_i

  if use_alternate_warehouse
    # Report a fraction (default 25%) of alternate warehouse stock
    fraction = alternate_warehouse_stock_fraction
    alternate_warehouse_store_items.each do |alternate_si|
      alternate_stock = (alternate_si.qty_available * fraction).ceil
      # Cap at alternate_warehouse_stock_reporting_max if set
      alternate_stock = [alternate_stock, alternate_warehouse_stock_reporting_max].min if alternate_warehouse_stock_reporting_max.present?
      qty_available += alternate_stock
    end
  end

  # Subtract safety stock (reserve_stock)
  qty_available = [qty_available - safety_stock, 0].max

  # Apply always_available floor and permanent_qty_available
  [qty_available, always_available, use_store_item.permanent_qty_available].compact.max
end

out_of_stock

# Uses reserve_stock for out-of-stock determination
def out_of_stock(use_threshhold = nil)
  stock_reserved = use_threshhold || reserve_stock.to_i
  return true if store_item && (store_item.qty_available - stock_reserved) <= 0
  false
end

product_stock_status

def product_stock_status
  if item.always_available_online? || !out_of_stock
    'InStock'
  else
    'OutOfStock'
  end
end

on_order_for_store_item

# Returns on order quantities
# For internal CRM use: call with use_alternate_warehouse: false (default)
# For external reporting: call with use_alternate_warehouse: true
def on_order_for_store_item(use_store_item: nil, use_alternate_warehouse: false)
  return 0 if discontinued? || pending_discontinue?

  use_store_item ||= store_item
  on_order_arr = use_store_item.on_order
  if use_alternate_warehouse
    # Include alternate warehouse on-order (with 1 week delay) - for external reporting only
    alternate_warehouse_store_items.each do |alternate_si|
      alt_on_order_arr = alternate_si.on_order
      alt_on_order_arr.each { |h| h[:promised_delivery_date] = (h[:promised_delivery_date] + 1.week) }
      on_order_arr.concat(alt_on_order_arr)
    end
  end
  on_order_arr
end

next_available

# Returns next available date/quantity
# For internal CRM use: call with use_alternate_warehouse: false (default)
# For external reporting: call with use_alternate_warehouse: true
def next_available(use_store_item: nil, use_alternate_warehouse: false)
  return nil if discontinued? || pending_discontinue?
  # When use_alternate_warehouse is true, includes alternate warehouse with 1 week delay
  # ...
end

Stock Scopes

# For internal CRM use (100% real stock)
scope :in_stock, -> {
  with_item.where('store_items.qty_available - COALESCE(catalog_items.reserve_stock, 0) > 0 OR items.always_available_online IS TRUE')
}

scope :in_stock_with_alternate_warehouse_store_items, -> {
  with_item.where("(store_items.qty_available +
    (select round(coalesce(sum(si.qty_available),0),0)
    from store_items si inner join stores s on si.store_id = s.id and s.owner = 'warmlyyours'
    where si.store_id <> store_items.store_id and si.item_id = store_items.item_id and si.location = 'AVAILABLE'))
    - COALESCE(catalog_items.reserve_stock, 0) > 0 OR items.always_available_online is true")
}

scope :out_of_stock, -> {
  with_item.where('store_items.qty_available - COALESCE(catalog_items.reserve_stock, 0) <= 0 AND items.always_available_online IS NOT TRUE')
}

---

Item Model - Configuration

File: app/models/item.rb

Stock Threshold Fields

Field	Type	Description	Status
qty_warn_on_stock	Integer	Low stock warning threshold	Active
legacy_qty_out_of_stock	Integer	Old out-of-stock threshold	DEPRECATED
always_available_online	Boolean	Always show as in stock on website	Active

Validations

validates_numericality_of :qty_warn_on_stock, greater_than_or_equal_to: 0
validates_numericality_of :legacy_qty_out_of_stock, greater_than_or_equal_to: 0

Scopes

# Uses legacy_qty_out_of_stock - should be updated to use CatalogItem.reserve_stock
scope :out_of_stock, -> {
  joins(:store_items).where('store_items.qty_available <= items.legacy_qty_out_of_stock')
    .where("store_items.location = 'AVAILABLE'")
}

scope :stock_warning, -> {
  joins(:store_items).where('store_items.qty_available <= items.qty_warn_on_stock')
    .where("store_items.location = 'AVAILABLE'")
}

---

Database Views

view_product_catalogs (v44)

File: db/views/view_product_catalogs_v44.sql

Used for website product display and Google feeds.

-- Stock status calculation
CASE
  WHEN (si.qty_available - COALESCE(ci.reserve_stock, 0) <= 0) THEN 'OutOfStock'
  ELSE 'InStock'
END as product_stock_status,

-- Exposed fields
si.qty_available AS store_item_qty_available,
ci.reserve_stock as catalog_item_stock_reserved,
ci.alternate_warehouse_stock_reporting_max,

view_item_catalogs (v11)

File: db/views/view_item_catalogs_v11.sql

Used for CRM item management.

i.qty_warn_on_stock as item_warn_on_stock,
i.legacy_qty_out_of_stock as item_qty_out_of_stock,  -- Legacy field
ci.reserve_stock as catalog_item_stock_reserved,

view_catalog_items (v06)

File: db/views/view_catalog_items_v06.sql

Used for catalog item listings.

i.qty_warn_on_stock,
i.legacy_qty_out_of_stock AS qty_out_of_stock,  -- Legacy alias
ci.reserve_stock AS catalog_item_stock_reserved,

view_stocks (v01)

File: db/views/view_stocks_v01.sql

Aggregated stock view across US and Canada.

-- Stock status based on qty_warn_on_stock
CASE
  WHEN usa_on_hand <= usa_committed or can_on_hand <= can_committed THEN 'out_of_stock'
  WHEN ((usa_on_hand - usa_committed) < COALESCE(item_stock.qty_warn_on_stock,5)
    or (can_on_hand - can_committed) < COALESCE(item_stock.qty_warn_on_stock,5)) THEN 'stock_warning'
  ELSE 'in_stock'
END as stock_status,

---

Stock Reporting Contexts

External (Public) Reporting

Channel	Method	Alternate Warehouse	Notes
Website	product_stock_status via view	N/A (view-based)	Uses reserve_stock
Google Feeds	reported_stock()	Fractional	Via view_product_catalogs
Amazon	reported_stock()	Fractional (US: disabled)	High tariff concerns for US
Walmart	reported_stock()	Fractional	Via EDI processor
CommerceHub	reported_stocks()	Fractional	Via EDI processor
MFT Gateway	reported_stocks()	Fractional	Via EDI processor
Costco	reported_stock()	Fractional	Via file format

Internal (CRM) Reporting

Feature	Method	Alternate Warehouse	Notes
Cycle Counts	qty_on_hand, qty_warn_on_stock	None (0%)	Primary warehouse only
Pick Items	qty_available, catalog_item_stock_reserved	None (0%)	Primary warehouse only
Order Management	qty_available_outside_order()	None (0%)	Primary warehouse only
Quotes	qty_available	None (0%)	Primary warehouse only
Heating Calculator	in_stock_with_alternate_warehouse_store_items	100%*	*Exception: shows combined availability
Stock Shuffler	qty_on_hand, qty_available	None (0%)	Primary warehouse only

Note: The Heating Calculator is an exception - it uses alternate warehouse stock to show customers what’s available for fulfillment across all warehouses.

---

EDI/External Feed Processors

Amazon Inventory Processor

File: app/services/edi/amazon/inventory_message_processor.rb

Uses reported_stock() with fractional alternate warehouse.

Amazon Fulfillment Availability

File: app/services/edi/amazon/json_listing_generator/attributes/fulfillment_availability.rb

# US: alternate warehouse disabled due to tariffs
use_alternate_warehouse = true
use_alternate_warehouse = false if catalog_item.catalog.country_iso3 == 'USA'
quantity = (discontinued ? 0 : catalog_item.reported_stock(use_alternate_warehouse: use_alternate_warehouse, safety_stock: 0))

Walmart Inventory Processor

File: app/services/edi/walmart/inventory_message_processor.rb

stock = (discontinued ? 0 : ci.reported_stock(use_alternate_warehouse: true, safety_stock: 0))

CommerceHub Inventory Processor

File: app/services/edi/commercehub/inventory_message_processor.rb

stocks = ci.reported_stocks(use_alternate_warehouse: true)
total_available = stocks.values.sum

MFT Gateway Inventory Processor

File: app/services/edi/mft_gateway/inventory_message_processor.rb

stocks = ci.reported_stocks(use_alternate_warehouse: true)
total_available = stocks.values.sum

Channel Engine Inventory Processor

File: app/services/edi/channel_engine/inventory_message_processor.rb

Uses qty_on_hand directly (not reported_stock).

Mirakl Seller Price Processor

File: app/services/edi/mirakl_seller/price_message_processor.rb

Uses qty_available or permanent_qty_available directly.

---

Internal CRM Services

Cycle Count Prioritizer

File: app/services/item/cycle_count_prioritizer.rb

Uses real stock (qty_on_hand, qty_warn_on_stock):

# +20 if below warning level and last count more than 4 weeks ago
if si.item.qty_warn_on_stock && (si.qty_on_hand <= si.item.qty_warn_on_stock) && (last_counted.nil? || (last_counted < 4.weeks.ago))
  priority += 20
end

Stock Shuffler

File: app/services/inventory/stock_shuffler.rb

Uses real stock (qty_on_hand, qty_available):

small_item_qty = small_item.qty_on_hand
qty_to_spread = [qty_to_spread, small_item.qty_available].min

Heating System Calculator

File: app/services/heating_system_calculator/heating_system_items.rb

Uses in_stock_with_alternate_warehouse_store_items scope (100% real stock):

res = res.in_stock_with_alternate_warehouse_store_items if @heating_system.use_in_stock_only

Item Base Generator (Feed)

File: app/services/feed/item_base_generator.rb

Uses raw qty_available:

xml.quantity_available ci.store_item.qty_available

Google Local Inventory Generator

File: app/services/feed/google/local_inventory_generator.rb

Uses view’s store_item_qty_available:

xml.send :'g:quantity', cip.store_item_qty_available

---

Stock Scopes Reference

CatalogItem Scopes

Scope	Purpose	Stock Type
in_stock	Items with available stock	Real (100%)
in_stock_with_alternate_warehouse_store_items	Items with stock including alt warehouse	Real (100%)
out_of_stock	Items without available stock	Real (100%)

Item Scopes

Scope	Purpose	Notes
out_of_stock	Items below legacy threshold	Uses legacy_qty_out_of_stock (deprecated)
stock_warning	Items below warning threshold	Uses qty_warn_on_stock

StoreItem Scopes

Scope	Purpose
available	Items in AVAILABLE location
active	Non-discontinued items
backorders_only	Items with back orders

---

Key Business Rules

1. External Stock Reporting Formula

reported_stock = qty_available
               + (alternate_warehouse_qty × alternate_warehouse_stock_fraction)
               - reserve_stock  # subtract reserve stock

# With caps and floors:
- alternate_stock capped at alternate_warehouse_stock_reporting_max (if set)
- Final result floored at min_stock_to_report (if set)
- Final result floored at permanent_qty_available (if set)

Example:

• qty_available = 10
• alternate_warehouse_qty = 20, fraction = 0.25 → adds 5
• reserve_stock = 3 (reserve to withhold)
• min_stock_to_report = 2 (minimum floor)
• Result: max((10 + 5 - 3), 2) = max(12, 2) = 12

2. Out of Stock Determination

out_of_stock = (qty_available - reserve_stock) <= 0
             AND NOT always_available_online

3. Internal Stock (CRM)

internal_stock = qty_available (primary warehouse only, no alternate warehouse)

4. Stock Warning

stock_warning = qty_available <= qty_warn_on_stock

5. Discontinued Items

- Discontinued or pending_discontinue items always report 0 stock externally
- Internal CRM still shows real stock for inventory management

---

Configuration Points

Catalog Level

Field	Default	Description
alternate_warehouse_stock_fraction	25	Percentage of alternate warehouse stock to report externally

CatalogItem Level

Field	Default	Description
reserve_stock	nil	Reserve stock to withhold (subtracted from available before reporting)
alternate_warehouse_stock_reporting_max	nil	Cap on alternate warehouse stock to include
min_stock_to_report	nil	Minimum floor - always report at least this amount

Item Level

Field	Default	Description
qty_warn_on_stock	5	Low stock warning threshold
legacy_qty_out_of_stock	2	DEPRECATED - Use reserve_stock instead
always_available_online	false	Always show as in stock

StoreItem Level

Field	Default	Description
permanent_qty_available	nil	Override for unlimited inventory

---

Migration History

November 2025

1. Renamed qty_out_of_stock to legacy_qty_out_of_stock

   • Migration: 20251126000001_rename_item_stock_thresholds_to_legacy.rb
   • Purpose: Deprecate item-level threshold in favor of CatalogItem.reserve_stock

2. Added alternate_warehouse_stock_fraction to Catalog

   • Migration: 20251126000004_add_alternate_warehouse_stock_fraction_to_catalogs.rb
   • Purpose: Make alternate warehouse reporting percentage configurable per catalog
   • Removed global constant: ItemConstants::ALTERNATIVE_WAREHOUSE_STOCK_FRACTION_TO_REPORT

3. Updated Database Views

   • view_product_catalogs v43 → v44: Uses reserve_stock instead of qty_out_of_stock
   • view_item_catalogs v10 → v11: Uses reserve_stock
   • view_catalog_items v05 → v06: Uses reserve_stock

4. Renamed CatalogItem stock fields for clarity

   • Migration: 20251126000005_rename_catalog_item_stock_fields.rb
   • min_reported_stock → reserve_stock (stock to withhold from reporting)
   • always_available → min_stock_to_report (minimum floor to report)
   • view_item_catalogs v10 → v11: References legacy_qty_out_of_stock
   • view_catalog_items v05 → v06: References legacy_qty_out_of_stock

5. Separated Internal vs External Stock Logic

   • reported_stock(): External reporting with fractional alternate warehouse (25% default)
   • Internal CRM: Uses qty_available directly (no alternate warehouse)
   • use_alternate_warehouse parameter defaults to false for internal use

---

Appendix: Method Quick Reference

For External Reporting (Website, EDI, Feeds)

# Get stock for external reporting
catalog_item.reported_stock(use_alternate_warehouse: true)

# Get stock status for website
catalog_item.product_stock_status  # => 'InStock' or 'OutOfStock'

# Check if out of stock
catalog_item.out_of_stock  # => true/false

For Internal CRM Use

# Get raw available quantity (primary warehouse only)
store_item.qty_available
catalog_item.qty_available  # delegates to store_item

# Get quantity available excluding specific order
store_item.qty_available_outside_order(order)

# Get on-order quantities (primary warehouse only)
catalog_item.on_order_for_store_item(use_alternate_warehouse: false)

# Get next available date (primary warehouse only)
catalog_item.next_available(use_alternate_warehouse: false)

Note: Internal CRM methods should NOT use use_alternate_warehouse: true. CRM users need to see their warehouse’s actual stock for picking, counting, and order fulfillment.

For Stock Warnings

# Check low stock warning
line_item.stock_status  # => :ok, :low, or :oos

# Uses item.qty_warn_on_stock for :low threshold