Large trade requests
A trade buyer sends a spreadsheet of hundreds or thousands of parts
Reads it in one step, matches it to our catalogue, records every line under one request number, asks the supplier only for what we can supply, and puts the supplier's prices back on the same rows
The situation
Trade buyers (fleets, contractors, shipping companies) send requests as spreadsheets. A vessel's list can be hundreds or thousands of lines, in the customer's own layout and language, mixing Caterpillar part numbers with bolts, sealants and other makers' parts. Typing that into a quotation by hand takes days. Sending the whole list to a supplier gets it ignored: they will not wade through lines they cannot price. And without a record, nobody can later say what the customer asked for.
What the app does
The request goes through a fixed order in the quote builder:
Read it. One upload takes any spreadsheet (xlsx, xls, csv), every sheet in it, by its column headings. A file with no usable headings, a PDF or a photo is read by the assistant instead. A file sent on WhatsApp opens in the builder with one click from the customer's thread.
Check the parts. Each part number is matched to our catalogue: our description replaces theirs where we know the part; theirs is kept where we do not. The customer's request is shown beside ours in colour: matched, not in our catalogue, not a Caterpillar number, no part number. Caterpillar-shaped numbers are ticked for the supplier; a new part with a Cat Reman equivalent gets the reman offered as an option beside it. What we already know from our catalogue (weight, lead time, list price, core deposit) is filled in.
Put every line on the sheet. The price request sheet is the record of what was asked. Every line goes under the request's R number, one row each (R141-A, -B … -Z, -AA … so a list of any length fits). A line we will not ask a supplier about is marked NS (not supplied) at once. The rows fold under the first, so a thousand-line request is one line on the sheet until someone opens it. New rows get the sheet's own formulas, so the sheet can price them.
Ask the supplier from the sheet. The supplier request is built from the sheet's rows that are not NS, as the sheet has them. If a salesperson changed a quantity or marked a row NS on the sheet, the sheet wins. Those rows are marked "..." (asked) and the supplier is named on them.
Put the supplier's reply back. Their spreadsheet or PDF is read and each price lands on the row with the same part number, on the sheet and in the quotation. Lines they did not price stay on the request.
Price and send. The quotation shows every line the customer asked for; lines without a price say "not quoted". When it is sent, the priced rows are marked ANSWERED. NS rows stay NS.
What it does not do yet
It cannot tell an obsolete Caterpillar number from a live one, so a list full of obsolete numbers still goes to the supplier. See What comes next.
Supplier emails are not drafted; the supplier request is a file.
The tests that hold it
tests/unit/test_quotes_import.py, test_sheet_holds_the_parts.py, test_price_request_sheet.py. Scenario P in the manual test map runs it end to end on the live system.