Case study · Process automation
SAP vs QWMS Inventory Reconciliation
Inventory reconciliation between the ERP and the warehouse system used to depend on a few expert employees. I turned it into a self-service web tool. Any employee can now run it in under 5 minutes, and it is used every working day in 4 warehouse buildings.
- Users: one person in each of 4 buildings, no expertise needed
- Status: Phase 1 in production · Phase 2 (fully automated) built and running alongside it
- Role: Sole developer
Background
Two systems track the same inventory. SAP, the company's ERP, holds the book stock. QWMS, the warehouse management system, holds the physical stock on the shelves. The two have to be compared regularly to confirm that they agree.
The comparison looks simple but isn't. Deciding whether a difference is real requires a long list of unwritten rules and judgment calls. Only employees who knew both systems and the business context behind the data could do it reliably.
This comparison is the first step of the warehouse's daily cycle count. To save time, it covers only the materials that moved that day. Stock that nobody touched can't have changed, so it is skipped.
Any difference between the systems has to be resolved before anyone goes to the warehouse floor. Most differences are sync failures: a movement was posted in SAP but never reached QWMS. Those are synced manually. Anything else is investigated until the cause is clear, the same day.
Once the two systems agree, the result is exported as an Excel count sheet. The sheet is printed and used for the physical count on the warehouse floor.
Four warehouse buildings run this count every day. They share the same plants in SAP and are told apart only by storage type, each with its own inclusion and exclusion rules. Picking the right rows for a building was one of the things only the experts knew how to do.
Impact of the problem
- Slow: even an experienced employee needed 15–20 minutes per reconciliation.
- Single point of failure: when a key person was on leave or left the company, the work stopped.
- Long ramp-up: new hires needed a long time to learn the rules well enough to do it alone.
- Error-prone: manual judgment produced inconsistent results and occasional misses.
Solution
The main result is knowledge encapsulation. The business rules and judgment calls that only experts knew now live in the application. Expert knowledge became a tool that anyone can use. That matters more than the time it saves.
Phase 1 · Web application (in production)
Before
An expert exports data from both systems and compares it by hand, applying rules from memory.
15–20 min, and only a few people can do it.
After
Any employee picks their building, downloads three standard exports (two from SAP, one from QWMS, about 1–2 min) and uploads them. The app applies every rule and returns the result in about a minute: the median run takes 62 seconds, and 90% finish within 2.5 minutes.
< 5 min end to end, and no background knowledge is needed.
Finding the cause of each difference
Comparing stock levels shows that a material is off, but not why. So the app works the way an expert does. It starts from the day's material documents in SAP (transaction MB51), which also tell it which materials moved today. It then pulls SAP's stock for those materials from a stock report and compares both the documents and the stock with QWMS, matching documents by document number.
- Movement in SAP but missing in QWMS: the movement never synced. This is the most common case, and it is fixed by syncing it manually.
- Stock still in QWMS but already gone in SAP: SAP is the source of truth, so the material is treated as issued and the leftover record is removed from QWMS. The physical count then confirms it: either the material really is gone, or SAP missed a movement.
- Any other mismatch: flagged for investigation.
Each document also shows who posted it, so the person resolving a difference knows exactly which transaction to look at and whom to ask.
Dashboard
- Six KPI cards summarize the day's reconciliation for the selected building: parts received, parts shipped, parts with movement, transaction mismatches, cycle-count accuracy and open exceptions. Each card is broken down by plant and opens the rows behind the number.
- A progress bar that can be trusted: it reaches 100% only after the back end has fully synced to the page, so 100% means the numbers on screen are final.
- Recent activity shows which building and which person made each update, when it started and finished, and whether it succeeded.
- Sign-in and permissions: staff log in, and an admin page controls who can do what.
Phase 2 · Fully automated standalone app (built, running alongside production)
Phase 2 removes the last manual step. The employee picks a building and clicks Refresh, and the app does the rest:
- Downloads the stock report and the MB51 movements from SAP by driving SAP GUI with keyboard input.
- Runs QWMS's stock-confirmation step for that building's storage types, waits until QWMS confirms it, then downloads the QWMS stock. This is done through browser automation.
- Applies the building's rules and merges the data.
- Checks its own output, then writes the Excel count sheet from the day's template.
- Self-checking: the merged result is reconciled against the raw SAP and QWMS exports, and the finished Excel file is read back row by row. Any difference fails the run, and the last good report stays available for download.
- Independent buildings: each building has its own folder, lock and status, so several buildings can refresh at the same time. Only the SAP step takes turns.
- Mirror mode for safe rollout: a background watcher rebuilds a report in the new app every time someone uploads in the production app. The two versions can be compared side by side on real daily data before anyone switches over.
- One machine, inside the network: the app runs on a single Windows machine, either locally for one person or shared on the internal network. Credentials live only in a local configuration file.
Monitoring and assistance (Phase 2)
- Cycle count monitor: every 10 minutes, the app downloads both systems and compares each building's movements for the day. A building shows Ready to count once every SAP document has reached QWMS, or N documents to go with how long each has been waiting. Mistakes such as a plant left out of a download are caught before the count starts.
- Operator view: for each QWMS operator, the documents processed and the average time from the SAP posting to the QWMS update.
- Material movements: receipts and issues by storage location for one day, or a trend line per operator over a date range.
- AI assistant: answers questions about inventory, cycle counts and movements in English or Chinese, with optional voice input. It queries the selected building's data through a read-only connection that only allows SELECT statements. Each person sees only their own history, every question is logged, and questions are rate-limited per person.
Tech stack
Phase 1 · Production web application
Phase 2 · Automated standalone app
Architecture
Key challenges
1. Turning tacit expert knowledge into explicit rules
ProblemThe rules lived in a few people's heads, including many edge cases and judgment calls that were never written down.
ApproachI wrote each judgment call as an explicit rule in the application. Every rule runs the same way on every run, so the result no longer depends on who does the check. The most valuable rule is the diagnosis an expert used to do by hand: matching SAP's MB51 documents against QWMS by document number to see which movement is missing on which side.
SafeguardEvery run checks itself. The merged result is reconciled against the raw SAP and QWMS exports, and the finished Excel file is read back row by row against the building's rules. If anything doesn't match, the run fails with the reason instead of producing a wrong count sheet.
2. Automating SAP with no integration interfaces
ProblemThe enterprise environment does not expose OData, RFC or SAP GUI Scripting, so none of the usual integration routes were available.
ApproachI built an RPA-style solution with PyAutoGUI that drives SAP through simulated keyboard input, without relying on screen coordinates. Moving the window or changing the screen resolution does not break it, which is the usual weak point of UI automation. The bot is already built and runs reliably unattended.
- Reads the screen instead of guessing: OCR (Tesseract) confirms each SAP window has appeared before the next keys are sent.
- One bot at a time: a machine-wide lock makes sure only one job types into SAP GUI at once. Other buildings' jobs queue for that step, and the browser automation for QWMS never starts while SAP is being typed into.
- Fails cleanly: every step has a timeout and a clear error message, such as "SAP download failed" or "QWMS step timed out". A stuck step stops the run instead of hanging, and a report is never built from stale data.
3. Meeting enterprise data-security expectations
ProblemInventory data is sensitive, and the tool needs system credentials to download it.
Approach
- Stays inside the network: Phase 2 runs on one Windows machine and talks to SAP and QWMS directly. Nothing goes to an outside service except the questions people choose to ask the AI assistant.
- Credentials kept out of the code: logins live only in a local configuration file that is never shared or committed.
- Narrow web surface: the server only serves the page and its JSON endpoints. Downloads are limited to each building's own finished reports, and raw exports and databases are refused.
- Read-only AI: the assistant can only run SELECT queries on a read-only connection.
4. Several buildings using the tool at the same time
ProblemThe tool is shared by four buildings, and they submit jobs at the same time. Their runs must not interfere with each other, their data must stay separate, and when a result looks wrong, someone has to be able to trace where it came from.
Approach
- Separate data per building: each building's files, status and results are kept apart, so one building's run never touches another's. A second Refresh of the same building while it runs is rejected with a clear message.
- A lock on SAP access: on the server, only one job can work in SAP at a time. Other jobs wait their turn instead of colliding.
- Upload history: every uploaded file is saved on its own, with who uploaded it. If a result looks wrong, the exact file can be pulled up to check whether the problem started with the download.
- Easy to extend: a building is one small module holding only its storage rule. Adding a building means writing that module and registering it, and nothing else changes.
5. Carrying each day's work over to the next
ProblemThe cycle count runs every day, and each day depends on the day before: the count file keeps changing, and stock counted yesterday shouldn't be counted again today.
Approach
- Rolling template: operators update their count file every day. The app finds the file updated the previous day and uses it as the new template.
- Count ledger: a ledger records which documents were counted each day. The next day, those document numbers are removed from the MB51 list, so nothing is counted twice.
6. Two systems, two time zones
ProblemSAP records posting dates in the headquarters' time zone, while the warehouse and its count cutoff run on local time. A naive date filter silently drops or double-counts movements near midnight.
ApproachThe app pulls MB51 from the day before the cutoff, converts each posting's date and time to local time, and only then applies the cutoff. The window is set automatically from the previous day's count template, so nobody has to calculate it.
Results
- Expertise built in: any employee can run the reconciliation without knowing the systems or the business rules.
- ~70% faster: 15–20 minutes per run became under 5 minutes end to end.
- Consistent results: one set of rules is applied every time, which removes person-to-person differences and missed checks.
- Adopted in production: one person in each of the 4 buildings runs it for the daily cycle count, once a day.
- Used every working day: 339 runs were logged from late August to early November 2026, across 52 working days. Each building counts once a day; the extra runs are same-day refreshes as new data arrives. 93% of runs completed successfully, and the rest stopped with a clear error instead of producing a wrong count sheet.
- Fast: once the data is uploaded, the median run takes 62 seconds, and 90% finish within 2.5 minutes.
- ~170–250 hours saved per year: 10–15 minutes saved per run × one run per working day (~250 days) × 4 buildings.
Demo
Roadmap
-
Completed
Phase 1 · Web comparison tool
All business rules encoded in a self-service web app. In daily production use in 4 buildings.
-
Rolling out
Phase 2 · Fully automated standalone app
Keyboard-driven SAP automation and browser automation for QWMS remove the last manual step. The app also adds self-checking reports, a 10-minute cycle count monitor and a read-only AI assistant. It currently runs alongside Phase 1, mirroring every production upload so results can be compared before switching over.
-
Planned
Phase 3 · Closing the loop
Automatically check each uploaded count result against the monitor's snapshot for that count's time, and warn when the number of moved parts is far off. Add daily and monthly operator totals from the transaction history the monitor already keeps.
Reflection
- Check integration limits first. The browser's lack of access to SAP is what kept one manual step in Phase 1. Checking how the tool could reach each system at the start would have pointed to the fully automated, standalone architecture sooner.
- Measure from day one. Logging every run (who, when, how long, whether it succeeded) from launch would make the impact easy to quantify. Today the total hours saved has to be estimated.
More projects
Analytics and machine-learning case studies, plus the SQL & Python practice platform this site runs on.