金蝶K3--生产领料单--更新销售订单编号

if object_id(N'tempdb.dbo.#my_select_log') is not null begin drop table #my_select_log end
go	--更新生产领料单行上的销售订单编号和销售订单行号
declare @fentryselfb0450 nvarchar(100),@fentryselfb0451 nvarchar(100)	--定义销售订单编号和销售订单行号
create table #my_select_log (id int identity(1000,1) not null,fcontent nvarchar(max),fresult nvarchar(100),fmodifytime datetime)
declare @finterid int,@fentryid int,@ficmointerid int,@forderinterid int,@fpporderinterid int,@fsourceentryid int
declare my_select cursor for select b.finterid,b.fentryid,ficmointerid from icstockbill a,icstockbillentry b where a.finterid=b.finterid and a.ftrantype=24 and a.frob=1 and isnull(ficmointerid,0)>0 order by a.finterid desc
open my_select
fetch next from my_select into @finterid,@fentryid,@ficmointerid
while @@fetch_status=0
begin
	select @forderinterid=forderinterid,@fpporderinterid=fpporderinterid,@fsourceentryid=fsourceentryid from icmo where finterid=@ficmointerid
	--print '@forderinterid='+cast(@forderinterid as nvarchar)+'|@fpporderinterid='+cast(@fpporderinterid as nvarchar)+'|@fsourceentryid='+cast(@fsourceentryid as nvarchar)
	if isnull(@forderinterid,0)>0	--销售订单
	begin
		select @fentryselfb0450=fbillno from seorder where finterid=@forderinterid
	end
	if isnull(@fpporderinterid,0)>0	--产品预测单
	begin
		select @fentryselfb0450=fbillno from pporder where finterid=@fpporderinterid
	end
	set @fentryselfb0451=cast(@fsourceentryid as nvarchar)
	if isnull(@fentryselfb0450,'x')<>'x' and isnull(@fentryselfb0451,'x')<>'x'
	begin
		insert into #my_select_log (fcontent,fresult,fmodifytime)values('销售订单编号='+@fentryselfb0450+'|销售订单行号='+@fentryselfb0451,'执行成功',convert(nvarchar(100),getdate(),21))
		update icstockbillentry set fentryselfb0450=@fentryselfb0450,fentryselfb0451=@fentryselfb0451 where finterid=@finterid and fentryid=@fentryid
	end
	else
	begin
		insert into #my_select_log (fcontent,fresult,fmodifytime)values('@forderinterid='+cast(@forderinterid as nvarchar)+'|@fpporderinterid='+cast(@fpporderinterid as nvarchar)+'|@fsourceentryid='+cast(@fsourceentryid as nvarchar),'执行失败',convert(nvarchar(100),getdate(),21))
	end
	fetch next from my_select into @finterid,@fentryid,@ficmointerid
end
close my_select
deallocate my_select
select * from #my_select_log where charindex('执行失败',fresult)>0
select * from #my_select_log
drop table #my_select_log
update a set fentryselfb0450=b.fentryselfb0450,fentryselfb0451=b.fentryselfb0451 from icstockbillentry a,icstockbillentry b where a.fsourcetrantype=24 and a.fsourceinterid=b.finterid and a.fsourceentryid=b.fentryid

 

转载于:https://my.oschina.net/ansenchina/blog/3029996