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