Stock position

1-- core.unleashed.stock_position
2-- Stock on hand, availability, value and slow movers per product, as of the last
3-- sync. Unleashed's Stock on Hand report with reorder flags.
4-- Grain: product, all warehouses combined.
5--
6-- The synced stock on hand is summed over warehouses (see
7-- staging.unleashed.stock_on_hand), so the flags compare total availability with
8-- the product-level alert levels. Value is quantity on hand x average cost, in
9-- base currency.
10
11SELECT
12    s.product_id,
13    s.product_code,
14    COALESCE(p.product_description, s.product_description)     AS product_description,
15    COALESCE(p.product_group, s.product_group)                 AS product_group,
16    p.product_brand,
17    p.default_supplier,
18    p.is_obsolete,
19    s.qty_on_hand,
20    s.allocated_qty,
21    s.available_qty,
22    s.on_purchase_qty,
23    s.avg_cost,
24    s.total_cost                                               AS stock_value,
25    p.min_stock_alert_level,
26    p.max_stock_alert_level,
27    s.available_qty < p.min_stock_alert_level                  AS is_below_min,
28    s.qty_on_hand > p.max_stock_alert_level
29        AND p.max_stock_alert_level > 0                        AS is_above_max,
30    s.days_since_last_sale,
31    CASE
32        WHEN s.qty_on_hand <= 0                THEN NULL
33        WHEN s.days_since_last_sale IS NULL    THEN 'never_sold'
34        WHEN s.days_since_last_sale > 180      THEN 'over_180_days'
35        WHEN s.days_since_last_sale > 90       THEN '91_180_days'
36        ELSE 'sold_last_90_days'
37    END                                                        AS sales_recency_bucket
38FROM {{staging.unleashed.stock_on_hand}} s
39LEFT JOIN {{staging.unleashed.product}} p USING (product_id)