PartAlloc QTY Transitions

I am trying to create an In-Tran Data Directive that will put a hard stop on users over Fulfilling orders. I’m trying to prevent an over shipment further upstream (we had several and it’s an issue with firearms). I created a BAQ that is being called VIA Custom Code (DD/In-Tran/SYNC/Terminate on Error) that basically provides summed totals of what was already shipped, currently “Reserved/Allocated/Picking/Packed” and what the Order/Line requires. My issue appears to be the way Epicor Updates the PartAlloc table. In short, I am summing up what they are doing in the current transaction and adding that to what was already shipped as well as what is in (Reserved/Allocated/Picking/Picked) and comparing quantities to Order Line total. If the quantity exceeds the line total it stops the transaction.
For starters if the current transaction is an Update I stop it if there is more than 1 row in PartAlloc for the order/line( a saw a few in there). If the transaction if Adding a Row I just sum Shipped/PartAlloc/ttPartAlloc. One of the questions I have is if there is an existing row for same order/line/rel shouldn’t any new PartAlloc against that be an Update? In other words I shouldn’t see 100/1/1 Reserved:20 and then be able to use Fulfilment Workbench to Create another reservation for 15 (totaling 35 between 2 PartAlloc Records)? It should update the 20 to 15 and not create a new row? I ask because I see 4 duplicate records in there. This leads to the next question. If we can/should only have 1 row per Order/Line/Rel then what is the Process the PartAlloc update follows? I assumed you would normally start with Reserved ->Allocated->Picking->Picked->Packed (PartAlloc records gets deleted when you set ReadyToInvoice in the Shipment). If that is the case can you have a given PartAlloc record that has quantities in more than one stage? 20 In Reserved and 20 in Picking same row? What I have seen is the ttPartAlloc appears to “move” the quantities during an update by writing 0 in the old state and updating the new state with quantity. If this is the case (for updates) where only 1 row will exists and only 1 qty per “state” then I should be able to just ignore what is currently in the database and just assume that the quantity in the ttPartAlloc will “win”? My false positives maybe from over complicated IFF statements to compensate/anticipate transaction that are not possible.

@AlexanderDelarge Can we take a step back and talk about what you are wanting to stop and where?

Order release has OurJobShippedQty, OurStockShippedQty and Required Quantity
Customer Shipment has SellingShippedQty, SellingShipmentQty and SellingReqQty if you are shipping from stock which it sounds like you are.

These are updated by Epicor and can be used to stop addling quantities to shipments or material queues.

We are using Fulfilment workbench and PackOut as well as straight shipments from inventory that did not go through Fulfillment or PackOut. Shipping department are the ones who use the fulfillment workbench not sales. Most items shipped are serialized and add to the complication. At the end of the day we do not want to over ship any order. This has happened while using both the fulfilment ->packout and straight up shipments. I added some hard stop when the users tried to add parts to the shipment line but they complained it was “too late” at that point. Not because they shipped it but rather the fact they now need to go “unpack” unallocated the order in fulfilment workbench and select the correct number of Serialized parts to put though the process again. I was trying to stop them at the fulfilment stage. So if the Order/Line was for 10 and they shipped 5, they would get an error if they tried to Reserve/Allocate/Picking/Picked 6 or more. They basically what instant feedback if they are attempting to provide an order line more then what was asked for by sales.

@AlexanderDelarge Did not know that the fwb would over allocate. We are all custom jobs so what little we used the fwb was too confusing so I built a pull from finished goods dashboard to create the material queues they needed.

I would still do the too late stop and work backwards. Is there a material queue record made that gets processed before the pack? Or when they are picking the serial numbers check the number already processed for that release.