# PRD — rgbc_projects_net

Evidence date: 2026-10-05.

Source: `git@bitbucket.org:overdrive/rgbc_projects_net.git`, `master` at `08268a8` (2021-03-08).

There is no separate requirements document in the repository. Requirements below were recovered from pages, SQL strings, and comments. Provenance is marked. A comment is not treated as the live rule when the code does something else.

This repository holds eight applications. They are specified separately. Shared login and database facts are in the first section.

The later People Development Tool repository is documented in `silicon_ark/RGBC_ScoreCard/`. This PRD covers only the Score Card code inside this archive.

## Product purpose

**Inferred** from titles and menus, not from a product statement:

- **Forecast** maintains brand forecasts, lets authorised users adjust figures, plan future quantity forecasts, and open adjustment seasons.
- **Promotions** plans and records promotions, with an audit log and a My Brands screen.
- **Score Card** is titled HR: People Development Tool. Comments say the Score Card was replaced by the People Balance Sheet. Menus cover home, team setup, surveys, questionnaires, and overall performance.
- **Stock Control** shows a product-period grid (opening, forecast, BMA, actuals, orders, receipts, and related columns) and a variance report.
- **Superbowl Web** maintains customers, products, reps, branches, and related master data, plus logs and a month-end action.
- **Superbowl Daily** and **Superbowl Daily 2** load external sales, stock, survey, vendor, and GL files into the database and write export files.
- **Superbowl Cronjob** processes rows in `sysCronJob` and sends freestock and purchase-order emails.

What RGBC stands for is **Unknown / Requires Confirmation**.

## Shared data

**Implemented.** Web and desktop projects connect to SQL Server catalog `Superbowl`. Table names used by login and the features below include `mstUser`, `mstUserGroup` plus a module suffix (`Forecast`, `Promotion`, `ScoreCard`, and the Superbowl/Stock Control group tables), `mstUsageLog`, `mstCompany`, `mstPeriod`, `mstBrand`, `stkProductPeriod`, `dwfSales`, `sysCronJob`, `porRequisition`, `porDocument`, `mstVendor`, and `sysQuery`.

The database project that owns these tables is not in this repository. Column nullability, keys, and procedures are **Unknown** except where a SQL string in this tree names them.

## Users and interactions

People sign in with a Windows identity. The applications do not collect a password in the login click handlers that were read. ASP.NET `authentication mode` is `Windows` for Forecast, Promotions, and Score Card, and `Forms` for Stock Control and Superbowl Web. All five still call `FormsAuthentication.SignOut()` on the login page and then read `Request.LogonUserIdentity.Name`. How IIS is configured to supply that identity is **Unknown / Requires Confirmation**.

Desktop programs have no login form in the code that was read. They use the SQL connection in `app.config` / settings.

---

## FR-001 — Sign in (Forecast, Promotions, Score Card)

**Who:** A Windows user whose `mstUser.strDomainLogin` matches the uppercase Windows identity.

**Trigger:** Click Login on `Index.aspx`.

**Implemented behaviour:**

1. Page load signs out Forms authentication and clears the session. Query `M=1` shows "Your Session has expired and you have been logged out."
2. `LogginUser` looks up the identity. Forecast joins `mstUserGroupForecast`. Promotions joins `mstUserGroupPromotion`. Score Card joins `mstUserGroupScoreCard`.
3. A matching row is stored in `Session("USER")`. Score Card also sets `USERNAME`, `USERID`, and `USERGROUP`. Promotions also sets `USERID`.
4. A row is inserted into `mstUsageLog`. Forecast writes `strSystem = 'Forecast'`. Promotions and Score Card also write `strSystem = 'Forecast'`. That string is in the Score Card and Promotions source; it is not a Forecast-only path. **Implemented.** Whether the shared log was meant to say `Promotion` or `ScoreCard` is **Unknown / Requires Confirmation**.
5. The browser goes to `Home.aspx`. No match shows "Insufficient Access" and says login is controlled through Active Directory.
6. Score Card only: if `OVERRIDE_USERNAME` is on the query string, that value replaces the Windows identity before the lookup. Comment date 2018-05-28. **Implemented.**

**Error:** Exceptions on Forecast and Promotions login are shown on the page, including the exception message. Score Card login swallows lookup errors and returns false (insufficient access). The master page, when `Session("USER")` is empty outside a DEBUG build, redirects to `Index.aspx?M=1`.

**Evidence:** `Index.aspx.vb` and `App_Code/VB/SystemFunctions.vb` in each of the three web projects.

## FR-002 — Sign in (Stock Control and Superbowl Web)

**Who:** A Windows user returned by `sql_get_Users` for domain `StockControl` or `Superbowl`.

**Trigger:** Click Login on `index.aspx`.

**Implemented behaviour:**

1. Page load signs out and clears `UDOMAIN`.
2. The click handler reads `Request.LogonUserIdentity.Name`, not the text box. Stock Control focuses `txtUser` on load; the click handler does not read it.
3. The query requires `SystemStockControl = 'StockControl'` or `SystemSuperbowl = 'Superbowl'` on the joined group row.
4. On a match, app settings `UNAME`, `UDOMAIN`, `UID`, `UGROUP`, and `UGROUPID` are set. Superbowl Web also sets `ULEVEL` from `intLevel` and `Session("User")` from `strUserName`.
5. `mstUsageLog` is inserted with `strSystem` `StockControl` or `Superbowl`.
6. Stock Control redirects to `ProductPeriod.aspx`. Superbowl Web redirects to `Home.aspx`.

**Error:** No row: "Insufficient Access". Exceptions are written to the page. Stock Control's catch block also writes the connection string onto the page. **Implemented.**

**Session check:** Stock Control master redirects to `index.aspx` when `UDOMAIN` or `UGROUP` is empty. Superbowl Web also redirects when `ULEVEL` is `0`. DEBUG builds fill dummy settings instead.

**Evidence:** `index.aspx.vb`, `Admin.Master.vb`, `sqlDB.vb` in both projects.

## FR-003 — Forecast screens

**Who:** A signed-in Forecast user. Flags `blnView`, `blnEdit`, and `blnSpecial` come from the user row. Group name `Dev` is compared as a string.

**Trigger:** Menu selection.

**Implemented behaviour:**

| Screen | Behaviour found |
|---|---|
| Home | Menu target after login. Detailed home widgets were not fully traced |
| Planning | If `blnSpecial` is not true, redirect to Home with "Insufficient rights". Save stays enabled only when `blnSpecial` is true. Planning inserts `sysCronJob` rows of type `BrandQF` |
| Adjustments | Save enabled when `blnEdit` is true. Apply enabled when `blnSpecial` is true |
| Reports | `ReportConsole.aspx` is on the menu |
| Open Season | Tooltip "Administer Brand Adjustment Periods". Requires `blnSpecial`, otherwise redirect to Home. Set button enabled only for `blnSpecial` |
| My Brands | If `blnSpecial` is false, the page still loads; the non-special branch exists in `MyBrands.aspx.vb` |
| Sandbox | Menu item removed unless `strUserGroup` is `Dev` |
| Help, Help Planning, Comments, Adjustments Comments | Pages exist. They are not all top-level menu items |

**View gate:** If `blnView` is not true, the master page clears the content placeholder and shows "You need View rights to access this page." The master `Page_Load` runs after the content page `Page_Load` (comment in `Admin.Master.vb`). **Implemented.**

**Version shown:** `1.0.3` and date 4 April 2012, plus ` [Dev]` or ` [Live]` from the compile symbol.

**Evidence:** `Admin.Master`, `Admin.Master.vb`, `Planning.aspx.vb`, `Adjustments.aspx.vb`, `OpenSeason.aspx.vb`, `MyBrands.aspx.vb`, `ProjectFunctions.vb` (`sql_insert_BrandQF_Job`).

## FR-004 — Promotions screens

**Who:** A signed-in Promotions user (`mstUserGroupPromotion`).

**Implemented behaviour:**

- Menu: Home, Planning, Promotions, My Brands, Audit Log, Logout. Sandbox is `Selectable="False"` and is removed from the menu unless the group is `Dev`.
- `blnView` false clears the content area with the same view-rights message as Forecast.
- `MyBrands.aspx.vb` checks `blnSpecial`.
- An `Account` folder (Login, Register, ChangePassword) and `Site.Master` are in the project. They are not on the admin menu. Whether they are reachable in IIS is **Unknown / Requires Confirmation**. `Web.config` includes an unused-looking `ApplicationServices` connection to a local `aspnetdb.mdf`.
- Version shown: `1.0.3`, 15 January 2015.

**Evidence:** `Admin.Master`, `Admin.Master.vb`, `MyBrands.aspx.vb`, `Web.config`.

## FR-005 — Score Card screens

**Who:** A signed-in Score Card user. Group names handled in the master page are `RSM`, `User`, `Admin`, and `Dev`.

**Implemented behaviour:**

- Page title: HR: People Development Tool.
- Menu items present: Home, Edit Team Setup, Edit Surveys, Edit Questionnaires, Overall Performance, Logout.
- Commented out, not shown: New Score Card, New People Development Tool, New Sales Capabilities. The pages `ScoreCard.aspx`, `PeopleBalanceSheet.aspx`, `PeopleBalanceSheetNew.aspx`, and `SalesCapabilities.aspx` are still in the project.
- `RSM` and `User` have menu items removed from the end of the menu (team setup, surveys, questionnaires, overall performance, and the separators in front of them), leaving Home and Logout. `Admin` and `Dev` keep the full menu.
- `blnView` false clears content, same message as Forecast.
- Mobile: `setMasterPage` picks `Mobile.Master` when the user agent matches a mobile pattern or `Request.Browser.IsMobileDevice` is true. Otherwise `Admin.master`.
- Version shown: `1.2.0`, 1 September 2018.
- A backup copy of `SalesCapabilities.aspx` sits in `Backup 20170306`. It is not the live page.

**Evidence:** `Admin.Master`, `Admin.Master.vb`, `SystemFunctions.setMasterPage`.

Questionnaire scoring, email-on-submit, and GPS behaviour are implemented in the later `rgbc_scorecard` repository's documentation. They were not re-verified page by page in this archive. Treat that later PRD as a different revision.

## FR-006 — Stock Control product period

**Who:** A signed-in Stock Control user. The group name is matched with `ToLower().Contains(...)`, not an exact list.

**Trigger:** Login lands on `ProductPeriod.aspx`. Menu: Home (that page), Imports (list), Variance Report, Stock Control Report.

**Implemented behaviour:**

- The grid covers 15 periods. Columns include opening, QF, BMA, actual, orders, receipts, exports, damages, repack, closing, and months cover.
- Brand group (name contains `brand`): can save BMA (`dblBMA`) and is stamped in `strLastUser` / `dtStamp`.
- Shipping or order group (name contains `shipping` or `order`): can save orders (`dblOrders`) and is stamped in `strLastUserSM` / `dtStampSM`.
- Admin (name contains `admin`): can save both, and the Imports menu item is enabled. Other groups have Imports disabled.
- If the product code is `"0"`, `setSecurity("Viewer")` is used. Otherwise the user's group is used.
- Target months cover is `mstBrand.dblVariance` for the selected brand. The variance report uses `Target Variance` from its query, and `2.5` when that value is null (`db.nz`). Months cover is coloured when it is more than `0.5` away from that target.
- Direct Sales row (commit `08268a8`): for a single product, sums `dwfSales.lngUnitsTotal * mstProduct.dblFlat` for product codes whose first five characters match. For product code `"0"` (all-products / 9L path), sums `dwfSales.dbl9lTotal` for the brand. The SQL has no extra predicate that says "direct". The row label is Direct Sales. **Implemented** label and query. Whether the sales table already contains only direct sales is **Unknown / Requires Confirmation**.
- Missing product-period rows are inserted as zeros via `sql_insert_PP` when `intPP = 0`.

**Evidence:** `ProductPeriod.aspx`, `ProductPeriod.aspx.vb`, `sqlDB.vb`, `Admin.Master.vb`, `BMAReport.aspx.vb`.

## FR-007 — Superbowl Web master data

**Who:** A signed-in Superbowl user with a non-zero `intLevel`.

**Implemented behaviour:**

Menu groups and pages:

- Home.
- Accounts: Customers, Key Accounts, Key Account Groups, Snell Areas, Snell Super Groups, AdCheck Customers, Non RGBC Customers.
- Product: Brands, Companies, Principals, Products, AdCheck Products, Product Archive, Sales Reps, Rep Managers. A menu item titled `[Sales QF]` has an empty `NavigateUrl`. `SalesRepQF.aspx` exists.
- Setup: Branches, Channel Segments, Rep Segments, Users, Export Sales, Audit Log, Daily Log, Integrity Test, Month End BFBE, Usage Log, Product Archive (repeated).

User insert/update SQL writes group foreign keys for Stock Control, Superbowl, Forecast, Promotion, Score Card, Free Stock, and POR, plus manager flags `blnManager` and `blnManagerExecutive`. **Implemented** in `sql_insert_Users` / `sql_update_Users`. The meaning of each group id is data, not code.

Product archive, from a 2013 comment, sends a job to the cron program (`ArchiveProduct`). **Implemented** as a cron case. The web click that inserts the job was not re-read line by line.

An admin-only menu enable block in `Admin.Master.vb` is commented out. **Implemented** as inactive.

**Evidence:** `Admin.Master`, `Admin.Master.vb`, `sqlDB.vb`.

## FR-008 — Superbowl Daily import

**Who:** An operator, or a non-debug startup that calls `Run()` immediately.

**Trigger:** Form load. If `My.Settings.blnDebug` is false, `Run()` starts without a click. DEBUG requires a button.

**Implemented behaviour:** The program expects files in an `Imports` folder next to the executable and moves processed files to `Imported`. Named inputs include `cognos09.txt` (customers), `cognos14.txt` (sales), `zmb59-daily.txt`, `cognos12.txt` (stock), segmentation and AdCheck workbooks, SALBA, call/order and survey/promo CSVs, and `AdSpend.xls`. Output path literal: `\\Benriach\Sales\eSalesCharter\NewFiles\`.

Buttons exist for test, email log, fix, post, SALBA, retotal, sales-charter queries, and Mondayly BFBE export. The Mondayly comment says files generate every day and mail only on Mondays and the first day of the month. That comment is historical (2009). The live method is `bfbeExports`. Full branch behaviour of every button was not line-traced.

**Evidence:** `Main.vb` header, `gFile` / `gType` arrays, `frmMain_Load`.

## FR-009 — Superbowl Daily 2 import and export

**Who:** An operator. Version label "RGBC Superbowl Daily Version 2" plus assembly build, dated 19 February 2019 in source.

**Implemented behaviour:**

Imports registered in `frmMain_Load`:

| Name | File or view | Share or object |
|---|---|---|
| Offcon Promo | `OffPromoSurvey.csv` | `\\Benriach\Sales\2WaySystem\` in release |
| Offcon Survey | `OffPremiseSurvey.csv` | `\\Benriach\Sales\ADM\` |
| Oncon Promo | `OnPromoSurvey.csv` | ADM share |
| Oncon Survey | `OnPremiseSurvey.csv` | ADM share |
| Rep Calls | `eSalesCharter_CallsAndOrders.csv` | ADM share |
| Vendor | view `vwAPVendors` | database view, file name `apvendors_.csv` |
| GL Code | view `vwGLAccounts` | database view, file name `GLCodes.csv` |

Exports: AdvPDA CRM (`pda_crm.csv` to the 2Way share), AdvPDA ExpCRM, AdvPDA PROD, Planned Promotions (`PlannedPromotions.csv`).

A Monday un-invoiced requisition email was disabled in a 2016 comment and a button `btnEmailRequisitions` still exists. Whether the click handler is empty was not confirmed past the method list. **Requires confirmation** if that email can still be sent.

Release download roots are the Benriach shares above. DEBUG builds point at `E:\work\RGBC\`.

**Evidence:** `frmMain.vb`.

## FR-010 — Cron job worker

**Who:** An operator who clicks Run, or whatever starts the WinForms process. No Windows Service or scheduler project is in the repo.

**Trigger:** `Run()` selects `sysCronJob` where `Status = 'New'`, ordered by date descending then type.

**Implemented behaviour:**

Each row is set to `Processing`, then to `Processed`, `Error`, or `Processed_MailError`.

Job types in the `Select Case`:

| Type | What the code does |
|---|---|
| `BrandQF` | Forecast quantity-forecast generation. Inserted by Forecast `sql_insert_BrandQF_Job`. Duplicate pending jobs can be marked `Removed` |
| `ArchiveProduct` | Archives a product code (comment: 13 sub-steps) |
| `FSR_NewRequisition` | Emails a new freestock requisition with PDF |
| `FSR_RequisitionApproved` | Emails an approved freestock requisition with PDF |
| `POR_1NewRequisition` | Emails a new purchase-order requisition with PDF and quote files |
| `POR_2ApprovedRequisition` | Emails an approved PO |
| `POR_3Receipted` | Receipted PO mail and document copy. Header comments also describe quantity-based status |
| `POR_2DeclinedRequisition` | Declined PO email |
| `POR_2CancelledRequisition` | Cancelled PO email |
| `POR_CompletedRequisition` | Completed PO email. Added 2021-02-09. Loads body SQL from `sysQuery` id `61` and the requisition joined to user and vendor |

Header comments describe additional processes that are not all separate `Case` labels: FSR file import, alternate-manager resend, POR export files, and a pending-approval reminder. Those run inside `PreProcess` or inside the cases above. A full line map of `PreProcess` was not copied here.

Release mail uses `My.Settings.SMTPHost` and from-addresses `administrator@rgbc.co.za`. DEBUG mail uses `smtp.office365.com` port 587 with a credential compiled into source. The credential value is intentionally omitted.

If a manager is missing, the job result is set to `No Manager` for the cases that check it.

**Evidence:** `frmCronjob.vb` header and `Run()` case labels. Forecast `ProjectFunctions.vb`.

## Integrations

| System | Direction | Evidence |
|---|---|---|
| Active Directory / Windows identity | Inbound identity string only. No LDAP calls were found | `LogonUserIdentity` |
| SQL Server `Superbowl` | Read/write | Connection strings |
| SQL Server `TRGBC` on `BenriachE` | Daily 2 connection present | `App.config` |
| SMTP | Outbound mail | Cronjob, Daily, Daily 2 |
| File shares on `Benriach` | Import and export files | Path literals |
| AdCheck, Cognos, SALBA, Snell, Advantex-style PDA files | File formats named in Daily / Daily 2 | File name arrays |
| Office 365 SMTP | DEBUG cron builds only | `#If DEBUG` |

Authentication to those file sources, and failure behaviour when a share is down, are **Unknown** beyond the local log writes in Daily (`WriteLog`).

## Important constraints

- SQL is concatenated in VB. `CleanString` is used on some inputs and not on others (Forecast `sql_get_Users` concatenates the domain login directly).
- User rights for Forecast are boolean columns on the user row (`blnView`, `blnEdit`, `blnSpecial`). Stock Control rights are substrings of the group name.
- Web session state is required. Empty session returns the user to the login page.
- There is no automated test that locks these rules in.

## Requirements that could not be confirmed

- Production host names, IIS authentication mode, and whether Forms versus Windows in `Web.config` matches the servers.
- The business definition of BMA, QF, BFBE, SALBA, Snell, and AdCheck beyond the labels in code.
- Whether Direct Sales is a filtered sales channel or a label on total `dwfSales`.
- The current text of `sysQuery` id `61`.
- Any requirement newer than 8 March 2021.