# PRD — forecast (RGBC Forecast Control) Evidence date: 2026-10-14. Source: `git@bitbucket.org:overdrive/forecast.git`, `master` at `6aac1d2` (2026-09-01). There is no prior PRD in Silicon Ark and no requirements document in the source repository. Requirements below are recovered from pages, classes, `Web.config`, `.env.example`, and source comments. Provenance is labelled. Connection-string templates in `.env.example` use placeholders. Those placeholders are not copied here. ## Product purpose **Implemented.** RGBC Forecast Control lets a signed-in user plan a brand's future fiscal forecast, adjust the working forecast while a season is open, and queue generation of product-level quantity forecasts. Reports show adjusted forecast 9L and a mix of actuals and adjusted QF. **Documented** only as comments in `Admin.Master.vb`: phase 1 was reports and the adjustment interface; phase 2 was the planning interface "to generate future QF". **Unknown / Requires Confirmation:** the commercial owner of the forecast (which company RGBC is), the business definition of QF, and whether 9L means nine-litre case equivalent. The code and captions use the token 9L. The stock update multiplies summed 9L by `9000` and divides by product `intSize` and `intPack` (`QFGenerator.vb`). Interpreting `9000` as centilitres in a nine-litre case is **Inferred**. ## Users and roles Login does not ask for a password. `Index.aspx` has a Login button. The click handler reads `Request.LogonUserIdentity.Name`, upper-cases it, and looks up `mstUser.strDomainLogin` joined to `mstUserGroupForecast`. A match stores the row in session `USER`, writes `mstUsageLog`, and opens `Home.aspx`. No match shows "Insufficient Access" and says login is controlled through Active Directory. `Web.config` sets ``. `FormsAuthentication.SignOut()` runs on the login page and on failure. There is no forms-login ticket issued in this code. Provenance: **Implemented**. Session timeout is 720 minutes, in-process, cookies required (`Web.config`). | Actor | What the code does | Status | |---|---|---| | Windows user whose domain login is in `mstUser` | Session is set. Usage row inserted | **Implemented** | | Windows user with no matching row | Message: insufficient access, contact the administrator | **Implemented** | | User with `blnView` not true | Master page clears page content and shows a view-rights message | **Implemented** | | User whose `strUserGroup` is not `Dev` | Sandbox menu item is removed | **Implemented** | | User with `blnSpecial` true | May open Planning and Open Season, enable Apply, and edit any user on My Brands | **Implemented** | | User without `blnSpecial` | Planning and Open Season redirect to `Home.aspx?Message=...`. My Brands keeps only that user's dropdown entry | **Implemented** | | User with `blnEdit` true, during an open season | Adjustments Save is enabled | **Implemented** | | User with `strUserGroup` = `RSM` | Brand list limited to `mstUserBrand`. Region list limited to `mstUser.strRegion`. Calculate Regions disabled | **Implemented** | | Debug build, no session | Master page loads user `SILICON\PJ` instead of redirecting to login | **Implemented**, compile-time `#If DEBUG` only | | Person who opens `TestDbConnection.aspx` | Page does not use the master page and does not check session. It opens SQL, counts `mstUser`, and prints the Windows identity | **Implemented** | The login query is `SELECT *` from the group join. Pages read `blnView`, `blnEdit`, `blnSpecial`, `strUserGroup`, `strUserName`, `UserID`, and `strRegion` from that row. Which table defines the boolean flags is **Unknown / Requires Confirmation**. Group names that appear as string comparisons are `Dev` and `RSM`. Other groups can exist in `mstUserGroupForecast` and are not named in code. **Unknown / Requires Confirmation.** `Home.aspx` does not read the `Message` query string. Redirects that attach an insufficient-rights message therefore land on Home without showing that text. Provenance: **Implemented** omission. ## Systems the product talks to 1. The operator's browser. 2. Windows / Active Directory, only as the IIS logon identity name. No directory search API is called. 3. One SQL Server database, connection name `SuperbowlConnectionString`, value from `RGBC_FORECAST_CONNECTION_STRING` or `SUPERBOWL_CONNECTION_STRING`. 4. SonarQube, from Bitbucket Pipelines on `master` only. Not a runtime dependency. No email, file share, HTTP API, or queue broker appears in the project. The cron queue is a SQL table. ## Major workflows ### WF-1 — Sign in and move around Trigger: user opens the site. Default document is `Index.aspx`. 1. Page load signs out forms authentication and clears session. Query `M=1` sets "Your Session has expired and you have been logged out." The master page sends an anonymous session to `Index.aspx?M=1` outside a debug build. 2. Login click looks up the Windows identity. Success goes to `Home.aspx`. Failure stays on Index. 3. Every master page load requires `Session("USER")`. The menu is Home, Planning, Adjustments, Reports, Open Season, My Brands, Sandbox (Dev only), and Logout. Logout navigates to `Index.aspx`, which clears the session. 4. If `blnView` is not true, the content placeholder is replaced with an error label. The menu remains. Home loads `mstPeriod` for `yyyyMM` of today. It does not render the forecast report. The report call is commented out. ### WF-2 — Plan a future fiscal year Page: `Planning.aspx`. Gate: `blnSpecial`, else redirect home. Rules written in the page comment: planning is for future fiscals; changes to the current fiscal belong on Adjustments; saving a plan overrides adjustments. Behaviour: 1. User picks a financial year (`mstPeriod.intFiscalYear > 2010`) and an active brand. Optional copy-from brand. 2. Load builds a `Branding` object and shows sales rhythm, stats, and a planning grid. 3. Save stays disabled unless the selected fiscal year is greater than the fiscal year of today's `yyyyMM` period. Otherwise the label says "Can only generate Forecast for future Fiscals!" 4. Save is also tied to `blnSpecial`. 5. The button confirms: "Are you sure you want to install this Forecast Plan? This process may take up to an hour to complete..." 6. Save deletes `dwfQFPlan` and `dwfQFAdjustment` for the brand and fiscal window, asynchronously deletes matching product rows from the table named by setting `dwfQFTable` (`dwfQF`), inserts plan rows, copies them into `dwfQFAdjustment` with `blnApplied = 0` and a "New Record from PLANNING" log, then queues `sysCronJob` type `BrandQF`. 7. The in-process `QFGen.GenerateBrand` / `CopyBrand` calls are commented out. The label then says "Brand QFs will be processed shortly." Provenance: **Implemented**. The one-hour warning describes work this request does not perform. **Requires Confirmation** that an external worker still does that work. ### WF-3 — Adjust the open model Page: `Adjustments.aspx`. Class name `Adjestments`. 1. User selects fiscal year, brand, and region (customer group name, plus `- All -`). 2. Load shows planned/historic, stats, adjusted, and actual grids. 3. Save is enabled only when `mstOpenSeason` has a row for brand `0` or the selected brand whose start and end dates include today, and `blnEdit` is true. Otherwise the label says "Model Locked." or "Model Unlocked: {dates}." 4. Save writes changed cells to `dwfQFAdjustment` (`blnApplied = 0`, log line with old and new values, user, timestamp). Inserts a row when the update affects zero rows. 5. Apply is enabled when `blnSpecial` is true, including when the model is locked. Apply does not call `QFGen`. It removes duplicate `New` `BrandQF` jobs with the same parameter string and inserts a new `New` job. Label: "Brand QFs will be processed shortly." Confirm text matches Planning's one-hour warning. 6. Calculate Regions is disabled for `RSM`. It spreads a changed Global cell across groups using `%C QF LY` from planning stats. It updates the grid text. It does not itself write SQL; the user still saves. 7. Export renders the adjusted grid as `application/vnd.ms-excel`. 8. Double-click on an adjustment cell opens `Comments.aspx` in `window.showModalDialog` with period, brand, and customer number. RSM brand and region lists are reduced on first render, as in the users table. ### WF-4 — Generate product QF `QFGen` documents the formula in comments: `QF[period][sku][customer] = (sales of brand to customer / sales of brand in group) * (sku sales / applicable sales) * group QF adjustment` for the last completed fiscal as the sales base, and the plan year's adjustment as the quantity. `Generate` then: - Reads unapplied-capable adjustments joined to `dwdCustomer.strGroupName`. - Inserts `dwfQF` rows (`dblBudget9l`, `percRegion`, `percProduct`). The physical table name is the setting `dwfQF`, not a hardcoded-only name. - Sets `dwfQFAdjustment.blnApplied` to `1` when the rounded generated total matches the adjustment. - Updates `stkProductPeriod.dblQF` with `Round(summed budget * 9000 / intSize * intPack, 4)` for the forecast product code's first five characters. - Asynchronously deletes `dwfQF` rows where `percRegion = 0`. Products with `blnQF = 2` are excluded from the customer sales share. Applicable SKU share uses `blnQF = 1`. Forecast product codes are the first five characters of the product code plus `F`. `CopyBrand` copies from another brand's largest `blnQF = 1` product and throws `DataException` with a row count. Planning's copy path is commented out; the cron parameter still includes `CopyBrandID`. Nothing in this repository selects `sysCronJob`. Provenance of the generator: **Implemented** class. Provenance of it running after Apply: **Unknown / Requires Confirmation**. ### WF-5 — Reports Page: `ReportConsole.aspx`. No extra rights check beyond the master page view gate. Filters: financial year (required, years after 2009), principal, daily report grouping (`mstGroupingDaily`), brand (all, my brands = `-1`, or one active brand), brand manager, region. Region is labelled "RSM Report Only". Decimals checkbox. Reports: - Forecast Report (`rpt.forecast`): active-filter is not applied (`WHERE 1=1`). Grid caption "STATS & FY {year} - Adjusted Forecast 9L". Region filter is not passed into `Branding`. - RSM Report (`rpt.rsm`): `mstBrand.blnActive = 1`. Grid caption "FY {year} - ACTUALS & QF 9L". Region filter is passed. `My Brands` limits `BrandID` to `mstUserBrand` for the session user. Export writes HTML with an Excel content type and a filename from the filter ids and timestamp. Run requires a financial year. Query string `Run=1` runs on first load. "Run in new Window" and "Open in new Window" open `ReportConsole.aspx` with the current filters. ### WF-6 — Open a season Page: `OpenSeason.aspx`. Gate: `blnSpecial`. The user picks a start date (default today) and an end date (default the last day of the current month) and clicks set. The insert always uses `refBrandID = 0`, which the grid displays as `- All -`. A successful insert says "New Season Added". The duplicate check does not run (`If False`). The grid lists seasons with brand name, dates, last user, and last edit, and can delete by `OpenSeasonID`. Open season is what unlocks Adjustments Save. It does not unlock Planning. Planning uses the future-fiscal rule instead. ### WF-7 — My brands and region Page: `MyBrands.aspx`. A special user can pick any user. Any other user sees only themselves. Load reads `mstUser.strRegion` and `mstUserBrand`. Save deletes that user's brand links, inserts the checked active brands, and sets `mstUser.strRegion`. If the edited user is the session user, session `strRegion` is updated too. Dropdown text shows user name, forecast group, and brand count. ### WF-8 — Notes on an adjustment cell `Comments.aspx` reads query `P`, `BID`, and `C` (cleaned by doubling quotes). It loads `dwfQFAdjustment` for that period, brand, and customer and shows notes, log, and last edit. Save writes `strNotes`, `strLastUser`, and `dtStamp`. It does not change `dblQF`. In a debug build, an empty query string substitutes period `200909`, brand `6`, customer `HISTJ1H`. `AdjustmentsComments.aspx` has no logic. ## Functional requirements ### FR-001 — Windows login The system admits a person only when the Windows logon name matches `mstUser.strDomainLogin` for the Forecast group join. - Actor: browser user already authenticated to IIS. - Trigger: Login on `Index.aspx`. - Input: `Request.LogonUserIdentity.Name`. - Behaviour: session `USER` set to the joined row; `mstUsageLog` insert with system `Forecast` and timestamp `yyyyMMddHHmmss`. - Output: redirect to `Home.aspx`, or an insufficient-access message. - Validation: `db.CleanString` on the identity name before it is embedded in SQL. - Error: exceptions are shown HTML-encoded; session user is cleared. - Evidence: `Index.aspx.vb`, `SystemFunctions.LogginUser`, `ProjectFunctions.sql_get_Users`. - Implementation: **Implemented**. Confidence: high. ### FR-002 — Session gate Content pages on `Admin.Master` require a session user. A missing session redirects to `Index.aspx?M=1` in a non-debug build. - Evidence: `Admin.Master.vb`. - Implementation: **Implemented**. Debug build substitutes a fixed domain user. Confidence: high. ### FR-003 — View right A user with `blnView` not true sees an error instead of page content. - Evidence: `Admin.Master.vb`. - Implementation: **Implemented**. Confidence: high. ### FR-004 — Future-only planning Planning save is offered only when the selected `intFiscalYear` is greater than the fiscal year of the current `yyyyMM` period, and the user has `blnSpecial`. - Evidence: `Planning.aspx.vb` comment `r1` and the enable block. - Implementation: **Implemented**. Confidence: high. ### FR-005 — Plan save replaces the year Saving a plan deletes existing plan and adjustment rows for that brand and fiscal window, writes the grid, copies plan rows into adjustments as unapplied, deletes generated QF rows for the brand's `F` product codes, and queues one `BrandQF` job. - Evidence: `Planning.aspx.vb` `btnSave_Click`, `ProjectFunctions.sql_insert_BrandQF_Job`. - Implementation: **Implemented**. The comment says this overrides adjustments. Confidence: high. ### FR-006 — Open season controls adjustment save Adjustments can be saved only inside an `mstOpenSeason` window for all brands or that brand, and only when `blnEdit` is true. - Evidence: `Adjustments.aspx.vb` `LoadBrand`. - Implementation: **Implemented**. Confidence: high. ### FR-007 — Apply queues work Apply, for a special user, marks matching new jobs removed and inserts a new `BrandQF` job. It does not generate rows in the web request. - Evidence: `Adjustments.aspx.vb` `Apply`. - Implementation: **Implemented** queue write. Downstream processing: **Unknown**. Confidence: high for the write. ### FR-008 — Customer group is the region axis Grids, filters, and QF generation group customers by `dwdCustomer.strGroupName`. Constructing `Branding` copies `mstCustomer.strCustomerGroup` into `strGroupName` and `mstCustomer.refCustomerGroupID` into `strRegionOrder` when they differ. - Evidence: `Branding.New`, `Planning.aspx`, `Adjustments.aspx`, `ReportConsole.aspx`, `MyBrands.aspx`, `QFGenerator.vb`. Commit `b4c83ce`. - Implementation: **Implemented**. Confidence: high. ### FR-009 — Region rollup customer A group is stored against one customer number. `LoadCustomers` keeps customer numbers like `HIST%`, one row per group name, first by `strRegionOrder` then customer number. A second `HIST` customer in the same group is ignored. - Evidence: `Branding.LoadCustomers` and its comment. - Implementation: **Implemented**. Confidence: high. ### FR-010 — Two reports and Excel export The console runs Forecast Report and RSM Report and can download the HTML grid as `.xls`. - Evidence: `ReportConsole.aspx`, `brand.reports.vb`. - Implementation: **Implemented**. Confidence: high. ### FR-011 — Notes A user can read and save notes on one adjustment cell without changing the quantity. - Evidence: `Comments.aspx.vb`, `Adjustments.aspx` `jsViewComments`. - Implementation: **Implemented**. `showModalDialog` is an old browser API. Whether current browsers still open it is **Unknown / Requires Confirmation**. ### FR-012 — Connection string from the environment The app reads the SQL connection from configuration if that value is non-empty, otherwise from the environment. Shipped `Web.config` connection strings are empty. Startup does not write `Web.config`. - Evidence: `db.GetConnectionString`, `SecureConfig`, `Global.asax.vb`, `.env.example`. - Implementation: **Implemented**. Confidence: high. ## Integrations | System | Purpose | Trigger | Direction | Auth | Failure | |---|---|---|---|---|---| | SQL Server | All product data | Page load, button, `SqlDataSource` | Read and write | SQL user inside the connection string | `doQuery` writes the SQL and exception to the response for many failures. `getRow` returns `Nothing` | | Windows identity | User key | Login and `TestDbConnection` | Read | IIS Windows authentication | Login shows insufficient access | | SonarQube | Static scan | Push to `master` | Pipeline to Sonar | `SONAR_TOKEN`, `SONAR_HOST_URL` repository variables | Pipeline failure. No deploy depends on it | | `sysCronJob` consumer | Run `BrandQF` | Insert of status `New` | This app writes only | Same database | **Unknown**. The UI still says processing will happen shortly | ## Important constraints - Fiscal boundaries come from `mstCompany` company 1: if `strFYStart = "01"` the fiscal year is that calendar year; otherwise it starts in the previous calendar year at `strFYStart` and ends in the fiscal year at `strFYEnd`. **Implemented.** - Planning and adjustment grids use fixed month columns July through June in the empty SQL templates (`Planning.aspx`). If company 1's fiscal year is not July–June, the labels and the date logic can diverge. **Requires Confirmation.** - Session is in-process. A recycle drops every login. Timeout is 12 hours. - `httpRuntime executionTimeout` is 43200 seconds and `maxRequestLength` is 104856 KB. **Implemented** configuration. Why those values were chosen is **Unknown**. - `customErrors` is `Off` and `compilation debug` is `true` in the committed `Web.config`. - SQL is concatenated. `CleanString` doubles single quotes and trims. It is not parameterised queries. - One shared `SqlConnection` is reused for the application domain (`db.conn`). - Numbers are formatted with a replacement of character 160 by a comma because of the server's number format (`ProjectFunctions.FormatNumber`). **Implemented.** ## Assumptions kept out of the requirements - That an external scheduler still runs `BrandQF` jobs. - That RGBC's fiscal year is July–June. The UI columns suggest it. The company row is the authority and was not readable from this repository. - That `blnView`, `blnEdit`, and `blnSpecial` are group-level permissions rather than per-user columns. - That past periods should be locked. The 2015 comment says they were unlocked, and the condition cannot succeed while `And False` remains.