Hi...

I've created store procedure like this:

create procedure sp_qty_transaction
as
declare variable v_sku_no varchar(10);
declare variable v_size_code varchar(10);
declare variable v_qty decimal(12,2);
declare variable v_sum_qty decimal(12,2);
declare variable v_t_amt decimal(18,2);
begin
for select a.sku_no,a.size_code,a.qty
from product_location a, tm_location b
where a.loc_code = b.loc_code
and a.loc_type = 'D'
into :v_sku_no, :v_size_code, :v_qty
do
begin
select sum(t_qty)
from pos_transaction_dtl
where t_sku_no = :v_sku_no
and t_size_code = :v_size_code
into :v_sum_qty;

if (v_sum_qty is null) then v_sum_qty = 0;

if(v_qty is null) then v_qty = 0;

if (v_sum_qty > 0) then
begin
update product_location set qty = :v_qty - :v_sum_qty
where sku_no = :v_sku_no
and size_code = :v_size_code
and loc_type = 'D';
end
end
when any do exception failed;

end

The sp works fine, but i didn't see any changes on product_location table. the quantity on product_location should be updated.
Is my sp wrong? anybody could help me?

Thanks in advance!

Regina Phandu



[Non-text portions of this message have been removed]