Link to home
Start Free TrialLog in
Avatar of mohsheikh
mohsheikh

asked on

Ordering Application with wrong stock

Dear all,

I created an ordering application that connected to MS access database. 25 users are using this application at the same time.
I usually check the stock avilabilty after the user select the item if stock avilable i insert new record in the grid without updating the stock on hand in item master table (Because the user may cancel the order or the power down).
When the user click the save button i update stock on hand field in item master table then save the order.
The problem is the time between selecting the item and update stock on hand if any other user create a new order this running order well not effect the stock at this stage.
Example I have item 1 with stock on hand 100.
If user A create an order with five different items if he select item 1 with qty 100 then the system will acept that qty. at this time (the stock still 100-user A does not save order yet) if user B create an order with same item he will find 100 unit avilable on the stock. after both users save their order the stock becom -100.
I would like to prevent that. Could you help me please?

Best regards
MFE
Avatar of bingie
bingie

Can you have it compare the quantity at save time as well? If insufficient quantities at save time, the order is not saved.
You need to allocate the qty of the incomming orders. Meaning, for example as long as the order is not saved, the qty is allocated. You'll find this usefull down the road as well. Orders are saved but not yet taken out of storage (phisically). Which order come first and which second.? That is why you need yo allocate the incoming quantities, and put on hold orders with insufficient inventory. A reasonable way to do it without touching the storage itself is to create a status for the item in the order. For example: "New" while the order is entered. "Allocated" while it is waiting to be pulled out of storage, "pending" if you dont have enough inventory, etc....
This as a nutshell. You'll find plenty of literature about how to handle stock and allocating orders on the net. This is one of the erp basic modules.
Hope this helps,

S
<<time (the stock still 100-user A does not save order yet) if user B create an order with same item he will find 100 unit avilable on the stock. after both users save their order the stock becom -100.>>

(((After both users save their order the stock become -100)))
this last part is the main point.

2 transactions happen on different times. so when user A(or B) clicked on the save you have to

LOCK the database (pesimistic) and prevent other user do the transaction. you Need to trap the
error properly and show it to the user which clicked on Save after the other.

and this is not very likely to happen a lot so dont worry about it.





Here is the flow you need:


Item                     qty             qty                 qty            qty               qty
                         in Stock    Allocated          Locked     Available        Pending (BO)
 Item #1               100             20                 25             55               100

Explanation:
We have 100 pcs of item #1 in stock. Orders came in for 20 pcs, we have stock, so these orders are allocated. Customer Service locked 25 pcs for VIP customer whose order is gonna come shortly. So the availabel qty is 55 (100 -(20+25)). However, an order of 100 pcs just came in. We have only 55 available, so this order is pending, or on Back Order status. You can achieve that without touching the qty in storage. Simply by creating the status for the items in the orders.

btw. Orders not yet saved are "nothing". You need to save them. Once they are saved, they can be part of the game above.

I hpe this is more clear.

S
Avatar of mohsheikh

ASKER

thanks to all of you for quick respond.

Shauli,
Do you mean create new field in item master table and recoerd the order qty during order creation and before he click save button. if he ignore saving or cancel the order i have to remove the qty from this field again. Do you mean that?
Thanks,
MFE
ASKER CERTIFIED SOLUTION
Avatar of Shauli
Shauli

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ps. To make it more clear. When you run the query, you look for the status in item_status and ofcourse for the qty of that order. Just to make sure that you dont count how many items are allocated, but how many pcs of each allocated item you have. So you look in two fields: item_status AND item_qty.

S
One more point: For locked qty, you need to create a field in the ITEMS table. As the ORDERS table and the ITEMS table as a field in common (item_name or item_number) then you can link these tables or use INNER JOIN in your sql statement to take the locked qty in the same function.

One advice though: If you choose to go that way, then draw flow chart on paper first, and once it is clear, start coding :)

S
Thanks Shauli for your help.
To create item_status field in ORDERS or item table will be update after i click save order.
If the user1 take 5 min to create an order during this time another user can take this stock because we don't save this order & item_status yet.
thanks
"If the user1 take 5 min to create an order during this time another user can take this stock because we don't save this order & item_status yet."

That is right. Now you have to make a decision. Orders not yet saved are "nothing". Keep that in mind. You cant win them all. Only when you save an order, it can participate in the allocation game. If you try to allocate qtys when orders are not saved then you'll end up running after your tail. The rule is FIFO (First in first out). What would happen if 9 users enter a new order at the same time? or 12 users? or 20 usres? That is why "orders not yet saved are nothing".

S
Hi Shauli,
Thanks for your help.
Sorry for late. How could i inform the
"What would happen if 9 users enter a new order at the same time? or 12 users? or 20 usres?"
This is correct but,
if the order has 20 items then the 20 users will loseing time to enter their orders and only one will take qty and the others will not but when? After they press the save button (After they finished).
This is time consuming.
I already have three fields 1-Stock On Hand, 2-Pending Stock, 3-Approved Stock.
When the user inter the order I checking the three fileds and limit the maxmim qty to the current stock. if more that enter the same item at the same time all of the will see avilabe stock but they will lose time to complete this order. at the end they will know that this order can't be saved.
Do you know how we can handle that?
Regards,
MSheikh
"at the end they will know that this order can't be saved."...
That is correct. Only when the order is saved you know if the stock is available for that order, unless you give them an option to lock a qty before the order is entered (but then, if the order entry process is cancelled for some reason, you have to release the locked qty). As I mentioned above, orders not yet saved are not yet orders.

S
ps. Stil you can save the order even if stock is not available. Just put it on "Back Order" status, as I mentioned above.

S