-- if exists (select 1 from sysobjects where id = object_id('Get_StrArrayLength' )) -- drop Function Get_StrArrayLength -- go -- create function Get_StrArrayLength -- ( -- @str varchar(5000 ), -- @split varchar(10 ) -- ) -- returns int -- as -- begin -- declare @location int -- declare @start int -- declare @length int -- -- SET @str = ltrim(rtrim (@str)) -- SET @location = charindex(@split , @str ) -- SET @length = 1 -- -- while @location <>0 -- begin -- SET @start = @location + 1 -- SET @location = charindex(@split , @str , @start ) -- SET @length = @length + 1 -- end -- -- return @length -- end -- GO -- create function [dbo].[f_split](@SourceSql varchar(8000),@StrSeprate varchar(10),@indexof int) -- returns nvarchar(50) -- as -- begin -- declare @i as int -- declare @temp_str as nvarchar(50) -- set @SourceSql=rtrim(ltrim(@SourceSql)) -- -- while @indexof > 0 -- begin -- set @indexof = @indexof - 1 -- set @i=charindex(@StrSeprate,@SourceSql) -- if @i <> 0 -- begin -- set @temp_str = (left(@SourceSql,@i-1)) -- set @SourceSql = right(@SourceSql,len(@SourceSql)-len(@temp_str)-len(@StrSeprate)) -- end -- -- if @i = 0 -- begin -- if @temp_str <> '' -- begin -- --取最后一个作为返回值 -- set @temp_str = @SourceSql -- break -- end -- end -- -- end -- return @temp_str -- end ALTER proc log_spthd @bh varchar(8000),@ck varchar(20) as begin declare @len int,@len2 int,@Djbh varchar(20) set @len=1; --select dbo.Get_StrArrayLength('93143407002',',') --select dbo.f_split('93143407002',',',0) set @len2=dbo.Get_StrArrayLength(@bh,','); select @djbh='JA6'+right('000000'+cast(right(max(djbh),6)+1 as varchar(10)),6) from xtrzb where djmc='SPTHD' set nocount on create table #Temp_spdm(spdm varchar(50)); if @len2=1 begin insert into #Temp_spdm(spdm) values(@bh); end else begin while (@len<=@len2) begin insert into #Temp_spdm(spdm) values(dbo.f_split(@bh,',',@len)); set @len=@len+1; end end ----------根据退仓编号获取SPKCB完整退仓明细信息----------------------------------------------------------- select t.spdm,f.商品名称,s.ckdm,s.gg1dm,s.gg2dm,f.供应商编号,f.供货商名称,sum(s.sl) 数量 into #Temp from #Temp_spdm t left join spkcb s on t.spdm=s.spdm left join fr_vw_shangpin f on f.商品编号=t.spdm where s.ckdm=@ck and s.sl>0 group by t.spdm,f.商品名称,s.gg1dm,s.gg2dm,f.供应商编号,f.供货商名称,s.ckdm ---------------------------------------------------------------------------------------------------------- create table #Gcour(row int,ghsdm varchar(50),djh varchar(10)); insert into #Gcour(row,ghsdm,djh) select row_number() over (order by ghsdm) as row,m.ghsdm,'' djh from (select distinct 供应商编号 ghsdm from #temp) m; ---------------------------------------------------------------------------------------------------------- declare g1_cursor cursor for select row,ghsdm,djh from #Gcour where djh=''; open g1_cursor declare @row int, @ghsdm varchar(20),@djh varchar(20) fetch next from g1_cursor into @row,@ghsdm,@djh while @@fetch_status=0 begin begin update #Gcour set djh=@djbh where current of g1_cursor end begin insert into xtrzb(guid,ip,jq,dt,czdx,djmc,djbh,ydjh,cz,zymc,module,execmodule) values('','','PC-20180527CWQX',getdate(),'1',left('SPTHD',50),left(@djbh,20),left('',120),'INSERT','系统管理员',0,'分销') end set @djbh='JA6'+right('000000'+cast(right(@djbh,6)+1 as varchar(10)),6) fetch next from g1_cursor into @row,@ghsdm,@djh end close g1_cursor deallocate g1_cursor if object_id(N'BSERP3test..#Temp_cptc',N'U') is not null drop table #Temp_cptc select g.djh,t.spdm,t.商品名称 spmc,t.ckdm,t.gg1dm,t.gg2dm,t.供应商编号 ghsdm,t.供货商名称 ghsmc,f.采购进价 标准价, sum(t.数量*f.采购进价) 标准金额,sum(t.数量) 数量 into #Temp_cptc from #Temp t left join #Gcour g on t.供应商编号=g.ghsdm left join fr_vw_shangpin f on f.商品编号=t.spdm group by t.spdm,t.商品名称,t.ckdm,t.gg1dm,t.gg2dm,t.供应商编号,t.供货商名称,g.djh,f.采购进价 -- select djh,ckdm,sum(数量) SL,sum(标准价) je,sum(标准金额) bzje -- from #temp_cptc -- group by djh,ckdm ----------SPTHD------------------------------------------------------------------------------------------------ declare g2_cursor cursor for select djh,ckdm,ghsdm,sum(数量) SL,sum(标准价) je,sum(标准金额) bzje from #temp_cptc group by djh,ckdm,ghsdm open g2_cursor declare @dj varchar(20),@ckdm varchar(20),@dm varchar(50),@sl numeric(18,4),@je numeric(18,4),@bzje numeric(18,4),@rq datetime fetch next from g2_cursor into @dj,@ckdm,@dm,@sl,@je,@bzje while @@fetch_status=0 begin set @rq=getdate() exec sp_executesql N'INSERT INTO "BSERP3test".."SPTHD" ("DJBH","RQ","QYDM","DM1","QDDM","DM2","DM2_1","BYZD1","BYZD12","YGDM","DM4","FPLX","BYZD18","BYZD7","DJXZ","LL","SL","JE", "BZJE","BYZD2","BYZD10","ZDR","RQ_4","SP","SH","JZ","ZS","FP","LLR","TJ","XC","YS","JS","DM5") VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21, @P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34)', N'@P1 varchar(9),@P2 datetime,@P3 varchar(3),@P4 varchar(7),@P5 varchar(3),@P6 varchar(3),@P7 varchar(3),@P8 varchar(1), @P9 numeric(18,4),@P10 varchar(3),@P11 varchar(3),@P12 varchar(1),@P13 varchar(1),@P14 varchar(1),@P15 varchar(1), @P16 varchar(1),@P17 numeric(18,4),@P18 numeric(18,4),@P19 numeric(18,4),@P20 varchar(1),@P21 numeric(18,4),@P22 varchar(10), @P23 datetime,@P24 varchar(1),@P25 varchar(1),@P26 varchar(1),@P27 varchar(1),@P28 varchar(1),@P29 varchar(1) ,@P30 varchar(1),@P31 varchar(1),@P32 varchar(1),@P33 varchar(1),@P34 varchar(1)', @dj,@rq,'000',@dm,'000',@ckdm,'000','0',1.0000,'000','002','3','','','0','1', @sl,@je,@bzje,'0',0,'系统管理员',@rq,'0','0','0','0','0','0','0','0','0','0','' fetch next from g2_cursor into @dj,@ckdm,@dm,@sl,@je,@bzje end close g2_cursor deallocate g2_cursor ----------SPTHDMX------------------------------------------------------------------------------------------------ declare g3_cursor cursor for select djh,spdm,gg1dm,gg2dm,标准价 bzj,标准金额 bzje,数量 sl from #temp_cptc open g3_cursor declare @dj2 varchar(20),@spbh varchar(20),@gg1dm varchar(20),@gg2dm varchar(20),@bzj numeric(18,4),@bze numeric(18,4), @mxsl numeric(18,4) fetch next from g3_cursor into @dj2,@spbh,@gg1dm,@gg2dm,@bzj,@bze,@mxsl while @@fetch_status=0 begin exec sp_executesql N'SET NOCOUNT OFF;INSERT INTO "BSERP3test".."SPTHDMX" ("DJBH","MIBH","HH","SPDM","GG1DM","GG2DM","SL_3","SL","SL_1","SL_2" ,"CKJ","ZK","DJ","DJ_1","DJ_2","DJ_3","JE","BYZD1","BZ","PCDM") VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20); --SELECT SCOPE_IDENTITY() AS SCOPE_ID_COLUMN' ,N'@P1 varchar(9),@P2 int,@P3 numeric(18),@P4 varchar(11),@P5 varchar(2), @P6 varchar(3),@P7 numeric(18,4),@P8 numeric(18,4),@P9 numeric(18,4),@P10 numeric(18,4),@P11 numeric(18,4),@P12 numeric(18,4), @P13 numeric(18,4),@P14 numeric(18,4),@P15 numeric(18,4),@P16 numeric(18,4),@P17 numeric(18,4),@P18 varchar(1),@P19 varchar(8), @P20 varchar(1)', @dj2,1,1,@spbh,@gg1dm,@gg2dm,0,@mxsl,0,0,@bzj,1.0000,@bzj,@bzj,1.0000,@bzj,@bze,'0','','' fetch next from g3_cursor into @dj2,@spbh,@gg1dm,@gg2dm,@bzj,@bze,@mxsl end close g3_cursor deallocate g3_cursor drop table #Temp drop table #Temp_spdm drop table #Gcour drop table #Temp_cptc select '执行成功' end go --exec log_spthd '94242350002,94332136001','W02' |