# Rule: Inventory & Stock Valuation

How UNO stores stock, and how the inventory tools value it. (Phase 2.)

## The model
- **Item = two levels:** `stock` (product) → `StockBar` (the SKU/barcode/UOM variant). The universal key is **`stkbar_id`**.
- **On-hand is a cached balance** held per `stkbar_id` × warehouse in `StockCalculateValue` (`qun_onhand`, `qun_reserved`, `qun_available`, `qun_on_order`). Do **not** re-sum movements to get current on-hand.
- **Cost is weighted-average**, stored per bar as `stk_Cost_LL` / `stk_Cost_USD` / `stk_Cost_FC`.
- **Stock value = qun_onhand × stk_Cost.** Reported in BOTH LBP and USD — never add the two currencies.
- The convenient report view **`vwStockListRpt`** already joins product + bar + on-hand + cost + warehouse + category. The tools build on it.

## Valuation rules the tools apply
- **Company-scoped**, and **services excluded** (`IsService = 0` — services hold no stock).
- **All stock bars are summed.** On-hand is tracked independently per bar (a pack-UOM bar and a base-UOM bar each carry their own real balance), so summing all bars is the true total. Do NOT filter to base-UOM only — that would drop real stock held in pack units.
- Sentinel "unbalanced" probe rows (`qun_onhand` ≈ −1e14) are filtered out.
- Verified company total ≈ **9.08B LBP / $6.02M** (a bakery/food manufacturer).

## Data-quality flags (report these as findings)
- **On-hand with zero cost** → valuation gap; the stock value is understated for those items.
- **Negative stock** (`qun_onhand < 0`) → oversold or a data issue; real in this DB, flag it, don't hide it.

## Reconciling to the books
Inventory value is a *stock-module* number; it won't always equal the inventory/stock **GL** account in `trial_balance` (postings can lag). Compare the two, but treat a gap as expected rather than an error.

## Movements, sales & profit
- **Dates carry a time of day** (`inp_edate` and GL `trp_valuedate` are `datetime`, >90% with hh:mm:ss). A period's `to` date is therefore inclusive of the whole day: the tools filter `date < DATEADD(day, 1, to)`, never `date <= to` (which silently dropped the last day of every period — fixed 2026-10-31).
- **Invoice date = `inp_edate` — the ONLY date used for period filters, grouping, trends and sorting.** `inp_vdate` is a *value date* (payment/credit-term date) and must never drive a period report; using it produced wrong sales figures for clients (fixed 2026-10-31). UNO's own reports filter on `inp_edate`.
- Stock movements are invoice lines in **`vwInvoice`** (line grain, everything joined). Direction = `int_inout` ('I' in / 'O' out / 'N' non-stock like orders & quotations).
- **A "sale" is a specific set of document types**, not just any OUT: sales OUT = `SA` (Sales Invoice), `SL` (Sales Local), `DN` (Delivery Note), `CS` (cash sale), plus **`SD` "Sales Invoice (Delivered)"** — `int_inout = 'N'` (the stock already left with the DN) but UNO sums it with SA/SL in every sales report (cStockReports.vb LoadSalesAnaylsis, cInvoice.vb GetPosDailySummarySales), so the tools do too (added 2026-11-09). Purchases IN = `PU`, `PL`, `GR`. Returns = `SR`/`RS` (sales), `PR`/`RP` (purchase). Transfers (`TI`/`TO`), damage (`DA`/`DM`), assembly (`AI`/`AO`), unpacking (`UP`), adjustments (`IO`/`SI`/`ST`/`OT`/`OO` and their LBP-book twins `SX`/`TX`) are NOT sales — the sales tools exclude them. `list_invoice_types` shows every code a company uses and how the tools classify it.
- **`YF` "Yearly First Inventory" (and `YX`) are RESTATEMENTS of opening stock, not receipts.** UNO re-states every item's balance on Jan-1 with a YF document; when on-hand is rebuilt from movements (ΣIN − ΣOUT) they must be excluded (cStockReports.vb :4235). Verified on LPHARM 2026-11-09: Σ(I−O) excluding YF matches the stored balance on 2,840/2,840 items; including YF only 1,944. `FI` (First Inventory) IS a real initial receipt and stays in. `stock_valuation_asof`, `item_locations` and `item_movement_summary` apply this; `item_movement_summary` reports the YF quantity separately.
- **Cost of goods sold** = each sale line's FROZEN cost `inc_cost_LL` / `inc_cost_USD` (stored at the moment of sale, both currencies) × qty — not today's cost.
- **Two discounts — both are netted from revenue.** Each line carries a line discount `inc_disc` (a %) which UNO already bakes into the stored `inc_lineTotal`. The invoice header carries a **global discount `inp_discpercent`** (a %, amount in `inp_gdisc`) which is NOT in the line totals — the tools apply it: net line = `inc_lineTotal × (1 − inp_discpercent/100)`, so Σ net lines = `inp_gtotal − inp_vat`. Revenue, spend, gross profit, discounts_given and stock-card line totals are all net of both discounts (fixed 2026-10-31 after clients reported overstated sales).
- **POS invoices store VAT-INCLUSIVE prices.** When `POS_ID` is set and the line is VATable (`inc_vat` = rate %, e.g. 11), `inc_lineTotal` includes VAT; the tools divide by (1 + inc_vat/100) so revenue is ex-VAT like UNO's own reports (`inc_price/1.11`). Non-POS invoices are already ex-VAT. Bakery: POS VAT was 4.1% of reported sales before this fix.
- **Revenue currency:** `inc_price` / `inc_lineTotal` are in the **document currency** (`inp_acc_curr`: 01=LBP, 02=USD, 03+=FC), NOT always LBP. The tools convert revenue to LBP and USD using the invoice rates: `inp_rate` = LBP per 1 USD; **`inp_rateFC` = USD per 1 FC unit** (EUR ≈ 1.1-1.2), so FC→USD = × `inp_rateFC` and FC→LBP = × `inp_rate` × `inp_rateFC` (UNO `cInvoice.vb:2226/2233`; verified against the frozen `inc_cost_USD/LL`). Never compare raw `inc_lineTotal` to `inc_cost_LL` — that mixes currencies.
- **Sales are NET of sales returns, as UNO reports them** (item/customer reports = SA − SR; stock card NetQ_SA = Q_SA − Q_SR). Every sales tool includes SR/RS documents with a negative sign (qty, revenue, COGS); entity-level revenue (customer_profitability, customer_trend) also subtracts credit notes (CN/CB). `sales_returns_by_item` / `sales_returns` show the gross returns alone. Purchases likewise: `purchases_summary` and sell-through are NET of purchase returns (PR/RP); `purchase_returns` shows them alone.
- **Walk-in / POS sales (UNO rule):** the invoice has `POS_ID` set and its third party is the till's **cash account** (e.g. "Cash On Hand - POS-LBP"), so the name is NOT null. A named customer = `thirdparty_type = 'Client'`. Tools group everything else as "(cash / walk-in)" and never use name heuristics.
- **Document type sets per company:** `stock_adjustments` lists any stock-moving `int_code` that none of the tools classify (`unclassified_movement_types`) — check it on a new client before trusting movement totals.

## Tools
- **`inventory_value`** — total stock value (LBP & USD), `by=warehouse|category`, data-quality flags.
- **`stock_on_hand`** — per-item qty + value, availability, `by_warehouse`, `below_reorder`, search/filter.
- **`negative_stock`** / **`stockouts`** — oversold items / out-of-stock (optionally selling_only).
- **`stock_movements`** — stock card for one item (signed in/out, price, cost, party).
- **`top_selling_items`** — best/worst sellers by revenue, qty or gross profit; by item or category.
- **`product_profitability`** — revenue, COGS, gross profit & margin % per item/category (LBP & USD), loss-making flag.
- **`inventory_turnover`** — COGS ÷ inventory value + days of inventory (DIO), overall or by category.
- **`slow_moving_stock`** — items not sold in N days and the cash value locked up.
- **`reorder_suggestions`** — velocity-based (item reorder points are usually unset): avg daily sales → days of cover → suggested order qty.
- **`item_price`** — selling price vs cost, markup %, below-cost flag.
- (`expiring_stock` not yet built — expiry is lot-level, needs a lot-balance model.)

See `docs/PHASE2_INVENTORY_REFERENCE.md` for the full schema and SQL recipes.