# Business rules — snell-cronjob

Evidence date: 2026-10-03. Source: `master` at `f092e9b` (2026-06-26).

No business-rules document exists in the repository. Rules below are taken from VB conditions, SQL in the executable, `app.config`, and comments. Each rule is marked **Implemented** or **Inferred**. A comment that states intent is quoted as a comment, not as a signed-off policy.

Amounts are the numbers in source. `snell-freestock` calls 10000 "R10 000". This repository never says rand. The currency label is **Requires Confirmation** if it must be stated as policy.

## When the job runs

### BR-001 — A release build runs itself once

**Implemented.** Outside a DEBUG compile, if `My.Settings.Debug` is false, `Load` calls `Run` and closes the form. The git value of `Debug` is `False`.

A DEBUG compile never takes that branch. The form waits for the Run button.

### BR-002 — Only one instance

**Implemented.** `Application.myapp` sets `SingleInstance` true.

**Unknown:** what the VB runtime does with a second start while the first `Run` is still inside SQL or SMTP. The setting is the evidence. The scheduler overlap policy is not.

### BR-003 — The queue loop stops after 40 minutes

**Implemented.** `intOpperationTimeMax = 40 * 60` seconds. After each job the elapsed ticks are compared with that duration. Further rows in the current result set are not processed. Rows still `New` stay `New`.

The comparison uses a snapshot taken at the start of the loop only in the sense that `dtOpperationStart` is set once. It is wall time from the start of the queue section, not from process start. Preprocess (import, SAP, midday chase) is not inside the cap.

### BR-004 — A job left in Processing is returned to New

**Implemented.** After the loop: `UPDATE sysCronJob SET Status = 'New' WHERE Status LIKE ('%Processing%')`.

`Processed`, `Warning`, `Error`, and `Processed_MailError` do not match that pattern.

## AdvM import

### BR-005 — The only import file is the named CSV

**Implemented.** Filename `Free Stock Requisition.csv`. Release folder `C:\am_ftpdownload\downloads\`. The file must already be there. This program does not fetch FTP itself. The folder name is the evidence for an earlier download step.

If the source file is missing, nothing is copied and no warning is written. An older file already in `Imports\` is still eligible for import.

### BR-006 — A valid file replaces staging

**Implemented.** `TRUNCATE TABLE stgFreeStockRequisition` runs only after the extension check sets `isValid`. Then every accepted data row is inserted. The local Imports file is deleted even if some rows failed, as long as `isValid` stayed true. `isValid` is cleared for an unsupported extension and for a file-read exception. A malformed line does not clear it.

### BR-007 — Row gates

**Implemented** in `sqlInsertStaging`. A failing check throws `InvalidCastException`, which the importer logs as a warning and continues.

Required and non-blank: UniqueID, rep code, request type, region, bill-to, ship-to, SKU, case quantity, bottle quantity.

Date: length exactly 10. No date-parse check. A 10-character string that is not a date still passes this gate. The later insert uses `CONVERT(DATETIME, date, 120)`.

Expense type: the emptiness check is commented out. Comment: "Manadatory check removed from fsr.strExpenseType" (2015-01-13).

Quantities: both present, and the pair `0` and `0` is rejected with `Quantity is 0`.

Request detail, contact, phone, and delivery are stored after `CleanString` on the text fields. Delivery is later inserted with `CInt`.

### BR-008 — New AdvM requisitions start as Pending and are queued

**Implemented.** `sqlInsertFSR` writes `strStatus` `'Pending'`. The pink slip is `'ES' + RIGHT('0000000' + CAST(MAX(RequisitionID) AS varchar(10)), 7)` read after the insert. A queue row is inserted only inside that same success path:

`Type = FSR_NewRequisition`, `Status = New`, `param = RequisitionID={id}`, `dtCronJob` = today's `yyyyMMdd` plus `HHmm` from `GETDATE()`.

Rows whose promote-query pink slip is already non-blank are skipped.

**Inferred:** `MAX(RequisitionID)` is not the identity of the row just inserted if another session inserts in between.

**Discrepancy with `snell-freestock`:** that application refuses Submit and Approve when there are zero items, so a blank pink slip is not emailed. This import queues `FSR_NewRequisition` even when the item insert matches no `dwdProduct` row (the insert is a join; zero rows still count as success). **Requires Confirmation** which behaviour is intended for an unknown SKU.

### BR-009 — Unknown request types are added

**Implemented** as a call to `sysQuery` ID 41 before the promote. The comment above the call is an `INSERT` into `fsrRequestType` of distinct staging `RequestType` values that are not already present, with `blnActive` 1 and `strLastUser` `'SB CRON: AM Import'`.

The executable does not contain that SQL. If the stored query differs from the comment, the comment is wrong. **Requires Confirmation.**

### BR-010 — Rows that never became requisitions are warnings

**Implemented.** After promote, `sysQuery` ID 42 is executed and each row is logged at level `warning` as `Record not imported` with UniqueID, RepCode, and Date. The comment shows a left join from staging to `fsrRequisition` on `UniqueID` where the requisition side is null.

## Who is emailed

`SalesManagerMaxValue` in git is the string `10000`. Comparisons use `<=`.

`vieRequsitionManager` supplies `TotalRequisitionValue`, `ManagerEmail`, `ManagerName`, `AltManagerEmail`, `AltManagerName`, `ExecutiveManagerEmail`, `ExeManagerName`, and the three ids. The view definition is not in this repository.

### BR-011 — A new requisition goes to the sales manager at or under 10000, otherwise the executive

**Implemented** for `FSR_NewRequisition`. At or under the setting, `ManagerEmail`. Above it, `ExecutiveManagerEmail`.

Comments in the same method disagree with each other: one says "<= 5 000, email Manager", another says "<= 20 000". The running condition is the setting, which is 10000 in git.

**Discrepancy with `snell-freestock` BR-010 and BR-011:** the web application treats a total **lower than** 10000 as the manager band and a total **greater than or equal to** 10000 as the executive band. This worker uses **less than or equal**. At a total of exactly 10000 the web app expects the executive to approve, and this worker emails the sales manager. **Requires Confirmation.**

### BR-012 — The midday chase uses the alternate manager on that same threshold

**Implemented.** Same `<=` test. The recipient is `AltManagerEmail` at or under the threshold, and `ExecutiveManagerEmail` above it. It does not mail `ManagerEmail`.

Eligible rows: `StrStatus = 'Pending'`, `DATEDIFF(day, dtDate, getDate()) > 1`, `dtEmailSentToAltManager IS NULL`, and the clock is after noon and before 12:19.

The 2014 comment says three days. The 2016 comment says 48 hours. `DATEDIFF(day, ...)` counts calendar-day boundaries, not elapsed hours. A requisition dated yesterday can qualify after midnight even if 48 hours have not passed. **Inferred** from SQL Server `DATEDIFF` day semantics. **Requires Confirmation** that a one-calendar-day chase is the rule operations still want.

On send, `dtEmailSentToAltManager` is set, so the chase does not repeat.

### BR-013 — An approved requisition is mailed to the orders list, not to the manager

**Implemented.** To = `SnellEmailAddress`. The threshold still runs, and the chosen manager id is written onto the requisition, but that manager is not the To address.

Git To list: `orders@esnell.co.za`, `Nondumisom@esnell.co.za`, `lloyd@overdrive.co.za`.

CC: the requester, then `SnellEmailAddress2` (`espda@esnell.co.za` in `app.config`).

BCC: literal `maanie@overdrive.co.za` in source, not the `BCCAddresses` setting.

`[ToName]` in the approved template is replaced with `Snell Clerk`, not with the manager name. The manager name is still passed through the threshold block and then unused in that replace.

### BR-014 — Release mail comes from noreply@esnell.co.za

**Implemented.** From display name `FreeStock Requisition` on the FSR mails and `Snell FSR SAP` on the SAP failure mail. Address `noreply@esnell.co.za`. Commit `b626d84` replaced an earlier freestock address. The old address remains in comments.

Host `mail.esnell.co.za`. SSL is off. If the configured password contains the letters `ASP`, `unsalt` rewrites them to `php` before login. Git leaves the username and password empty.

### BR-015 — A missing manager address does not send

**Implemented.** Empty manager email sets the job to `Warning` and does not send. A missing `vieRequsitionManager` row does the same with `No manager found`.

The midday chase has no queue row. It skips the send and does not stamp `dtEmailSentToAltManager` when the chosen address is empty, so the same requisition is eligible again on a later day inside the window.

### BR-016 — The stale-file notice is coded and the window does not open

**Implemented** pieces:

- File last-write age is compared with `StaleFileNotifyPeriodHours` (git value 1) using `TotalHours`.
- Subject `ATTENTION: Free Stock Requisition File`.
- Body states the file has not been updated in that many hours.
- To and CC are the stale-file settings (aimmieg@esnell.co.za, justink@esnell.co.za).

The send sits inside `If Today >= #07:45:00 AM# And Today <= #08:00:00 AM#`. `Today` is midnight on the current date. A VB time-only literal is 1 January 0001. The second comparison is false on any modern date.

`StaleFileNotifyStartTime` and `StaleFileNotifyEndTime` are stored and never read.

**Inferred:** the notice does not leave the building. **Requires Confirmation** if a production binary was built from different source.

The CC loop splits on comma and then adds `MailAddress(strCC)` using the whole string. With one address, as in git, that still produces one CC. **Inferred:** a comma-separated CC list would not add each address.

## Approval and status

### BR-017 — This program does not approve, decline, or complete a requisition

**Implemented.** The only status it writes on `fsrRequisition` is `'Pending'` during AdvM insert. It reads `'Approved'` for SAP and `'Pending'` for the chase. It never writes `Approved`, `Declined`, `Cancelled`, `Draft`, or `Completed`.

`snell-freestock` writes Draft, Pending, Approved, Declined, and Cancelled, and also does not write `Completed`. Who sets `Completed` remains **Unknown** after reading both repositories.

### BR-018 — Manager ids on the requisition are overwritten from the view

**Implemented** on the chase and on both email jobs, before the send: `refManagerID`, `refAlternateManagerID`, `refExecutiveManagerID` are replaced with the view's values.

SAP export does not do this.

## SAP

### BR-019 — Export is off unless explicitly enabled and credentialled

**Implemented.** `FsrSapExportEnabled` must equal `True`, ignoring case. Endpoint, username, and password must all be non-empty. Designer defaults: enabled `False`, credentials empty, SOAP action `ZRfcEdiIdocInputOrders`, max retries `5`, order type `OR`, reason `279`, Z1 email `rgbcpda@esnell.co.za`, failure mailbox `maanie@overdrive.co.za`.

Those keys are not in `app.config`. A config file on the server can override them. **Unknown / Requires Confirmation** whether it does.

### BR-020 — Only approved, unsent rows under the retry cap are candidates

**Implemented.** `strStatus = 'Approved'` and `ISNULL(blnSentToSap, 0) = 0` and `ISNULL(intSapRetryCount, 0) < maxRetry`. Order is `RequisitionID` ascending.

### BR-021 — A requisition with no shippable quantity is not posted

**Implemented.** No joined lines, or every line has case quantity and bottle quantity both zero or null: `strSapLastError` is set, retry count is not increased, a failure mail is sent, the row stays unsent.

The same mail-every-run behaviour applies when bill-to or ship-to has no `dwdCustomer.strSAPCode`.

### BR-022 — Transport failure counts; data failure does not

**Implemented.** Exceptions and an HTTP/SOAP failure increment `intSapRetryCount`. When the stored count reaches the cap, the failure mail is sent and later runs exclude the row.

Data failures (BR-021) notify immediately and remain under the cap, so the next run selects them again and notifies again.

### BR-023 — Success is "HTTP response without a fault marker"

**Implemented.** `blnSentToSap = 1` and `dtSentToSap = GETDATE()` when `ParseSoapResponse` does not find `<faultcode>`, `<soap:fault>`, or `:fault>` in the body. An empty body is failure. No check for an SAP document number.

**Inferred:** a 200 response that is not a fault can be recorded as sent even when SAP did not create an order. **Requires Confirmation** against a real response sample.

### BR-024 — Collection versus delivery

**Implemented** in `IdocGenerator`. `blnDelivery` false is treated as collection: `Bstzd` `COLL`, `Lifsk` `ZG`. The comment equates this with Repsly "Collection". `blnDelivery` true leaves both elements empty.

Partner numbers: bill-to SAP code as AG, ship-to SAP code as WE, each padded to 10 digits. Z1 partner number `991`. Z2 partner number `992`. Z2 name is the rep's `mstUser.strEmail`, or `orders@esnell.co.za` when that email is empty.

### BR-025 — Case and bottle are separate SAP lines

**Implemented.** Case quantity above zero emits unit `CS`. Bottle quantity above zero emits unit `BT`. A trailing `S` or `F` on the product code is removed for `Idtnr`. Description is at most 30 characters, then space-padded to 30.

### BR-026 — The failure mail claims the PDF was already sent

**Implemented** as body text: "The PDF email to the orders team was still sent as usual."

The code does not check that. `PreProcess` runs SAP export before `Run` reads the queue. A failure on a requisition whose approved job is still `New` is mailed before that PDF is sent. **Requires Confirmation** before treating the sentence as operational fact.

### BR-027 — SAP columns

**Implemented** in the script, not automatically applied:

| Column | Definition in the script |
|---|---|
| `blnSentToSap` | `bit NOT NULL` default 0 |
| `dtSentToSap` | `datetime NULL` |
| `intSapRetryCount` | `int NOT NULL` default 0 |
| `strSapLastError` | `nvarchar(500) NULL` |

The script is written to skip columns that already exist. The header comment says to run it on `snell_2015` before deploying the export class.

## PDF and templates

### BR-028 — PDF file name follows the job type string

**Implemented.** `createPDF` uses `SubmittedRequisition_{pink slip}.pdf` only when the status argument equals `FSR_NewRequisition`. Every other value, including `FSR_RequisitionApproved` and the midday call (which passes `FSR_NewRequisition`), uses `ApprovedRequisition_` or `SubmittedRequisition_` on that basis.

The midday chase passes `FSR_NewRequisition`, so the chase attaches a file named as a submission.

An existing file of that name is deleted first. The delete error is ignored.

### BR-029 — Report data is the requisition header and its items

**Implemented** in the typed datasets.

Header query (`dsRequisition.xsd`): pink slip, user name, request type, date as the first 10 characters of style 120, region, expense, contacts, delivery, status, invoice number, last edit, authorised by, request detail, and bill-to / ship-to as `customer number - customer name` from `dwdCustomer`. The report's `RequisitionID` column is `RIGHT('0000' + id, 5)`, which is 5 digits, not the 7-digit pink slip.

Item query (`dsItem.xsd`): `strProdCode`, `dwdProduct.strLongProdDesc`, case quantity, bottle quantity, for `refRequisitionID`.

The XSD parameter annotation still says `DataSourceName` `Superbowl.dbo.fsrRequisition`. The connection used at runtime is `SQLConnectionRPT`, which in git points at `snell_2015`.

### BR-030 — Email bodies are database templates

**Implemented.** New requisition and the chase load `sysQuery.memQuery` where `ID = 38`. Approved loads `ID = 39`. Missing `memQuery` sends an empty body. The token list is in comments in `frmCronjob.vb` and is what the `Replace` chain implements. The template HTML is **Unknown**.

## Logging and safety

### BR-031 — Operational logs go to stgSuperbowlLog

**Implemented.** Columns written: `strStamp` (ticks), `dtLog` (universal sortable string), `strType`, `strProcess`, `strAction`, `txtLog`, `blnEmail` (callers leave the default 0). `txtLog` passes through `db.CleanString`.

Nothing in this program reads the log or sends it onward. `blnEmail` is stored and unused here.

### BR-032 — SQL text is concatenated

**Implemented.** Statements are built with quotes and `CleanString` on some text fields. This is how the program talks to SQL Server. It is not a separate validation layer.

`doQuery` treats a string that starts with `SELECT` (after trim and upper-case) as a query and anything else as a non-query. A `SELECT` that fails still calls `Fill` again inside the catch.

## Rules that are not in this repository

No rule here covers tax, credit limits, stock on hand, who may approve (that is `snell-freestock`), an expiry other than the midday chase, or a reason code list beyond the SAP constants `OR` and `279`.

Whether those SAP constants are the business's order type and order reason is **Requires Confirmation**. They are defaults in `IdocGenerator` and in the designer attributes, and the PowerShell sample uses the same values.