Hi Roger,

Roger Pullen wrote:

> I have a stock control program where I need an SP
> to move items from one location to another...
> Stock for product in any one location is made
> up from several transaction entries thus:
>
> LOCATION DIVISION PROD_CODE UNIT_QTY
> ------------------------------------
> 5841 30832 18006 10
> 5841 30833 18006 10
> 5841 30807 18006 10
> 5841 30817 18006 10
>
>
> I pass the SP parameters thus:
>
> existing location (5841 in this case)
> new location - say 5500
> product code (18006)
> qty to move - say 25
>
>
> Conceptually I need to "loop" thru the
> above result set moving the first 2 entries
> by changing the location to 5500 (ie 20 total)
> and splitting the third line into a balance
> of 5 left and a new entry with the other 5 in
> a new location - total 25 moved
>
> I have been on this all day and feel am nearly
> there but would appreciate a second opinion
> as to the best approach

assuming your stock table is called tstock, one way would be:

create procedure spMoveStock
(old_loc integer,
new_loc integer,
prod_code integer,
qty_to_move integer)
as
declare variable cur_qty integer;
declare variable cur_division integer;
begin
for select division, unit_qty
from tstock
where LOCATION = :old_loc
and PROD_CODE = :prod_code
order by DIVISION
into :cur_division, :cur_qty do
begin
if cur_qty <= cur_rest then /* move it all */
begin
update tstock
set LOCATION = :new_loc
where LOCATION = :old_loc
and DIVISION = :cur_division
and PROD_CODE = :prod_code;

qty_to_move = qty_to_move - cur_qty; /* decrement the rest */

if qty_to_move=0 then /* fits exactly: we're done, get outta here */
exit;
end
else /* split it up */
begin
update tstock
set LOCATION = :new_loc,
UNIT_QTY = :qty_to_move
where LOCATION = :old_loc
and DIVISION = :cur_division
and PROD_CODE = :prod_code;

/* and a new one for the rest that wasn't moved */

insert into tstock
(LOCATION, DIVISION, PROD_CODE, UNIT_QTY)
values
(:old_loc, :cur_division, :prod_code, :cur_qty-:qty_to_move);

exit; /* we're there */
end /* else */
end /* for .. loop */

exit;
end
^
something like this? i'm curious, what was your approach?

hth,
fingerman

--
-------------------------------------------------------------------------
when parsers parse, and compilers compile, then why don't objects object?

fingerbirdy - fingerman's door to Firebird
http://www.fingerbird.de