# Product requirements — edi_sage_middleware (TransactFlow Document Sync) Evidence date: 2026-10-16. Source: `git@bitbucket.org:overdrive/edi_sage_middleware.git`, `main` at `75823ee` (2026-04-21). There is no PRD in the source repository. Requirements below were recovered from the WinForms code, the SQL scripts, ClickOnce metadata, and Git history. Provenance is marked on each item. Secrets that are hardcoded are not copied into this document. The maintained product is `BSBC Source Code`. `BSBC_FTP_Integration` and `ADE Source Code` are separate executables in the same Git repository. Requirements for those trees are called out as such. `.gitignore` says ongoing work is only the BSBC source tree. ## Product purpose An operator runs a Windows program, **TransactFlow Document Sync**, and does two jobs: 1. Choose an invoice date range and export Sage invoices as pipe-delimited CSV, one file per invoice, then upload the file by FTP. 2. Download purchase-order CSV files named `Order_*.csv` from a second FTP server and create Sage back orders. The project folder is still `Sage2GS1Xml`. The GS1 SOAP script in the project is not what the buttons produce. Provenance: **Implemented**. A written statement of the business purpose was not found. Provenance: **Unknown**. ## Users and roles No login screen, role, or permission check exists in the maintained application. Whoever can launch the ClickOnce app, and whose Windows profile holds `My.Settings`, can run it. FTP and email passwords are encrypted with DPAPI for the current Windows user. The SQL login used at runtime is compiled into `SQLConnector` and is not taken from the settings screen. | Role | What they do | Evidence | Provenance | |---|---|---|---| | Desktop operator | Sets dates, runs Process Invoices and Process Orders, reads the log | Main tab | **Implemented** | | Person who configures connections | Saves SQL server and database names, two FTP accounts, the local folder, and SMTP | Settings tabs | **Implemented** | | Email recipient | Receives the HTML processing report | `ToAddress`. Committed default in `Settings.settings` is `renier@overdrive.co.za` | **Implemented** as a default. Whether that mailbox is still the live recipient is **Unknown / Requires Confirmation** | | Helpdesk | Named on the form | Label text `Helpdesk: 021 448 5759 \| helpdesk@overdrive.co.za` | **Implemented** as on-screen text. Whether that line still answers is **Unknown** | | Retailer or EDI consumer | Receives invoice CSV, or places `Order_*.csv` on the order FTP | Account comments and FTP upload/download | **Inferred** from account codes and file movement. The consumer system is not named in code | | Sage user | Works with the back order the procedure inserts | `InvNum` / `_btblInvoiceLines` writes | **Inferred**. No Sage UI is in this repository | ## Main workflows ### WF-001 — Start the application Trigger: the operator launches the ClickOnce application. Implemented behaviour (`frmMain_Load`): - Date from and date to are set to the current date and time. - If the app is network-deployed, the window title becomes `TransactFlow Document Sync V` plus the ClickOnce version. If that read fails, the designer title `TransactFlow Document Processing` remains. The catch is empty. - If `ApplicationDeployment.IsNetworkDeployed` and `CheckForUpdate` is true, a message says an update is required, `Update` runs, and `Application.Restart` is called. `AutoUpdateEnabled` in settings is not consulted. ClickOnce project `UpdateRequired` is false, while this code updates whenever a check finds an update. - Saved settings are loaded. FTP and email passwords are decrypted with DPAPI (`DataProtectionScope.CurrentUser`, entropy text `BSBC_Invoice_Export_v2_Salt`). - If the SQL server box is empty, it becomes `localhost`. If the file path is empty, it becomes `C:\Temp\BSBC_Export`. - Three `SQLConnector` objects are pointed at the invoice database (header and details) and the order database. Empty database names fall back to `BSBC_Test`. Provenance: **Implemented**. ### WF-002 — Export invoices Trigger: operator clicks **Process Invoices**. Preconditions: `SQLHeader.QueryExecuterIsIdle`. Otherwise a message says a SQL request is still open. Steps the code performs: 1. Process CSV files already in `\RETRY_UPLOAD\\*.csv`. Each file is uploaded to `ftp:///` (the subfolder name is not part of the FTP path). Success deletes the local retry file. 2. `EXEC [SO_GETINVOICES]` with `@EFFECTIVE_FROM` and `@EFFECTIVE_TO` formatted `yyyy-MM-dd` from the date pickers. 3. For each row, read `Reference` and `AccountLink`. 4. `AccountLink` `5261` writes `\SHOPRITE\.csv`. `6960` writes `\PNP\.csv`. Any other value leaves the path empty. The list procedure already restricts rows to those two accounts. 5. `EXEC [SO_GETINVOICEDETAILS] @Reference=''`. The detail rows are written as pipe-separated columns 0 through 26, one line per detail row. There is no header row in the file. 6. The local path is recorded, then `FTPFile` uploads to `ftp:///.csv`. 7. Upload failure moves the file to `\RETRY_UPLOAD\SHOPRITE\` or `PNP\`. A file already under `RETRY_UPLOAD` is not moved again. 8. Files that still exist are zipped to `\Backup\Invoices_Uploaded_.zip` and then deleted. Zip entries keep the relative path from the base folder. 9. Zip files of that prefix whose name-embedded timestamp is older than 90 days are deleted. 10. An HTML email "TransactFlow Document Processing Report" is sent with the date range, counts, failed file names, and the log text. Provenance: **Implemented**. ### WF-003 — Import orders Trigger: operator clicks **Process Orders**. The invoice date pickers are not used. Preconditions: `SQLOrder.QueryExecuterIsIdle`. Otherwise a message says a SQL request is still open. Steps the code performs: 1. Ensure `\ORDERS` exists. 2. Process `\ORDERS\Retry\*.csv` first. 3. List the order FTP root. Keep names that start with `Order_` and end with `.csv`. Download each file into `ORDERS`, then delete it from the FTP server. Deletion happens before Sage insert. 4. For each downloaded file, parse pipe-delimited lines, group them by purchase-order number, and call `dbo.SO_INSERTORDER` once per purchase order. 5. Success, "order already exists", "no client match", and "would be truncated" move the file to `ORDERS\Processed`. Other failures move it to `ORDERS\Retry`. 6. An HTML email "TransactFlow Order Processing Report" is sent when at least one file was downloaded, or when the retry folder produced failures and no new file was downloaded. 7. Successfully inserted files (not the three skip categories above) are zipped to `\Backup\Orders_Uploaded_.zip` with flat file names. The zip does not delete sources. 8. Every file in `ORDERS\Processed` is then deleted, including files that were not added to the zip. 9. Order zip files older than 90 days, by the timestamp in the file name, are deleted. Provenance: **Implemented**. ### WF-004 — Save settings Trigger: SAVE on SQL, FTP, or Email, or Browse on General. - SQL save stores server, invoice database, and order database, then rebuilds the three connections. It does not store `SQLUsername` or `SQLPassword`. - FTP save stores both server names and usernames in clear text, and both passwords through DPAPI. - Email save stores SMTP host, port, username, from, to, and SSL flag. The password goes through DPAPI. - Browse stores `XMLPath`. Provenance: **Implemented**. ### WF-005 — ADE invoice export (separate executable) Trigger: a WinForms timer on `ADE Source Code/Project/Form1.cs`. The comment says 5 seconds. `Interval` is `600000` (10 minutes). Each tick calls `ExportInvoices` then `UploadInvoices`. Export calls stored procedure `SUN_INVOICE_spSel` (body not in Git) and, when `c:\xprocure\uploaded\.csv` does not already exist, writes one pipe-delimited line of every column to `c:\xprocure\.csv`. Upload appends one more `|`, FTP-uploads the file into a `PNPInvoices` folder, and moves it to `c:\xprocure\uploaded\`. Provenance: **Implemented** in that tree. The timer comment and the interval disagree. Provenance of the comment: **Documented** in source. Provenance of the interval: **Implemented**. ### WF-006 — Older BSBC invoice export (separate executable) `BSBC_FTP_Integration` loads with window title `BSBC Invoice Export V (25 June 2018)` when ClickOnce is available. Process asks whether to run an FTP test. The non-test path calls `SUN_GETINVOICES` and `SUN_GETINVOICEDETAILS` (bodies not in Git), writes 23 pipe fields (indexes 0–22) plus a trailing line that is only `|`, and uploads Shoprite/Checkers (`5261`) and PNP (`6960`) to different FTP directories. The local path variable is set to an empty string before use. Settings-save bodies are commented out. Provenance: **Implemented**. Whether this build can complete a real export is **Inferred** as unlikely, because the output path and SQL server name are placeholders. **Requires Confirmation** if any PC still runs it. ## Functional requirements ### FR-001 — Manual operation The system shall export invoices and import orders only when the operator clicks the matching button. No scheduler, service, or queue worker is in the repository. Evidence: `cmdProcessInvoices_Click`, `cmdProcessOrders_Click`. No Windows service project. Implementation: **Implemented**. Confidence: High. ### FR-002 — Invoice population The system shall list invoices by executing `SO_GETINVOICES` for the selected from-date and to-date, formatted as dates without a time. The script in Git selects `Reference` and `AccountLink` from `_bvARTransactionsFull` where `TrCode` is `INV`, `AccountLink` is `5261` or `6960`, `TxDate` is between the two parameters inclusive, and `ExtOrderNum` is not null, not blank, and does not start with `CRN`. Evidence: `frmMain.vb` query string; `SO_GETINVOICES.sql`. Implementation: **Implemented** in the app and in the script. Whether the script matches the procedure installed on the server is **Unknown / Requires Confirmation**. Confidence: High for the client call. Medium for the server body. ### FR-003 — Invoice file layout The system shall write one `.csv` per invoice reference. Each detail row is 27 pipe-separated fields and a newline. Columns, in order, from `SO_GETINVOICEDETAILS`: | Index | Script column | |---|---| | 0 | Date (`TxDate` as `yyyy-MM-dd`) | | 1 | Invoice number (`Reference`) | | 2 | Seller GLN, constant `6009900156101` | | 3 | Buyer GLN, `Client.Post5` with prefixes `GLN `, `GLN: `, and `No.: ` removed | | 4 | Seller VAT registration, constant `4810259673` | | 5 | Stock code | | 6 | `Description_1`, trimmed | | 7 | Barcode | | 8 | Unit of measure, constant `BT` | | 9 | Weight unit of measure, empty string | | 10 | `cSellWeight` | | 11 | `fQuantityInvoiced` | | 12 | Unit selling price, `ActualSalesValue / fQuantityInvoiced` | | 13 | Line total excluding VAT, `ActualSalesValue` | | 14 | VAT amount, `ActualSalesValue * 1.15 - ActualSalesValue` | | 15 | Line total including VAT, `ActualSalesValue * 1.15` | | 16 | Discount percentage, constant `0` | | 17 | Discount value, constant `0` | | 18 | Order number, `ExtOrderNum` | | 19 | Invoice total excluding VAT, `Outstanding - Tax_Amount` | | 20 | Invoice VAT amount, `Tax_Amount` | | 21 | Invoice total including VAT, `Outstanding` | | 22 | Pack size, constant `1` | | 23 | Currency, `CurrencyCode` or `ZAR` | | 24 | Ship-to store name | | 25 | Country, `cEUCountryCode` or `ZA` | | 26 | Invoice name from the customer group (see business rules) | Evidence: `frmMain.vb` field loop `0` to `26`; `SO_GETINVOICEDETAILS.sql`. Implementation: **Implemented**. Confidence: High. ### FR-004 — Retailer folders for invoices The system shall place account `5261` files in a `SHOPRITE` folder and account `6960` files in a `PNP` folder. Comments in the list script and in the form identify `5261` as Checkers and `6960` as PNP. The FTP URL for both accounts, and for retry, is the invoice FTP host plus the file name only. Evidence: `cmdProcessInvoices_Click`, `ProcessRetryFiles`, `FTPFile`. Implementation: **Implemented**. Confidence: High. Whether the remote system wanted a retailer directory is **Unknown / Requires Confirmation**. ### FR-005 — Invoice upload retry When an upload throws, the system shall move the file into `RETRY_UPLOAD` under the retailer folder, unless it is already there. The next Process Invoices run shall try those files again before querying Sage. A successful retry shall delete the local file. Evidence: `FTPFile`, `ProcessRetryFiles`. Implementation: **Implemented**. Confidence: High. ### FR-006 — Invoice backup After the invoice loop, the system shall zip remaining local invoice files into `Backup\Invoices_Uploaded_.zip` and delete the files that were added. It shall delete zip files of that name pattern whose embedded `yyyyMMddHHmm` is more than 90 days before now. Evidence: `CreateZipFile`, `CleanupOldZipFiles`. Commit `c728aba`. Implementation: **Implemented**. Confidence: High. ### FR-007 — Order file selection The system shall download from the order FTP root only files whose names start with `Order_` and end with `.csv`, then delete each downloaded name from that FTP server. Evidence: `DownloadOrderFiles`, `DeleteOrderFileFromFTP`. Implementation: **Implemented**. Confidence: High. ### FR-008 — Order file parse The system shall treat a line without a pipe as ignorable. It shall skip the first line when its first field is not a parseable date-time. It shall skip a line with fewer than 28 fields. It shall map up to 35 fields: `DateTime`, `PurchaseOrderNumber`, `BuyerReferenceNumber`, `SellerGLN`, `BuyerGLN`, `AccountNumber`, `SellerName`, `SellerVATNumber`, `BuyerRegionGLN`, `BuyerRegionDescription`, `ShipToStoreGLN`, `ShipToStoreCode`, `ShipToStoreName`, `DeliveryDate`, `SellerProductCode`, `SellerBarcode`, `BuyerProductDescription`, `BuyerProductCode`, `WeightUOM`, `Weight`, `UOM`, `OrderQty`, `SingleUnitPrice`, `DiscountPercentage`, `DiscountValue`, `LinePriceExcl`, `LineVat`, `LinePriceIncl`, `OrderType`, `Filler1`, `Filler2`, `PackConfiguration`, `Filler3`, `Originator`, `VendorNumber`. Lines shall be grouped by `PurchaseOrderNumber`. One procedure call shall be made per group. Header values sent to SQL are taken from the first line of the group: order date, delivery date, purchase-order number, ship-to GLN, ship-to code, and ship-to name. `ValidateOrderData` lists mandatory fields and numeric checks. Its call is commented out, so those checks do not run. Evidence: `ProcessOrderFile`, `InsertOrderBatchIntoDatabase`. Implementation: **Implemented**. The validator is **Implemented** as unused code. Confidence: High. ### FR-009 — Sage back order `SO_INSERTORDER` shall insert a Sage sales-order document (`DocType` 4) with `DocVersion` 1, `DocState` 1, `DocFlag` 0, description `Sales Order`, tax not inclusive, tax per line, empty invoice number, and empty delivery note. The procedure comment calls `DocState` 1 a back order. The client is the `Client` row whose address-like fields contain the ship-to GLN. Physical and postal columns score higher than the other searched columns. The best score wins; ties break by `DCLink`. The client's price list and a delivery method are required. Duplicate detection is an existing `InvNum` row with the same `DocType`, `AccountID`, and `ExtOrderNum`. That case returns `Result` 2 and does not insert. The next order number is `SO` plus eight digits, allocated under a table lock on `InvNum`. Evidence: `SilOve_INSERTORDER.sql`. Implementation: **Implemented** in the script. Application on the server is **Unknown / Requires Confirmation**. Confidence: High for the script. ### FR-010 — Partial order lines A line shall be skipped, and reported, when the barcode does not resolve, the quantity is null or not positive, or the unit price is null or negative. A skipped line is not inserted. If every line is skipped, the procedure returns `Result` 1, inserts no document, and still counts as success in the desktop app. Barcode resolution tries `StkItem.ucIIPackBarcode` first, then `_etblBarcodes`, using the raw barcode, the right-most 13 characters, the barcode with the first character removed, and each of those with a leading `0`. When several stock items match, the one whose case size equals `PackConfiguration` is preferred. Quantity stored on the Sage line is order quantity times a factor. The factor is `PackConfiguration` when that is not zero, otherwise the stock item case size, otherwise 1. The unit price stored is the file's single-unit price divided by that factor. VAT rate used by the procedure is 15 (`DefaultTaxTypeID` 1). Unit of measure is the stock item's sell unit when that id exists in `_etblUnits`, else the stocking unit, else the unit whose code is `UNIT`. `fQuantity`, `fQuantityUR`, `fQtyChange`, and `fQtyChangeUR` are all set to that pack-adjusted quantity. Provenance of that equality: commit `75823ee`. Evidence: `SilOve_INSERTORDER.sql`, `InsertOrderBatchIntoDatabase`. Implementation: **Implemented** in the script. Confidence: High. ### FR-011 — Order file disposition | Outcome | Local file | Counted as | Included in the order zip | |---|---|---|---| | Procedure accepted (`Result` not 2, no exception), including all lines skipped | `ORDERS\Processed`, then deleted when the folder is cleared | Success | Yes | | Message contains "Order already exists" | `Processed`, then deleted | Success on a new download | No | | Message contains "No client match" | `Processed`, then deleted | Success on a new download | No | | Message contains "would be truncated" | `Processed`, then deleted | Success on a new download | No | | Other errors the script is considered to have handled | `ORDERS\Retry` | Failure | No | | Other errors | `ORDERS\Retry` | Failure | No | Handled script messages are: no client match, no price list, no delivery method, order already exists, unresolved barcode, UNIT not found, and `SO_INSERTORDER failed:`. A missing `#SO_INSERTORDER_LINES` temp table is not treated as handled. The first purchase order in a file that fails returns immediately. Later purchase orders in that file are not attempted. Purchase orders already committed earlier in the same file stay in Sage. Evidence: `ProcessOrderFile`, `cmdProcessOrders_Click`, `IsHandledByOrderScript`. Implementation: **Implemented**. Confidence: High. ### FR-012 — Processing email The system shall send one HTML report per process click when SMTP server, port, from address, and to address are all non-empty and a ping of the SMTP host returns success. SSL follows the checkbox. The client forces TLS 1.2. The certificate callback accepts every certificate. Timeout is 60 seconds. Username and password are used only when the username is non-empty. If the ping fails, the email is not sent. Invoice processing still completes the files before that. The order report includes separate sections for retry-folder failures, new-download failures, orders that already exist, GLN misses, and truncated data. Evidence: `SendEmail`, `SendProcessingReport`, `SendOrderProcessingReport`. Implementation: **Implemented**. Confidence: High. ### FR-013 — ClickOnce distribution The maintained project shall be a ClickOnce Windows application, product **TransactFlow Document Sync**, publisher **Silicon Overdrive**, install URL `https://overdriveqa.co.za/BSBC_App_Update/`, version `2.0.0.*`, application revision 17 in the project file, minimum required version `1.0.0.59`, update mode foreground, update check interval 1 day, install from the web, desktop shortcut created. The assembly attributes still say version `1.0.0.0`. The window shows the ClickOnce version only when network-deployed. Evidence: `Sage2GS1Xml.vbproj`, `AssemblyInfo.vb`, `frmMain_Load`. Implementation: **Implemented** as project metadata. Whether revision 17 is what clients have installed is **Unknown / Requires Confirmation**. Confidence: High for the project file. ### FR-014 — ADE export The ADE executable shall, on its timer, export invoices from catalog `ADE - Live` through `SUN_INVOICE_spSel` into `c:\xprocure\` and upload new CSV files to the PNP invoices FTP folder, then move them to `c:\xprocure\uploaded\`. Evidence: `Form1.cs`. Implementation: **Implemented** in that project. It is not part of TransactFlow Document Sync. Confidence: High for the code. Whether it is still deployed is **Unknown**. ## Integrations | System | Direction | Trigger | Data | Auth as implemented | Failure | |---|---|---|---|---|---| | SQL Server, invoice database | Read | Process Invoices | Invoice list and 27-column detail | Login compiled into `SQLConnector`. Server and database name from settings | Exception text in the log and the email. Busy worker blocks the click | | SQL Server, order database | Write | Process Orders | Back-order header and lines | Same connector class, order database name | Classified into retry or processed. See FR-011 | | Invoice FTP | Upload | After each CSV is written, and on retry | One CSV per invoice at the FTP root | Username from settings. Password from DPAPI | Move to `RETRY_UPLOAD` | | Order FTP | Download then delete | Process Orders | `Order_*.csv` | Separate host, username, and password | Download error is logged and that file is skipped. Delete failure is a warning; the local file is still processed | | SMTP | Outbound mail | End of a run | HTML report | Settings. Optional username | Log the SMTP status. The file work is not rolled back | | ClickOnce host `overdriveqa.co.za` | Inbound update | Startup, if network-deployed | Application update | Not shown in code | Empty catch. The app continues | | Sage tables | Written by `SO_INSERTORDER` | Each purchase order | `InvNum`, `_btblInvoiceLines` | The SQL login | Procedure `RAISERROR`, or `Result` 2 | No REST API, message queue, or webhook is implemented in this repository. ## Important constraints - .NET Framework 4.8 WinForms. `Option Strict` is off. The manifest requests `asInvoker`. Single-instance is false. - Invoice and order databases may differ. Both default to `BSBC_Test` in settings and in code. The invoice scripts say `USE [BSBC]`. - The SQL username and password arguments passed by the form are the literal strings `.SQLUsername` and `.SQLPassword`. `SetConnectionString` does not use those arguments. - Invoice queries concatenate the reference and the dates into the SQL text. Order batch text escapes single quotes on some strings and parses numbers with the invariant culture. - GS1 XML at 14% VAT remains in `SUN_SAGE2XML.sql` and is not the live export. The live invoice VAT factor is `1.15`. The live order VAT rate is 15. ## Documented vs implemented conflicts | Topic | One source | Other source | |---|---|---| | Working trees | `.gitignore` says ignore `ADE Source Code/` and `BSBC_FTP_Integration/` | Both are still tracked | | Discovery snapshot | Issue text: `main` at `5b9772a` (2026-01-19), plus Sonar files at the root | `main` at `75823ee` (2026-04-21). Those Sonar files are not in Git | | Product name | Folder `Sage2GS1Xml`, script `SUN_SAGE2XML` | Running product is CSV in and CSV out, branded TransactFlow | | Database | Scripts `USE [BSBC]` | App default `BSBC_Test` | | Update policy | Project `UpdateRequired` is false. Settings `AutoUpdateEnabled` exists | Startup installs an update whenever ClickOnce reports one, and does not read `AutoUpdateEnabled` | | Assembly version | `1.0.0.0` | ClickOnce `2.0.0.*` revision 17 | | ADE timer comment | "5 seconds" | Interval `600000` milliseconds | | Failed-archive comment | Enum comment says handled errors go to a retry folder and that no FAILED folder is used | Both handled and unhandled order failures are left in `Retry`, so the next run attempts them again | | Price list | The procedure requires a client price list and looks up `_etblPriceListPrices` | The inserted unit price is the file price divided by the pack factor. `PriceListExcl` is selected in a CTE and not used in the insert | | Warehouse | Comment says warehouse id is set to the delivery-method id | That assignment is what the insert writes | ## Unknown / Requires Confirmation - Legal name behind BSBC, and who the operator is. - Which database catalog is production, and whether the three scripts have been applied there. - Whether the invoice FTP consumer distinguishes Shoprite from PNP without a remote folder. - Whether files removed from `Processed` without a zip (already exists, no GLN match, truncated data) are an accepted loss of the local copy. - Whether a multi-PO file should stop at the first failing purchase order. - Whether ADE and `BSBC_FTP_Integration` are still in use. - Whether TransactFlow Document Sync feeds `edi_shopfront` or replaces `sagegs1xml`. - The tracker that owns commit tags `#482_BSBC`, `#680_BSBC`, and `#711`.