Requests to move inventory — between owners, between dispositions, out to a repair line, back off a lease — arrived the way inter-company work usually arrives: a spreadsheet attached to an email. Someone opened it, read the lines, and built the matching order by hand, one part number at a time.
The format was the first difficulty. Every requesting team exported from something different, so the same template came back with columns bolted on the end, with two columns carrying the same header, and — often enough to matter — as an HTML table saved with a spreadsheet extension, which opens perfectly well by hand and defeats any reader that trusts the file name.
The second difficulty was that nothing was checked before the order existed. A request against stock that had already moved became an order nobody could fill, found at the far end of the process by somebody with no idea where it came from.
- No two requesting teams sent the same file, and the shape changed without notice
- Availability was confirmed after the order was built, if at all
- Reference numbers came off a shared running list — fine until two people work at once
- Nothing connected a finished order back to the message that asked for it
One page with a tab per request type, and behind it a pipeline that runs on a schedule and on demand: read the mailbox, pull the attachments, check every line, build the order file, upload it, record what happened.
The reading end assumes nothing about the sender, because the sender is not going to change.
- An attachment is read as a workbook first and as an HTML table second — the fallback triggers on the parse failure, never on the file extension
- Columns are taken by position, since the template carries two columns under the same name; trailing columns nobody asked for are ignored, and a file with too few columns is an error rather than a guess
- Every line is checked against live available balance at its source disposition before it can become an order; lines that don't balance are dropped and reported, and the lines that do balance still go
- Balanced lines are grouped by owner, source disposition, destination and request type, then split so every reference lands inside the unit band the receiving process accepts — a group slightly over the ceiling becomes two even halves, not one full reference and one nearly empty one
- A line whose quantity straddles a split is divided across the two references rather than rounded onto either side of it
- Each reference number is reserved in the database before it is used, so two runs on the same day cannot issue the same one, and line numbers are renumbered inside each reference
The expensive failure in a pipeline like this is not a crash. It is a partial success that looks finished — the same orders created twice because a run was repeated, or a sheet sent up carrying a reference somebody else is also using.
So the order of operations is deliberate. A message is marked processed before anything is uploaded: if the upload dies, the sheet is left visibly unfinished for a person to push through, which is a smaller problem than a second copy of the same order appearing downstream. Each attachment is processed in isolation, so one unreadable file cannot cost the run the work it has already done.
Where the build cannot be completed correctly it is not completed at all. If a reference can't be generated for a group — an unrecognized request type, a daily sequence run out — that group keeps the requester's original reference, unsplit, and the whole sheet is marked not-uploadable with the reason attached. Nothing invents a number to keep moving.
- One lock is shared by the scheduled run and the page's check-now button, so pressing it mid-run cannot process the same message twice or open a second portal session on one account
- The whole flow has a single off switch that reads no mail, builds nothing and uploads nothing — and if the configuration it reads fails to load, it falls back to the environment rather than defaulting to on
- Every sheet carries its own upload state and reason, so the page shows what happened instead of implying it
- A failed upload never records an empty reason: the exception class is always kept, because blank is indistinguishable from never attempted
- Any run that processed an attachment emails a summary with the dropped lines and the built workbook, whether or not something failed
A request goes from an inbox to a live order without anyone retyping it, and the people who used to key them spend their attention on the exceptions the checks surface — which is the part that needed a person all along.
The audit trail is the quieter win. For any order the system built you can name the message it came from, the sheet it was written to, the references it was split into, and every line dropped along the way with the balance that dropped it. When a requester asks why their transfer came back short, the answer is on the page rather than in somebody's mail folder.
