Help creating a message pop up on sales order when an item's selling quantity is larger than what is in inventory

Hello,

I have been asked to see if it is possible to have a pop-up tell the sales people when the inventory has less than the item they are trying to sell.

How would I go about that?

Any help is appreciated.

Thanks,

Shawn

sound like a pre process BPM on OrderEntry.

There might be a QTY change event. Then you could check inventory levels right there. then give the pop up if needed

I was thinking something like that but wasn’t sure how to compare the OrderDtl.SellingQuantity to the PartBin.OnHandQty

Do you have any ideas?

you could do it via a query? on where onhandqty is less than selling qty. If a row is returned then pop up the message box

I am reading through the methods for SalesOrder ans came across one called NegativeInventoryTest. Do you know anything about that one? Or how I could find out more about it?

not sure. Does the trace call it? Is there a global setting that will not allow orders for negative inventory?

I did a trace and the only method that is called is the ChangeSellingQtyMaster method.
I did a pop up on it in Pre-processing and post-processing. Looks like I need to use the Post processing to get the value they input.
Now I just need to figure out how to bring in the PartBin field OhHandQty so I can compare it to selling Qty.

Any ideas?

Do you need to prevent the line from being saved? Or just popup the info warning?

I’d do a post process on update.

For the QOH (i assume a single plant and warehouse) use the PartWhse table

One of the conditions in a BPM is lines in a designed query. You can join the part number to the partwhse table which has a qty oh in it. Do a condition where the join has the proposed quantity is greater than the qty on hand, then check for when the number of rows is more than 0. Let me know if you need screen shots, I can get them tomorrow. This will work if you are pulling out of one warehouse.

@Banderson

Unfortunately, we use multiple warehouses. I am told that I can check against a specific warehouse though. Not too sure how to set that up.

Any ideas?

If you don’t filter your query by warehouse you can check whether the total order is more than the quantity in a specific warehouse. But if you want to add your total On Hand quantity you will have to do the summing of that outside of that query since you don’t have all of the normal SQL tools available in that one. I’d have to play around a bit to try and figure out how that would work, but it’s definitely possible.

What do you have so far?

I set up a condition "Number of rows in the (Query) query is more than 0.
In the query i set up:
ttOrderDtl connected to ERP.PartWhse Company to Compant and PartNum to PartNum.
On ttOrderDtl, I did a table criteria: SellingQuantity >= PartWhse.OnHandQty
On ERP>PartWhse table, I did a table criteria: WarehouseCode = “3”

I have the condition connected to a pop-up message telling me it is working.

It seems to just trigger all the time, even when the on hand is larger than the selling qty.

Any ideas?

(EDIT): I finally got it working thanks to @Banderson. I changed SellingQuantity >= PartWhse.OnHandQty to SellingQuantity > PartWhse.OnHandQty. That seems to have fixed it.

can you give me a screen shot of your BPM?




In your test order, do you have more than one line on the order?

in your popup, do a table return to see what’s in ttOderDtl. Do you know how to do that?

Nope, just 1 line to test with.

Not sure, how do you do it?

in your message, right click, table query, pick some fields so you can identify some stuff. This should help you figure out if your criteria are working correctly

image

image

It looks like the query creates 1 line.

When would it not create a line or create more than one line?

Not sure I understand what the query is supposed to be doing.

the query is just checking for records. Based on your joins. Basically like the game “Guess who” (remember that game?) I want all orders that aren’t x or y and have z. That leaves you with a list from 0-x lines. The condition is just a true or false if you are higher (or lower) than the number of lines you specify.

I set it up the same way you set it up and it’s working for me. I set it up on SalesOrder.Update on per-processing. It seems odd though that it wouldn’t work on ChangeSellingQty.

Can you show me the part tracker screen of the part you are trying to check against? And the order line screen you are saving? And the rows that are coming back in your message?