Try this, I hope it will help:
/*
Assumption 1:
PutawayPriority is unique for each Item (no two bins for an item can have the same priority)
If this is not true, the some another criteria should be used to UNIQUELLY order bins,
like concatenating the BinId to the priority (converted to varchar).
Assumption 2:
The procedure does not handle the case when the quantity to put away
is bigger than the available space in all bins. It should be checked at the beginning,
and maybe the @QtyToPutAway should be OUTPUT parameter, returning the remaining quantity that
is not possible to put away.
The basic idea is:
1. Find all bins that should be updated at all (this is the WHERE part of the UPDATE statement).
You can put away items in bins until all items are used. Due to the priority,
the minimum priority is found, so all bins with that priority OR HIGHER will consume all
items that should be put away.
2. Update all quantities on hand in all bins having the priority NOT LOWER than the one found
in step 1 as follows:
- if the quantity that is left after filling all bins with higher priority is bigger than
the available space, than the bin is filled up to its maximum quantity.
- otherwise (only for the last bin, and only if any items are left until filling
all other bins ut to their maximum) increase the quantity with the rest of the items.
Some useful indexes may be created on (ItemId, Priority) or (ItemId, Proirity, BinId)
if the table is large, due to the compexity of the SQL.
Also, appropriate locking may be added (like SELECT-ing all records for the item WITH (UPDLOCK).
*/
create procedure usp_PutawayQuantity (@Item varchar(80), @QtyToPutAway numeric(19,2))
as
set nocount on
update
ItemBin
set
QtyOnHand =
case
when QtyMax - QtyOnHand < @QtyToPutAway -
(select isnull(sum(QtyMax - QtyOnHand), 0)
from ItemBin IB2
where (Item = @Item) and (IB2.PutawayPriority < ItemBin.PutawayPriority))
then QtyMax
else
QtyOnHand + @QtyToPutAway -
(select isnull(sum(QtyMax - QtyOnHand), 0)
from ItemBin IB2
where (Item = @Item) and (IB2.PutawayPriority < ItemBin.PutawayPriority))
end
where
(Item = @Item) and
PutawayPriority <=
(select
min(PutawayPriority)
from
ItemBin IB1
where
@QtyToPutAway <=
(select sum(QtyMax - QtyOnHand)
from ItemBin IB2
where (Item = @Item) and (IB2.PutawayPriority <= IB1.PutawayPriority)
)
)
go
Edited by - g_smilevski on 06/06/2003 05:55:55 AM