Why is an ATP store overselling a non-backorderable product?
Mojca 1200008G2Y Comments (3) Visits (9780)
A WebSphere Commerce system is rarely set up in a stand alone mode. Rather it is very commonly integrated with other systems, getting updates from / and passes updates to those other systems.
In an online WebSphere Commerce store that is set up with availability to promise (ATP) inventory model, the stock quantities would usually not play an important role. However there may be some products that exist only in limited quantities - just imagine how an online store would be selling the very last couple of tickets for an important soccer game. For these products in limited quantities the store obviously does not want to oversell them under any circumstance.
This can normally be prevented by marking those specific product as "non
But it does not always go so smooth and easy, as I have recently found out on a case (hyper urgent of course). Those are the ones I like the most.
In this particular story there was an external inventory management system in charge of updating the information in the WebSphere Commerce database about the stock and inventory quantities, for example about the quantity that is available for a product being sold in an ATP online store, the changes about the stock (increase / decrease), etc. There were some products set as "non-backorderable" because of their limited quantities.
But yet these products were being sold and oversold. In a case like this, a WebSphere Commerce Administrator's life gets very difficult very quickly. It normally will take some time before figuring out what the problem may be, the store people are in a panic due to overselling a product, and it's not pretty. But it feels very good when the problem is solved.
This is a story about what happened, and maybe it will help someone else solve the problem quickly.
As it turned out, and as it's also commonly the cause, that external inventory management system sometimes does not know exactly how WebSphere Commerce works or what data should it pass to it, nor does it care, and in this situation a severe misunderstanding may happen. I call these "communication problems". If only these systems would want to talk to each other...
An ATP store is looking at RECEIPT table, at the data in quantity received (QTYRECEIVED), quantity in process (QTYINPROCESS), and quantity on hand (QTYONHAND) fields values and this is how it decides whether it should try to do a backorder for a product or not. But for non-backorderable products it will do the math and just come back and say, "No inventory is available" where there isn't any.
One quick look into these fields, we found that there's been plenty of negative values, which were being fed from the external inventory management system, which was thinking that it needed to decrease the stock in WebSphere Commerce data by passing it negative amounts.
Whilst WebSphere Commerce out-of-the-box system does not support these negative values in RECEIPT table, the external inventory management system was convinced that this is how it needs and wants to handle the inventory.
Well, the problem has been solved quickly, by merging the records and making sure that the data is okay. For example:
In addition to merging all records for the same versionspc_id, making a sum(QTYRECEIVED), sum(QTYINPROCESS), sum(QTYONHAND), cleaning out the other records and updating that one remaining record in RECEIPT table with a reasonable value, to ensure that QTYONHAND - QTYINPROCESS > 0, which is as the data should be because back order is disabled, and so QTYINPROCESS should of course be always less than QTYONHAND.
A long term solution approach may be to modify the inventory load customization command, because WebSphere Commerce out-of-the-box inventory adjustment is normally going to update RECEIPT.QTYONHAND with actual inventory. For example, if there is QTYONHAND=100, and inventory decreased by 10, then QTYONHAND will be updated to 90 instead of inserting a new record with -10 in the table.