27,582
社区成员




SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Create procedure proc_stock_in_modify_stocknum(@input_id varchar(20))--审核时实时修改库存日和月数量
as
begin transaction
declare @ware_id varchar(10),@site_id varchar(10),@item_id varchar(40),@deli_type char(1)
declare @meas_unit varchar(4),@df_no varchar(20),@input_type char(1),@move_id varchar(20)
declare @out_id varchar(20),@real_quan Decimal(12,2),@bad_quan Decimal(12,2),@month varchar(8)
declare @goods_formno varchar(20),@client_id varchar(12),@check_date datetime
declare @last_date datetime,@end_quan decimal(12,3),@last_month varchar(8)
--定义游标
declare cur_input cursor for
select a.in_ware_id,a.input_type,a.df_no,b.site_id,b.item_id,b.real_quan,a.move_id,a.input_date
from i_stockin_master a,i_stockin_detail b
where a.input_id = b.input_id and a.input_id = @input_id
open cur_input
fetch cur_input into @ware_id,@input_type,@df_no,@site_id,@item_id,@real_quan,@move_id,@check_date
while @@fetch_status = 0
begin
--改变库存
if exists (select * from i_item_stock
where ware_id = @ware_id and site_id = @site_id and item_id = @item_id )
update i_item_stock set quantity = isnull(quantity,0) + isnull(@real_quan,0)
where ware_id = @ware_id and site_id = @site_id and item_id = @item_id
else
begin
select @meas_unit = meas_unit from material where item_id = @item_id
insert into i_item_stock(ware_id,site_id,item_id,meas_unit,quantity)
values(@ware_id,@site_id,@item_id,@meas_unit,isnull(@real_quan,0))
end
fetch cur_input into @ware_id,@input_type,@df_no,@site_id,@item_id,@real_quan,@move_id,@check_date
end
close cur_input
deallocate cur_input
update i_stockin_master set state = 'C' where input_id = @input_id
select 1
commit transaction
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO