# PRD — superbowl_cronjob

Evidence date: 2026-10-02. Revision: `master` `986893e` (2026-09-11).

No PRD existed in the source repository. Requirements below are taken from `frmCronjob.vb`, the process classes, `app.config`, SQL scripts, and the Repsly plan file. Provenance is marked on each requirement.

Users of this executable are not people sitting in a product UI, except an operator who clicks Run when `Debug` is true. The people named in emails (managers, accounts payable, Edward Snell, finance) are recipients. The actors who create queue rows live in the Superbowl web application, which is outside this repository.

## Product purpose

**Implemented.** Give RGBC Superbowl a Windows process that:

1. Imports Repsly purchase orders and promotes free-stock documents into requisitions.
2. Posts orders and, when enabled, approved free-stock requisitions to Edward Snell as SAP IDOCs.
3. Sends the emails, PDFs, and Sage CSV files that the `sysCronJob` queue asks for.
4. Regenerates a brand quantity forecast and rewrites historical rows when a product code is archived.

## Users and interactions

| Actor | Interaction | Provenance |
|---|---|---|
| Operator or scheduler | Starts `Superbowl Cronjob.exe`. If `Debug` is false, `frmCronjob_Load` calls `Run` and closes the form. If `Debug` is true, the form stays open until Run is clicked | Implemented. Scheduler itself is Unknown |
| Superbowl web users | Insert `sysCronJob` rows. Not implemented here | Inferred from job parameters such as `RequisitionID=` and `VendorID=` |
| Sales manager / alternate / executive | Receive free-stock approval mail and the noon chase mail | Implemented |
| Edward Snell mailboxes | Receive approved free-stock notification (`SnellEmailAddress`) and are the SOAP peer | Implemented |
| Accounts payable, finance accountant, finance executive | Receive purchase-order and vendor-workflow mail | Implemented, addresses in user/application settings |
| Repsly | Source of purchase-order JSON | Implemented |

## Major workflow

```text
Start
  -> PreProcess (always)
       Repsly import
       -> promote DocumentTypeName = FSR into fsrRequisition (status Pending)
       -> post other unsent orders to ESnell
       -> post Approved FSR to ESnell if the gate is on
       -> noon reminders for aged Pending FSR, POR, and vendors
  -> select sysCronJob where Status = 'New'
       order by date prefix of dtCronJob descending, then Type ascending
  -> for each row, set Status = Processing, do the case, set Processed or Error
  -> stop the loop if elapsed time reaches 40 minutes
  -> set any status like '%Processing%' back to New
```

`intOpperationTimeMax` is `40 * 60` seconds. Provenance: **Implemented** in `frmCronjob`.

A job type with no `Case` is left at `New` and will be selected again on the next run. There is no `Case Else`. Provenance: **Implemented**.

## Functional requirements

### FR-001 — Run once per launch

**Requirement.** When `My.Settings.Debug` is false, load runs the full cycle and closes the window. When it is true, load does not run; the Run button calls `Run`.

**Trigger.** Process start, or the Run button.

**Output.** Queue and integration side effects described below. The form closes only on the non-debug load path (and if the progress bar exceeds its range while `Debug` is false).

**Evidence.** `frmCronjob_Load`, `btnRun_Click`, `bgwTimer_ProgressChanged`.

**Implementation.** Implemented.

**Constraint.** Committed `app.config` sets `Debug` to `True`. Deployed value: **Unknown / Requires Confirmation**.

### FR-002 — Repsly purchase-order import

**Requirement.** Each run pulls purchase orders from Repsly and upserts them by `PurchaseOrderID`.

**Who.** The process itself. No user input.

**Inputs.** Settings `RepslyBaseUrl`, `RepslyUsername`, `RepslyPassword`. Last successful `repslyImportRun.LastIDProcessed`, or 0.

**Behaviour.**

- If any of the three settings is empty, log an error and return.
- Insert a `repslyImportRun` row with status `Running`.
- GET `{base}/export/purchaseorders/{lastId}` with HTTP Basic authentication until `TotalCount` is 0.
- Insert a new `repslyOrder` or update the existing one and replace its `repslyOrderItem` rows.
- On success, store `LastIDProcessed` and status `Success`. On failure of the run, status `Error` and do not advance `LastID` beyond the start value used in `CompleteRunRecord` for the catch path.

**Outputs.** Rows in `repslyOrder`, `repslyOrderItem`, `repslyImportRun`. Log rows in `stgSuperbowlLog`.

**Evidence.** `RepslyOrder_AM_Process.vb`, `RepslyApiClient.vb`, `Scripts/CreateRepslyTables.sql`.

**Implementation.** Implemented. The plan file still marks live testing as pending.

**Confidence.** High for the code path. Whether the SQL script has been applied is **Unknown / Requires Confirmation**.

### FR-003 — Promote Repsly FSR documents

**Requirement.** After the import, create a free-stock requisition for each `repslyOrder` whose trimmed `DocumentTypeName` is `FSR` and whose `refFsrRequisitionID` is null or 0.

**Behaviour (skip, do not insert, when any check fails).**

- An `fsrRequisition` already exists with `UniqueID` equal to the Repsly `PurchaseOrderID`: link it and skip.
- Request type cannot be resolved from custom attribute `Request Type`, then setting `RepslyFsrRequestTypeName`, then a `fsrRequestType` row whose name contains `Free Stock`.
- Bill-to cannot be resolved to exactly one non-empty regional "Bill-to Account No" (Eastern Cape, Free State, Gauteng, KZN, Western Cape), or the value has no leading account number.
- `ClientCode` (ship-to) is missing.
- `RepresentativeCode` does not match `mstUser`.
- No line's `ProductCode` matches `dwdProduct.strProdCode`.

**On success.** Insert `fsrRequisition` with `strStatus` `Pending`. Set pink-slip number to `RG` plus the maximum `RequisitionID` padded to 7 digits. Insert `fsrItem` priced from `dwdProduct.dblPrice`. Insert `sysCronJob` type `FSR_NewRequisition` with `param` `RequisitionID={id}` and status `New`. Set `repslyOrder.refFsrRequisitionID`.

**Delivery flag.** Custom attribute `Delivery Method` equal to `Delivery` (ordinal ignore case) sets `blnDelivery` to 1. Any other value, including blank, sets 0.

**Evidence.** `RepslyFSR_AM_Process.vb`.

**Implementation.** Implemented.

**Confidence.** High.

### FR-004 — Post non-FSR Repsly orders to Edward Snell

**Requirement.** Each run posts `repslyOrder` rows with `blnSentToESnell = 0` and `DocumentTypeName` not equal to `FSR`.

**Inputs.** `ESnellSoapEndpoint`, `ESnellUsername`, `ESnellPassword`, `ESnellSoapAction`. Line items from `repslyOrderItem`. Rep email from `mstRep` where `RepID` equals `RepresentativeCode`. If missing, Z2 `Name1` is `orders@esnell.co.za`.

**Behaviour.** Build a SOAP 1.2 envelope (`IdocGenerator.BuildSoapXml`) and POST it with Basic authentication, content type `application/soap+xml`, timeout 60 seconds. A SOAP fault marks failure. HTTP success without a fault sets `blnSentToESnell = 1`. Orders with no line items are skipped and left unsent. A failed post is logged and the flag stays 0, so the next run tries again. There is no retry counter on this path.

**IDOC (inferred from `BuildSoapXml`).** `Bsart` `OR`, `Augru` `279`. Collection (custom attribute delivery type `Collection`) sets `Bstzd` `COLL` and `Lifsk` `ZG`. Any other delivery type leaves both blank. Material number drops a trailing `S` or `F`.

**Evidence.** `RepslyOrder_ESnell_Process.vb`, `IdocGenerator.vb`.

**Implementation.** Implemented. Committed endpoint host is `ediqa.esnell.co.za` (QA in the hostname). Whether production uses another endpoint is **Unknown / Requires Confirmation**.

### FR-005 — Post approved free-stock requisitions to Edward Snell

**Requirement.** When `FsrSapExportEnabled` is true, post `fsrRequisition` rows with `strStatus` `Approved`, `blnSentToSap` null or 0, and `intSapRetryCount` below `SapMaxRetryCount`.

**Gate.** Committed value is `False`. The process logs and returns without calling SOAP. `Scripts/AlterFsrRequisition_SapColumns.sql` says to keep the gate false until order reason 285 exists in SAP QA.

**Behaviour when enabled.**

- Skip (and record an error; retry count is not incremented) when there are no lines, every quantity is 0, or bill-to or ship-to is missing.
- If ship-to starts with `A`, use the bill-to account for both AG and WE partners. Comment in code: collection account not in SAP.
- `blnDelivery` false (collection) sets `Bstzd` `COLL` and `Lifsk` `ZG`. `blnDelivery` true (delivery) sets `Lifsk` `Z5` and leaves `Bstzd` blank.
- `Augru` comes from `FsrSapAugru` (committed `285`). `Bsart` from `FsrSapBsart` (committed `OR`).
- Unit of measure: product code ending in `S` is `BT`, otherwise `CS`. SAP material number strips that suffix.
- Notes (`txtRequestDetail`) are truncated to 35 characters on `E1edk02` `Belnr`.
- On accept, set `blnSentToSap = 1` and `dtSentToSap`. Store the last SOAP request and response on the requisition.
- On a counted failure, increment `intSapRetryCount`. When the count reaches `SapMaxRetryCount` (committed `5`), or on a non-retryable data error, email `SapFailureNotifyEmail`.

**Evidence.** `FsrOrder_ESnell_Process.vb`, `IdocGenerator.BuildFsrSoapXml`, `app.config`, `Scripts/AlterFsrRequisition_SapColumns.sql`.

**Implementation.** Implemented and gated off in the committed config.

**Confidence.** High for the code. Production gate and whether the ALTER script has run: **Unknown / Requires Confirmation**.

### FR-006 — Drain `sysCronJob`

**Requirement.** After pre-process, process every `sysCronJob` row with `Status = 'New'`.

**Inputs.** Columns used in code: `CronJobID`, `Type`, `param` (pipe-separated `key=value`), `Status`, `Result`, `dtCronJob`.

**Behaviour.** Set `Processing` before work. On success set `Processed` and a result string. Mail failures often set `Processed_MailError` or `Error`. At the end of the run, any status containing `Processing` is set back to `New`.

**Job types implemented** (exact `Case` strings):

| Type | What the case does |
|---|---|
| `BrandQF` | `QFGen.GenerateBrand` for `FinYear` and `BrandID` |
| `ArchiveProduct` | Rewrite old product code to new code, with `F` and `S` suffixes, across sales, QF, and further tables (result text says n/13) |
| `FSR_NewRequisition` | Email plus PDF to the routed manager. Template `sysQuery` ID 38 |
| `FSR_RequisitionApproved` | Email plus PDF to Edward Snell addresses. Template ID 39 |
| `POR_1NewRequisition` | New PO email, PDF, quote attachments. Template ID 43 |
| `POR_2ApprovedRequisition` | Approved PO email and CSV `approved_po_{PO number}.csv`. Template ID 48. Export SQL from `sysQuery` 46 and 47 |
| `POR_3Receipted` | Receipt email, invoice attachments, CSV `receipts_po_{receipt number}.csv`. Template ID 49. Export SQL 54 and 55. Updates PO status from quantities and sets the receipt to `Processed` |
| `POR_2DeclinedRequisition` | Decline email. Template ID 56 |
| `POR_CompletedRequisition` | Completed email. Template ID 61 |
| `POR_2CancelledRequisition` | Cancellation email. Template ID 57 |
| `POR_NewVendor` | New vendor email. Template ID 63 |
| `POR_VendorDeclined` | Template ID 64 |
| `POR_ApprovedVendor` | Finance accountant. Template ID 65 |
| `POR_VendorCompleted` | Finance executive. Template ID 66 |
| `POR_VendorInCompleted` | Template ID 67 |
| `POR_FinanceApproved` | Template ID 68 |
| `POR_VendorDone` | PDF from `vendorDone.rdlc`. Template ID 69 |
| `POR_Activate` | Mail to `accountspayable@rgbc.co.za`. Template ID 70 |

Header comments still name older strings such as `POR_NewRequisition` and `POR_RequisitionApproved`. The `Select Case` uses the numbered names in the table. Provenance: **Discrepancy** between comments and the executable cases. The cases are what run.

**Evidence.** `frmCronjob.Run`.

**Implementation.** Implemented.

### FR-007 — Noon chase for records left Pending

**Requirement.** Only when the local time is after 12:00:00 and before 12:19:00:

- Free-stock requisitions with `StrStatus` `Pending`, age greater than 3 days, and `dtEmailSentToAltManager` null: email the alternate manager if total value is less than or equal to `SalesManagerMaxValue`, otherwise the executive manager. Then set `dtEmailSentToAltManager`.
- Purchase-order requisitions with `strStatus` `Pending`, age greater than 1 day, and `dtEmailSentToAltManager` null: email the level-1/2/3 approver and alternate derived from `viePOR_UGAccess`. Then set `dtEmailSentToAltManager`.
- Vendors with `strStatus` `Pending` and age greater than 3 days: email the looked-up manager and set `porVendor.strMailStatus` to `Mail re-Sent`.

**Evidence.** `PreProcess` and `ProcessPendingVendors`.

**Implementation.** Implemented, with the vendor SQL defect in business rule BR-012.

**Note.** Commented blocks for stagnant Approved and Completed vendors (templates 65 and 66) are not executed.

### FR-008 — PDF attachments

**Requirement.** Mail jobs call `createPDF` and attach `Reports\PDF\` files. POR quote and invoice files are attached from `strPORDocumentDir` only when `File.Exists` is true. Missing files are skipped, not fatal by themselves.

**Evidence.** `ProjectFunctions` PDF render, comments `v2.0.1` / `v1.0.5` in `frmCronjob.vb`.

**Implementation.** Implemented. Report definitions other than `vendorDone.rdlc` are **Unknown** inside this repo (the PDF method uses a report object supplied by the caller path; dataset XSDs are committed).

### FR-009 — Logging

**Requirement.** `WriteLog` inserts into `stgSuperbowlLog` (override table name allowed). A comment on the free-stock SAP saver says that table is cleared daily in production.

**Evidence.** `ProjectFunctions.WriteLog`, comment in `FsrOrder_ESnell_Process.SaveSapExchange`.

**Implementation.** Implemented. The daily purge job is not in this repository. Provenance of the purge: **Documented in a code comment**, not implemented here.

## Integrations

| System | Direction | Trigger | Auth as implemented | Failure |
|---|---|---|---|---|
| Repsly v3 | Inbound JSON | Every `PreProcess` | HTTP Basic, settings | Run row `Error`; LastID not advanced on the catch path |
| Edward Snell SOAP | Outbound IDOC | Unsent non-FSR orders; approved FSR when gate on | HTTP Basic | Order flag stays unset; FSR increments retry and can email |
| SQL Server `Superbowl` | Read/write | Every run | Integrated security on the release connection string; SQL login on the debug string | Query errors are often swallowed by `db.doQuery` (returns `Nothing` or an empty set) |
| SMTP | Outbound mail | Queue jobs and noon chase | Release: host from settings, no credentials in code. Debug: Office 365 with a hardcoded mailbox | `Processed_MailError` or `Error`, plus `stgSuperbowlLog` |
| UNC shares | Files in/out | CSV export jobs; POR attachments; legacy import classes | Windows identity of the process | Missing attachment is skipped. Export failure is logged by `Export_Process` |

## Important constraints

- Single-threaded UI process. One `Run` holds the form thread.
- 40 minute cap per run.
- Noon reminders only fire if a launch happens inside 12:00–12:19 local time. A scheduler that never runs then will never send them. The schedule is **Unknown / Requires Confirmation**.
- SQL is concatenated strings. `db.CleanString` doubles single quotes. This is not a parameterised API.
- `FsrSapExportEnabled` defaults off in source.
- Credentials are stored in `app.config` and, for debug SMTP, in source. This document does not repeat the secret values.

## Error conditions

| Condition | Behaviour |
|---|---|
| Repsly settings missing | Log and skip import |
| ESnell settings missing | Log and skip that export |
| FSR promotion validation miss | Log warning, leave the order unlinked, continue |
| SOAP fault or HTTP error on a Repsly order | Log, leave `blnSentToESnell` at 0 |
| FSR SAP retry exhausted | Email `SapFailureNotifyEmail` |
| Unknown `sysCronJob.Type` | Row stays `New` |
| Run exceeds 40 minutes | Loop stops; in-flight `Processing` rows return to `New` |
| `Debug` true | No automatic run |

## Assumptions requiring confirmation

1. Production launches this exe on a schedule, with `Debug` false.
2. The Superbowl web application is the only writer of `sysCronJob` rows besides this exe's own `FSR_NewRequisition` insert.
3. Database scripts under `Scripts/` have been applied before the 2026 code runs.
4. The live Edward Snell endpoint and the live value of `FsrSapExportEnabled`.
5. CSV free-stock and purchase-order imports are retired on purpose.