Solved

Ordering Application with wrong stock

Posted on 2004-04-23
14
276 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:mohsheikh
14 Comments
 
LVL 11

Expert Comment

by:bingie
ID: 10903588
Can you have it compare the quantity at save time as well? If insufficient quantities at save time, the order is not saved.
0
 
LVL 19

Expert Comment

by:Shauli
ID: 10903839
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
0
 
LVL 15

Expert Comment

by:unknown_routine
ID: 10904073
<<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.





0
 
LVL 19

Expert Comment

by:Shauli
ID: 10904088
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
0
 

Author Comment

by:mohsheikh
ID: 10907067
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
0
 
LVL 19

Accepted Solution

by:
Shauli earned 125 total points
ID: 10907598
No. You don't record any qty EXCEPT a qty that was TAKEN OUT of the storage. This is the only time you change numbers.
What you do, you create a TEXT field in the ORDERS table. Lets call it item_status. In this field you record the status of the item, such as "Allocated", "Pending", "Processed", "Shipped" etc... You can cover the whole cycle of the order, or just the inventory module, its up to you.
Now, you create a subroutine, or a function, and each time a new order is entered, this function goes to the orders table and run a query: how many item_status are "allocated", how many item_status are "Pending", how many item_status are ... in any status you create. In addition it looks for the stock number. So what you are left to do is subtract the numbers, and here you have to birds in one shot. First: live inventory picture for order entry and customer service (and purchasing director if you wish), and the second: every item in an order is put on the right status so you have a complete control over where the order is (again, for customer service when the customer calls, etc,,,).

I hope I was clear enough,

S

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 19

Expert Comment

by:Shauli
ID: 10907627
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
0
 
LVL 19

Expert Comment

by:Shauli
ID: 10907702
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
0
 

Author Comment

by:mohsheikh
ID: 10908543
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
0
 
LVL 19

Expert Comment

by:Shauli
ID: 10914462
"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
0
 

Author Comment

by:mohsheikh
ID: 11304624
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
0
 
LVL 19

Expert Comment

by:Shauli
ID: 11305280
"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
0
 
LVL 19

Expert Comment

by:Shauli
ID: 11305290
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
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now