# Business rules — stock_control (RGBC Stock Control)

Evidence date: 2026-10-02.

Source: `git@bitbucket.org:overdrive/stock_control.git`, `master` at `02bce8c` (2026-03-16).

There is no business-rules document in the source tree. Every rule below is **Implemented** unless the line says **Inferred** or **Requires Confirmation**. Rules are what the code does, not a signed policy.

## Identity and access

1. A person can open the planning grid only when `Request.LogonUserIdentity.Name` matches `mstUser.strDomainLogin` and the joined group row has `SystemStockControl = 'StockControl'`. The domain argument passed to SQL is the literal `StockControl`, which is concatenated into the column name `SystemStockControl`. `index.aspx.vb`, `sql_get_Users`.
2. The login text boxes are not the credential. The Windows identity is. **Implemented.**
3. Each successful login inserts `mstUsageLog` (`mstUserID`, `dtStamp` `yyyyMMddHHmmss`, `strSystem = 'StockControl'`).
4. The group name is matched with `Contains`, not equality. See the PRD users table for admin, brand, shipping, and order.
5. Imports menu item (the second item, index 1) is enabled only when the group contains `admin` and `blnDebug` is false. `Admin.Master.vb`.
6. When `blnDebug` is true and the build is DEBUG, a missing user is replaced with a hard-coded admin (`UNAME = a`, `UID = 1`). The committed `Web.config` has `blnDebug` true. A Release build does not compile that block. **Implemented** for debug builds.
7. User and brand maintenance pages do not check the group. **Inferred** from the absence of a check. Whether that is allowed is **Requires Confirmation**.
8. User name and domain login are required. Domain login must be unique across other user ids.
9. Brand name is required and unique. Lead months must be an integer from 0 to 12. Variance must be a number greater than or equal to zero. A new brand is inserted with `blnActive = 1`.
10. Deleting a user is a direct `DELETE FROM mstUser`. No "inactive" flag was found on the user save path.

Signed-in identity is written to `appSettings`. **Inferred** rule, and a defect if more than one person uses the site at once: the last login wins for every request that reads `UNAME` or `UGROUP`. **Requires Confirmation** that production is single-user or that this is known.

## Who may edit which column

Grid columns that are text boxes are BMA (index 3) and orders (index 5). Receipts were editable in older code and are now read-only. Comment in `setSecurity`: "Receipts are not editable".

| Group contains | BMA | Orders | From which month |
|---|---|---|---|
| `admin` | editable | editable | Orders: current `yyyyMM` through the last column. BMA: current month through the last column. |
| `brand` | editable | read-only | Next calendar month through the last column, and only if the signed-in user id equals the product's brand manager user id |
| `shipping` | read-only | editable | Current month through the last column |
| `order` | read-only | editable | Current month through the last column |
| other | read-only | read-only | — |

Brand-manager edit used to start at current month + lead months + 1. Comment v1.2.9 (2010-06-15) says brand managers adjust from the current month + 1. The lead-time value is still loaded and is not used in that branch. **Implemented.**

`intLeadMonths` is still stored on the brand and shown nowhere else in the security method. What lead months are for, beyond that unused variable, is **Requires Confirmation**.

Save persists only when the truncated integer BMA or orders differ from the previously loaded grid. `sql_Update_PP`:

- brand → `dblBMA`, `strLastUser`, `dtStamp`
- shipping or order → `dblOrders`, `strLastUserSM`, `dtStampSM`
- admin → both pairs

Confirm (`btnEmail`):

- brand → replace `stkConfirm` for the product and today's `yyyyMM` with BMA from grid columns 4–15 (twelve values).
- shipping → replace `stkConfirmSM` with orders from those same columns.
- admin and orders do not insert a confirm row.

## Products, brands, and units

11. A stock-keeping code on `stkProductPeriod` is the left five characters of `mstProduct.strProdCode`. Import pads shorter codes with leading zeros (`ProdCode5Chars`).
12. Dropdowns and report populations require `mstProduct.blnActive = 1`, `mstBrand.blnActive = 1`, and `blnCompProduct = 0` or null.
13. 9-litre aggregation and quota forecast require `strProdCode` to contain `F`. **Inferred:** `F` means a finished-goods unit. **Requires Confirmation.**
14. 9-litre quantity is `intPack * intSize * sourceQuantity / 9000`. `sql_insert_9L`. When the sum of `dblOpeningImports` for the group is not zero, opening 9-litre uses opening imports. Otherwise it uses `dblOpening`. Comment v1.2.9 (2013-01-14).
15. The all-products grid (`strProdCode = 0`) is labelled in 9-litre cases. A single SKU is labelled in flat cases.
16. Comp import skip: if the first five characters of the file code start with `COMP`, the row is ignored. `IsCompProductCode`. This is separate from the `blnCompProduct` flag. A product can be excluded by either rule.
17. Varietal and category filters are `LIKE '%value%'` against `strBrandVarietal` and `strCategory2`. An empty filter matches all.

## Period window

18. The grid starts three months before the selected period. `getGridStartDate`: if the month is greater than 3, subtract 3 months; otherwise go to the previous year, month + 9. The overload that takes `intDiff` ignores `intDiff` and does the same calculation. **Implemented.**
19. The grid ends eleven months after the start month of the selected period, except January, which ends in December of the same year. `getGridEndDate`: month < 2 → same year, month + 11; else next year, month − 1. February therefore ends in January of the next year.
20. The stock control report copies that February rule explicitly (`month = 2` → `(year + 1) * 100 + 1`). January uses `period + 11`. Any other month uses `period + 99`.
21. The variance report and the confirm HTML report still use `period + 11` when the month is 1 or 2. For February that sum is not a valid month (`202602 + 11 = 202613`). **Implemented** inconsistency with rule 20.

Periods offered in the grid dropdown are `mstPeriod.strPeriod >= '200809'`.

## Opening, closing, and months of cover

Two calculators exist.

### On-screen `reTotal` (`ProductPeriod.aspx.vb`)

For a column before the current `yyyyMM`:

- balance = opening − (actual + direct sales) + receipts − exports + damages + repack

The exact sign of receipts, exports, damages, and repack follows the block around line 1156. Direct sales are included by adding `closingDifference` to actual.

For the current month, if actual + direct sales is greater than zero and "Use Projected Figures" is unchecked, the past formula is used. Otherwise the forward formula is used:

- balance = opening − QF + orders − exports + damages − BMA + repack

Future months use the forward formula.

If `dblOpeningImports` on the loaded row is greater than zero, the opening label is replaced with that value and coloured dark violet.

### Persisted `updatePPBalance` (`Import.aspx.vb`)

Runs after import and after save. Rows are ordered by product then period, from the start period forward.

- If `dblOpeningImports > 0`, opening is set to that value.
- The first row of a product uses `dblOpening`.
- Each later row of the same product uses the closing just computed for the previous row. It does not read the previous period from the database.
- Past or current month: closing = opening − actual + receipts − exports + damages + repack.
- Current month with actual = 0, or any future month: closing = opening − QF + orders − exports + damages − BMA + repack.
- Direct sales are not in this formula.
- Months of cover walks forward over later periods of the same product, consuming `QF + BMA`, and stops at 15, at a zero consumption month (which forces 15), or at the end of the product. A negative remaining balance with cover still under 1 can produce a negative fraction from the next period's QF + BMA.
- Writes `dblOpening`, `dblClosing`, `dblMonths`, `strLastUserUpdate = 'SC Import'`, `dtStampUpdate`.

**Inferred:** the screen can show a closing that includes direct sales while the database closing written by import or save does not. Which figure the business treats as official is **Requires Confirmation**.

### Zero opening

Commit `02bce8c` reverted "allow zero opening stock" and "always use previous period's closing".

The running rule is: an imported opening replaces the chained opening only when `dblOpeningImports` is greater than zero. A zero in `dblOpeningImports` does not trigger that replacement. The first period of a product in the result set still keeps whatever `dblOpening` already is, including zero. Later periods take the previous computed closing, so an imported zero on those periods does not stick.

The comment at the top of `Import.aspx.vb` says the revert makes opening imports, including zeros, stick. That sentence does not match the `> 0` condition. **Requires Confirmation.**

`Scripts/Set_Zero_Opening_Feb2026.sql` forces `dblOpening` and `dblOpeningImports` to 0 for a fixed list of codes in period `202602`, and sets closing with the past-month formula. Its header says a later `updatePPBalance` under v1.3.2 would undo the zeros. After the revert, a later balance pass still overwrites non-first periods from the previous row's closing. **Requires Confirmation** before anyone runs that script on the reverted code.

`Scripts/Recalc_stkProductPeriod_OpeningClosing.sql` still implements the reverted v1.3.2 chain (previous period's stored closing, last three calendar years, `strLastUserUpdate = 'SC Import'`). It is not what `updatePPBalance` does. **Do not treat the script as the application rule.**

## Sales and forecast facts

22. Previous-year sales on the grid come from `dwfSales.lngUnitsTotal * mstProduct.dblFlat` for a SKU, or `dwfSales.dbl9lTotal` for the 9-litre view. Top 15 periods. `sql_get_PreviousPP`, `sql_get_PreviousPP_9L`.
23. Direct sales are the same sums filtered to `dwfSales.refDocumentNo LIKE 'Y%'`.
24. Cognos 14 import sets `dblActual` to 0 for the periods in the file, then adds `units * dblFlat` per five-character product code. Rows whose product has no brand are counted as failures.
25. Cognos lines with `doctype` null, empty, `-`, or `C` have units, 9-litre, nett, GSV, and nett-ex multiplied by −1 before the sum. **Inferred:** those document types are reversals or credits. **Requires Confirmation** of the code list.
26. Opening import nulls `dblOpeningImports` for the whole period, then writes column index 2 of the workbook into both `dblOpening` and `dblOpeningImports` for each non-comp product that already exists in `mstProduct`. Column index 0 is the product code. Column index 1 is displayed and is not named in code. Product code `05016` is multiplied by 9 before it is stored. The comment says this converts SoCo essential oils into the litres they can make. **Requires Confirmation** that this exception is still intended. A comment above the loop says inserts are commented out. The `sql_insert_PP` call in that loop is not commented out.
27. BM adjustment import adds into `dblBMA` (`sql_Update_PP_Import` adds the value to the column). It does not replace the column when the period row already exists. A new period row is inserted with that BMA. **Implemented.**
28. Quota forecast (`RepQF`): historical customer share of 9-litre sales, for the principal's last completed fiscal year, times the SKU's QF, written to `dwfQF.dblBudget9l` and `dblPercentage`. Customers on branches whose `strCode` contains `x` or `y` are excluded. Decimals constant is 8. The button always uses fiscal window start `200807` and end `200906`. **Implemented.** Whether that range is still the intended run is **Requires Confirmation**.

## Variance

29. Target months of cover is `mstBrand.dblVariance`. The variance report uses 2.5 when the value is null. The grid loads the brand value into `dblVariance` (the field defaults to 2.5 before the query).
30. A variance-report row is kept when any displayed month of cover is greater than target + 0.5 or less than target − 0.5. If none are, the row is deleted unless the user asks for all rows. **Implemented.**

## Data rules that are easy to miss

31. `sql_get_PP` turns null BMA, actual, orders, receipts, exports, damages, and repack into 0. Opening, closing, months, and opening imports are not wrapped in `ISNULL`. Commit note 2024-06-19: nulls broke decimal truncation in `Save`.
32. `CleanString` turns double quotes into single quotes and doubles single quotes. It is not used on every SQL string. Many statements concatenate product codes and periods directly. **Implemented.**
33. `stk9L` is deleted entirely (`DELETE FROM stk9L`) before a brand fiscal insert. **Inferred:** it is a scratch table, not a system of record.
34. New grid rows inserted because `intPP = 0` are stored with `strLastUser = 'new'`.
35. Page size for the user grid defaults from app setting `PAGESIZE` (20 in `Web.config`).

## Unknown

- The business meaning of QF, BMA, flat cases, and the `Y` document prefix, beyond the names in the grid. QF is labelled in code comments as forecast and as quota forecast. The grid row is "QF".
- Whether damages and repack are still supplied by a system outside this website after their Excel imports were commented out.
- Approval of the February 2026 zero-opening product list in `Set_Zero_Opening_Feb2026.sql`. The script names the codes. It does not say who authorised them.