用友U8按BOM计算销售订单物料需求SQL代码 第一稿
drop table #tmp1999 drop table #tmp2999 drop table #tmp3999 drop table #tmp4999 drop table #tmp5999 drop table #tmp6999 drop table #tmp7999 drop table #tmp8999 drop table #tmp9999 drop table #tmp1 create table #tmp1999 ( [autoid] [int] IDENTITY(1,1) NOT NULL, [opcomponentid] [nvarchar](100) NULL, [bomid] [nvarchar](100) NULL, [sortseq] [nvarchar](20) NULL, [componentid] [nvarchar](100) NULL, [baseqtyn] [decimal](10, 4) NULL, [baseQtyD] [decimal](10, 4) NULL ) delete #tmp1999 declare @id int set @id=(select min(autoid) from SO_SODetails /*where cInvCode='1C00601009'*/ ) ---声明一个变量,销售明细里面最小的ID号赋值给变量 while(@id)<=(select max(autoid) from SO_SODetails /*where cInvCode='1C00601009'*/) ---当@id<=销售订单明细表最大ID时,循环 begin insert into #tmp1999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)( select opcomponentid , bomid, sortseq , componentid, (baseqtYn*(select iQuantity from SO_SODetails where autoid=@id )) as baseqitn, baseQtyD from bom_opcomponent ----查询BOM子表并插入到#Tmp1999中 where BomId =( select MAX( BomId) as bomid from bom_parent where ParentId = ( select partid from bas_part where InvCode = ( select cinvcode from SO_SODetails where AutoID=@id)))) set @id=@id+1 end ----根据#tmp1999创建一个数据结构相同的表 #tmp2999 select * into #tmp2999 from #tmp1999 where 1=0 /*** 第二次循环,查找tmp#1999中的子件 ***/ delete #tmp2999 set @id=(select min(autoid)from #tmp1999) while @id<=(select max(autoid)from #tmp1999 ) begin insert into #tmp2999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)( select opcomponentid , bomid, sortseq , componentid, (baseqtYn*(select baseqtyn from #tmp1999 where autoid=@id )) as baseqitn , baseQtyD from bom_opcomponent ----查询BOM子表并插入到Tmp2999中 where BomId =(select MAX( BomId) as bomid from bom_parent where ParentId =(select ComponentId from #tmp1999 where AutoId=@id ) )) set @id=@id+1 end select * into #tmp3999 from #tmp1999 where 1=0 /*** 第三次循环,查找tmp#2999中的子件 ***/ delete #tmp3999 set @id=(select min(autoid)from #tmp2999) while @id<=(select max(autoid)from #tmp2999 ) begin insert into #tmp3999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)( select opcomponentid , bomid, sortseq , componentid, (baseqtYn*(select baseqtyn from #tmp2999 where autoid=@id )) as baseqitn , baseQtyD from bom_opcomponent ----查询BOM子表并插入到Tmp3999中 where BomId =(select MAX( BomId) as bomid from bom_parent where ParentId =(select ComponentId from #tmp2999 where AutoId=@id ) )) set @id=@id+1 end select * into #tmp4999 from #tmp1999 where 1=0 /*** 第四次循环,查找tmp#3999中的子件 ***/ delete #tmp4999 set @id=(select min(autoid)from #tmp3999) while @id<=(select max(autoid)from #tmp3999 ) begin insert into #tmp4999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)( select opcomponentid , bomid, sortseq , componentid, (baseqtYn*(select baseqtyn from #tmp3999 where autoid=@id )) as baseqitn , baseQtyD from bom_opcomponent ----查询BOM子表并插入到Tmp4999中 where BomId =(select MAX( BomId) as bomid from bom_parent where ParentId =(select ComponentId from #tmp3999 where AutoId=@id ) )) set @id=@id+1 end select * into #tmp5999 from #tmp1999 where 1=0 /*** 第四次循环,查找tmp#4999中的子件 ***/ delete #tmp5999 set @id=(select min(autoid)from #tmp4999) while @id<=(select max(autoid)from #tmp4999 ) begin insert into #tmp5999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)( select opcomponentid , bomid, sortseq , componentid, (baseqtYn*(select baseqtyn from #tmp4999 where autoid=@id )) as baseqitn , baseQtyD from bom_opcomponent ----查询BOM子表并插入到Tmp5999中 where BomId =(select MAX( BomId) as bomid from bom_parent where ParentId =(select ComponentId from #tmp4999 where AutoId=@id ) )) set @id=@id+1 end select * into #tmp6999 from #tmp1999 where 1=0 /*** 第六次循环,查找tmp#5999中的子件 ***/ delete #tmp6999 set @id=(select min(autoid)from #tmp5999) while @id<=(select max(autoid)from #tmp5999 ) begin insert into #tmp6999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)( select opcomponentid , bomid, sortseq , componentid, (baseqtYn*(select baseqtyn from #tmp5999 where autoid=@id )) as baseqitn , baseQtyD from bom_opcomponent ----查询BOM子表并插入到Tmp5999中 where BomId =(select MAX( BomId) as bomid from bom_parent where ParentId =(select ComponentId from #tmp5999 where AutoId=@id ) )) set @id=@id+1 end select * into #tmp7999 from #tmp1999 where 1=0 /*** 第七次循环,查找tmp#6999中的子件 ***/ delete #tmp7999 set @id=(select min(autoid)from #tmp6999) while @id<=(select max(autoid)from #tmp6999 ) begin insert into #tmp7999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)( select opcomponentid , bomid, sortseq , componentid, (baseqtYn*(select baseqtyn from #tmp6999 where autoid=@id )) as baseqitn , baseQtyD from bom_opcomponent ----查询BOM子表并插入到Tmp7999中 where BomId =(select MAX( BomId) as bomid from bom_parent where ParentId =(select ComponentId from #tmp6999 where AutoId=@id ) )) set @id=@id+1 end select * into #tmp8999 from #tmp1999 where 1=0 /*** 第八次循环,查找tmp#7999中的子件 ***/ delete #tmp8999 set @id=(select min(autoid)from #tmp7999) while @id<=(select max(autoid)from #tmp7999 ) begin insert into #tmp8999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)( select opcomponentid , bomid, sortseq , componentid, (baseqtYn*(select baseqtyn from #tmp7999 where autoid=@id )) as baseqitn , baseQtyD from bom_opcomponent ----查询BOM子表并插入到Tmp7999中 where BomId =(select MAX( BomId) as bomid from bom_parent where ParentId =(select ComponentId from #tmp7999 where AutoId=@id ) )) set @id=@id+1 end select * into #tmp9999 from #tmp1999 where 1=0 /*** 第9次循环,查找tmp#8999中的子件 ***/ delete #tmp9999 set @id=(select min(autoid)from #tmp8999) while @id<=(select max(autoid)from #tmp8999 ) begin insert into #tmp9999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)( select opcomponentid , bomid, sortseq , componentid, (baseqtYn*(select baseqtyn from #tmp8999 where autoid=@id )) as baseqitn , baseQtyD from bom_opcomponent ----查询BOM子表并插入到Tmp7999中 where BomId =(select MAX( BomId) as bomid from bom_parent where ParentId =(select ComponentId from #tmp8999 where AutoId=@id ) )) set @id=@id+1 end select * into #tmp1 from #tmp1999 where 1=0 set @id=(select count(autoid) from #tmp9999 ) if @id=0 begin delete #tmp1 insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD) (select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp1999) insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD) (select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp2999) insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD) (select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp3999) insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD) (select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp4999) insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD) (select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp5999) insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD) (select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp6999) insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD) (select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp7999) insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD) (select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp8999) insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD) (select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp9999) end /*** select * from #tmp1 where componentid='35547' select * from bas_part where invcode='3020900005' partid='33857' ***/