Flow
The order the planner works in now
1Group matching rows
Same supplier, type, variant, pack size, case size and cost.
2Count once
Use the biggest single row's need, not the total.
3Hand out whole cases
One case at a time in a fixed order, leftover on one item.
4One set of facts
One row supplies stock and sales numbers for the group.
5Check the room
If the rounded order won't fit, drop to the most whole cases that do, or zero.
A few rows in the ordering system were each planning a full case for the same product on the same shelf, and one of my tests said that was fine.
It starts with the catalog, because a supplier can sell one product under several item numbers. When I review the catalog, I can end up with a few rows that all mean the same goods: same supplier, type, variant, pack size, case size and cost. Each row asks how fast the product sells at a store, and each gets the same answer, since it's one shelf, and then each row plans its own order. That's one need counted three times.
On top of that, each row rounded its share up to a full case, so a store that needed a few units could get a case per row.
Counting the shelf once
I fixed this over a string of commits in one day. First, the cart stopped spreading a small need across every interchangeable item in a group; it now fills one case before it starts on the next item.
Then the planner started grouping rows by that strict key. Spacing and capital letters in the text don't count, but case size and cost must match exactly. Each group gets one count per store.
The group's need is the largest single row's demand, not the sum of them.
Then it gets handed out one case at a time, in a fixed order. The supplier rounds once, on the leftover, not once per row. If you build this, sort on something stable, or two runs on the same data can give two different orders.
I chose the biggest number because I'd rather have stock than run out. Someone who wants lean shelves could use the average.
One set of facts
Each row also had its own copy of stock on hand and sales rate, and the copies could disagree. Now one row per group and store, mostly the one with the biggest demand, supplies those numbers for all of them. That makes the numbers consistent, which isn't the same as correct.
New products on their first order stay out of all this, and a group with one of those in it isn't pooled.
The carry I took back out
Each store usually ends up with a part-full case. I added a step that moved those leftovers between stores to fill one case before starting another. It went in missing a closing brace, and the next commit is one line adding it. Later that day I took the whole step out. Each store's leftover now stays on one item. No reason is recorded; my hunch is it clashed with the new per-store cap.
The room cap
Room is the buyer's top days of supply times daily sales, minus what's on hand. If the rounded-up order fits, the store keeps its real need. If not, it drops to the most whole cases that fit, or zero.
The checks worth copying, all from my tests:
- Several matching rows should order what one row would.
- A need under one case should give one case, on one item.
- An order that would overflow the room should shrink.
- Each store's own total should stay the same after it's spread across items.
All of that runs on made-up stores. What I still don't know is how often the cap will zero out a real store.