Carlos,
The way I always do this is to have a thing called "Stock Status". As soon as an order detail is created, the stock-in-hand is checked and, if there is sufficient, that stock goes into "reserved" status, meaning that it is unavailable to any other transaction. If the order transaction rolls back, or the order detail line is cancelled, the stock is released; if it commits, that transaction gets it and it becomes allocated to that detail line.

This means that, in a multi-user distribution system, stock-on-hand is calculated each time any query is done on stock quantity, as stock in store less allocated less reserved (less any outstanding unfulfilled backorders).

Invoicing time is too late to reserve stock.

fwiw,
Helen


At 09:29 AM 09-03-01 -0300, you wrote:

>Hi everybody !
>
>I would like to ask what would be your approach on the following situation
>(a very common scene in Brazil) :
>
>The situation : Many people working at the same time creating and printing
>comercial invoices. Here is very common to have an invoice payment divided
>in 3 or more parcels, each one with a diferent expiry date.
>
>As you can guess, the invoice total (and other fields) are calculated based on the invoice
>items (items = detail table), so you only have the invoice total when all
>the items were entered. Each item, when the transaction is commited,
>fires a trigger that will (among other things) update the stock of that
>item.
>
>The parcels are stored in separated table and actually the inserts are done
>automaticly (stored procedure called from an after_insert trigger) when the
>order is posted.
>
>I can put everything inside a unique transaction, but it is not a good
>thing to have all the invoice lost if, at the commit time, IB reports a
>deadlock (maybe some other guy did another invoice using the same item and
>so updating the stock = conflict).
>
>There is much more complexity than I'm exposing here (actually some
>triggers also updates other tables that deal with quarrels, etc...)but at
>the moment I would like to hear your comments on how to make the above
>process in a way to minimize deadlocks.
> pendencia
>[]s
>
> Carlos
> WarmBoot Informatica - http://www.warmboot.com.br
> Interbase-BR - http://www.interbase-br.com
>
>
>
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________