# Product Need Calculation

Product Need view shows product stock balance development and future needs based on sales orders, production orders, purchase orders, and material requirements.

## Views

Two views are available via the Balance/Need tab:

| Tab | Description |
|-----|-------------|
| Balance | Weekly summary per product with purchase order creation |
| Need | Detailed row-level view showing each event and calculation |

## Checkbox Settings

These checkboxes control what is calculated and displayed. Clicking any calculation checkbox triggers an update.

### Calculation Checkboxes

| Checkbox | Default | Description |
|----------|---------|-------------|
| Negative start balance | off | Include negative starting stock balance in running balance |
| Negative end balance | on | Add need when final balance is negative |
| Minimum stock | on | Add need when balance falls below product minimum stock |
| Action limit | on | Warn when balance falls below product action limit |
| Estimate order | on | Include estimate orders in calculation |
| Critical date | off | Calculate critical date when stock runs out |
| Only sales need | off | Calculate only from sales orders, disables minimum stock, action limit, negative end balance, and critical date |

### Display Checkboxes

| Checkbox | Default | Description |
|----------|---------|-------------|
| Only need | on | Show only products that have need (negative balance or need down) |
| Hide empty | on | Hide products with zero balance and no transactions. Disabled when Only need is on |
| Has error | off | Show only products that have errors |
| Select upper | on | Highlight upper level (parent) products when row is selected |
| Select lower | on | Highlight lower level (child) products when row is selected |
| Chart current | off | Show only the chart for the currently selected product |
| Create schedule | off | Mark matching production orders as scheduled |
| Virtual production order | off | Create virtual production order IDs for need rows without existing orders |

### Advanced Checkboxes

| Checkbox | Default | Description |
|----------|---------|-------------|
| Incremental | off | Send only changed data on updates |
| Reload data | off | Force full data reload from database |
| Old work date | off | Use old work date in calculation |
| Max down | 500 | Maximum iterations for cascade loop |

## Product-Level Settings

Each product has settings that control how it participates in the calculation. These are set on the product record:

| Setting | Description |
|---------|-------------|
| Need calculation in use | When off, product is excluded from calculation. Auto-enabled when not set. Also off when product itself is marked not in use |
| Stock balance | Current stock balance, used as starting balance |
| Action limit | Per-product action limit threshold |
| Minimum stock | Per-product minimum stock level |
| Critical end date | Saved critical end date, compared against calculated value |

## Row Types (db column)

Each row in the need calculation has a source type shown in the **db** column:

| db value | Description |
|----------|-------------|
| product | Product header row with starting balance |
| work | Production order (positive = incoming) |
| purchase | Purchase order (positive = incoming) |
| sales | Sales order (negative = outgoing) |
| material | Material requirement (negative = outgoing) |
| estimate | Estimate need row (predicted demand from this product) |
| est. | Upper level estimate need (estimate from parent product) |
| act. limit | Action limit warning row |
| actl. | Upper level action limit |
| neg. balance | Negative end balance row (synthetic) |
| balance | Upper level negative end balance |

### Generated Production Order IDs

When **Virtual production order** is on, sales and material rows that have need down but no existing production order get a generated ID:

- Format: `V-{order id}.{order row}` where prefix `V-` is configurable
- Example: sales order `12345` row `2` becomes `V-12345.2`
- These rows show brown color in the grid
- Requires both order id and order row to exist on the row, otherwise an error is shown

When **Create schedule** is on, rows with an existing production order get a suffix appended:

- Suffix is configurable (default: `c`)
- Example: production order `2024/123` becomes `2024/123c`

## Calculation Rules

### Running Balance

For each product, rows are sorted by date, then by type, then by order. Running balance is calculated:

- If start balance is negative and **Negative start balance** is off: first event row balance = row amount only (start balance ignored)
- Otherwise: balance = previous balance + row amount

### Need Down

Need down is the amount that must be procured or produced to cover a shortage. It flows down the product tree from finished products to raw materials.

#### When need down is set

| Condition | Need down amount | Notes |
|-----------|-----------------|-------|
| Production order row (positive amount) | amount | Only for products that have materials in the work structure |
| Balance goes negative (negative amount row) | -amount | Only for products in the product tree |
| Schedule row | abs(amount) | When previous balance minus amount would be negative |
| Last row, negative end balance on | gap to previous need down | Covers the remaining shortage |
| Last row, minimum stock on (final pass only) | minimum stock - balance | When balance is below minimum stock |
| Product with only start balance, negative, no events | -balance | Synthetic row created with amount 0 |

#### When need down is cleared

- Row amount is zero (with error message for production/sales/material rows)
- Product has need calculation in use = off
- Row source database is marked as not in use
- After calculation: need down less than or equal to 0 is removed
- If balance has recovered to or above previous need down level, need down is cleared

#### Σ info

Σ appears when need down is reduced because part of the shortage was already counted in an earlier row. The Σ value shows the amount already counted.

Example: product starts at balance 0 and has three sales rows:

| Row | Amount | Balance | Need down before Σ | Lowest negative | Action |
|-----|--------|---------|----------------------|-----------------|--------|
| Sales A | -5 | -5 | 5 | -5 | New low, need down = 5 |
| Production | +3 | -2 | - | -5 | Balance recovered, no new need |
| Sales B | -4 | -6 | 4 | -6 | New low, need down reduced to 1 |

At row 3, sales B creates need down of 4. But sales A already caused need down of 5 which covers most of the shortage. Need down is reduced to 1 — only the new part. Σ shows `Σ5` meaning 5 was already counted.

Σ info is shown only when the reduction amount differs from the final need down value.

### Negative Start Balance

When **on**:

- Negative starting stock balance is included in the running balance
- All events add to the balance which may start negative
- The first row shows info `"negative"` when header balance is negative
- Last row: if balance is still negative and need down exists, it is increased to cover the full shortage

When **off**:

- If start balance is negative, the first event row ignores it: balance = row amount only
- The running balance starts from zero (from the first event)
- Negative end balance still works on the last row balance (which may be negative from events)

### Negative End Balance

When **on** and product has no event rows (only start balance):

- Creates a synthetic row with amount 0 and balance = start balance
- Sets need down = -balance if start balance is negative and negative start balance is on

When **on** and product has event rows:

- On the last row: if balance < 0, sets need down to cover the remaining shortage
- The "neg. balance" cause label is shown in the Info column

### Minimum Stock

When **on** (final pass only):

- On the last row: if balance < minimum stock, sets need down = minimum stock - balance
- Combined with negative end balance: if both apply, the larger need down is used
- Info column shows "min. stock" or "neg. balance, min. stock"

### Action Limit

When **on**:

- Warning is shown when product balance falls below action limit
- Action limit value comes from the product record

### Critical Date

When **on**:

- Calculates the date when stock balance first goes to zero or below
- Compares calculated critical date against product's saved critical end date
- If they differ, an error is shown on the product

### Only Sales Need

When **on**:

- Calculation only uses sales orders (no production orders, purchase orders, or material requirements)
- Starting balance is set to 0 instead of stock balance
- Automatically disables: minimum stock, action limit, negative end balance, critical date

## Estimate Need

Estimate need predicts future product demand from historical sales. It calculates a daily consumption rate over a lookback period, then inserts negative rows to represent expected future demand.

Two calculation methods:

| Method | Description |
|--------|-------------|
| Last | Simple average: total delivered amount divided by lookback days (default 78 days). Growth rate is always 1.0 |
| Period | Compares a date range this year to the same range last year. Auto-calculates year-over-year growth rate. Period is defined by before/after days around an anchor date |

Per-product group overrides: each product group can use a different method and growth rate multiplier. Products not in the override table use the global settings.

The daily rate formula: `consumption / days × auto growth rate × manual growth rate`. Estimate rows are inserted between existing product rows, splitting when material rows land inside their date range. This ensures correct intermediate balance calculations during the cascade loop.

## Cascade Loop

Need down flows from finished products to raw materials. Each step may create new need down that triggers the next level.

### Flow

```mermaid
flowchart TD
    L1["Calculate balance for all changed products"] --> L2{"Need down found?"}
    L2 -->|yes| L3["Load lower level materials from database"]
    L3 --> L4["Insert cascade rows into sub-material arrays"]
    L4 --> L5["Recalculate affected sub-materials"]
    L5 --> L1
    L2 -->|no| L6{"Last pass features enabled?"}
    L6 -->|yes| L7["One final calculation pass"]
    L7 --> L8["Apply minimum stock and negative end balance"]
    L6 -->|no| DONE["Done"]
    L8 --> DONE
```

### Example

Product A has BOM requiring Material B. Product A gets need down.

1. Balance calculation for Product A produces need down
2. Database loads Material B requirement with BOM quantity
3. Cascade row inserted into Material B: **db** shows `"work"`, **Origin** shows `"work -"`, **From** links back to Product A's row
4. Balance calculation for Material B runs, may produce its own need down
5. If Material B has sub-material C, the process repeats for C
6. Loop ends when no new need down is produced or Max down limit reached

### Cascade row in the grid

Cascade rows appear like normal rows in the sub-material's product view. Key columns:

- **Origin**: shows `"work -"` for material cascade, indicating this need comes from a parent production order
- **From**: links back to the parent product row (clickable to navigate)
- **Amount**: negative value = material quantity x parent's need down amount

### After cascade loop

One final pass runs when any of these are on: minimum stock, action limit, estimate order, negative end balance. This pass applies features that should only run after all levels are calculated.

## Chart Types

The chart type selector offers these views:

| Chart | Description |
|-------|-------------|
| Stock balance | Line chart showing balance development over time per product |
| Product tree | Tree chart showing product hierarchy and lower level connections |
| Work phases | Gantt chart showing production order work phases and timing |

## Scope Column

The Scope column shows hierarchical relationships for the selected row:

| Symbol | Meaning | Example |
|--------|---------|---------|
| ↓3 | 3 lower level (child) products, all visible | Click Product A, see 3 materials below |
| ↓3/1 | 3 child products total, 1 visible in current filter | 2 materials hidden by filter |
| ↑2 | 2 upper level (parent) products, all visible | Material used in 2 finished products |
| ↑2/1 | 2 parent products total, 1 visible | 1 parent hidden by filter |
| ↓3 ↑2 | 3 children and 2 parents | Both relationships visible |

## Info Column (Info ⇣)

The Info column shows why and how much need down was calculated:

| Info | Meaning | Example |
|------|---------|---------|
| `negative` | Header row has negative start balance | Product stock balance = -10 |
| `neg. balance` | Need down from negative end balance | Last row balance = -8, need down = 8 |
| `min. stock` | Need down from minimum stock requirement | Balance = 2, minimum stock = 10, need down = 8 |
| `neg. balance, min. stock` | Both negative end balance and minimum stock apply | Balance = -8, minimum stock = 5, need down = 8 |
| `Σ5` | Need down reduced because 5 units were already counted | Sales A already caused need down for 5 units |
| `⇣2/3` | 2 of 3 materials in work structure contribute to need down | Product has 3 materials, only 2 have need down |
| `⇣` | Need down set but no material count info available | Product has no work structure data |

## Grid Colors

### Cell Colors

| Color | Column | Condition |
|-------|--------|-----------|
| Semi-bold text | All columns | Product header row |
| Semi-bold + underlined | Balance | Last row of product |
| Grey | Date | Date is before today (not header) |
| Brown | Date | Date is before today (header row) |
| Blue | Date | Header row with valid date |
| Dark grey | Name, Amount (estimate) | Estimate type rows |
| Dark red | Balance (non-last row) | Balance is negative |
| Red | Balance (last row) | End balance is negative |
| Red | Row number | Error that is not a note |
| Green + bold | Work / prod. lot | Existing production order (not virtual) |
| Dark red + bold | Scope | Row highlighted but scope target not visible |
| Dimmed | Scope | Row not highlighted, scope not visible |
| White | Order row, Amount, Need down | Zero value |
| Black | Amount | Positive amount (incoming) |
| Blue | Need down | Need down > 0 |

### Row Colors

| Color | Condition |
|-------|-----------|
| Brown | Virtual production order (not estimate type) |
| Light brown | Estimate order type |
| Teal | Estimate row |
| Golden yellow background | Upper level highlight (parent product selected) |
| Light green background | Lower level highlight (child product selected) |
| Red border + red text | Missing order data |
| Darksalmon | Product excluded from calculation |

### Error Colors

| Color | Condition |
|-------|-----------|
| Green text | Informational note |
| Red text | Error |
| Red background + border | Cell validation failed |

## Incremental Update

When the Incremental checkbox is on, the server tracks data changes using modify timestamps:

- First load returns all data
- Subsequent updates return only changed rows
- Falls back to full reload if incremental update fails
- Each table must have a modify tracking field

## Add Purchase Order (Balance View)

In Balance view, you can create purchase orders:

1. Select one or more rows
2. Set order amount (must be > 0) for each row
3. Set supplier for each row
4. Click "Add order"

Validation requires both fields on all selected rows before saving.
