【用友U8BOM全阶展开SQL】存储过程批量展开BOM结构，由子阶向上算母件的成本。

在百度查询U8BOM结构批量导出时，发现都要收费，于是自己写了个存储过程，顺便分享给需要的朋友。

USE [UFDATA_110] 
--使用你的账套数据库
GO
/****** Object:  StoredProcedure [dbo].[BOMJS]    Script Date: 2022-11-02 23:28:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[BOMJS] 
--定义变量会计年，月
 @nian int,
 @yue int 

 as 
 --使用游标取出最近审核的BOM ID号并存入临时表 #BB；也可以使用 max获取最大时间，但不知道为什么会多出重复的BOMID？？

---获取bom_bom 表存入临时表，把BOM_BOM的修改时间赋值为创建时间
select * into #bom_bom from  bom_bom
update #bom_bom set ModifyTime=CreateDate from #bom_bom where ModifyTime is null

declare @zz int 

--创建游标
declare youbiao cursor for
        SELECT DISTINCT PARENTID FROM bom_parent GROUP BY PARENTID  --select * from bom_parent  where parentid='2134' order by parentid
open youbiao
fetch next from youbiao into @zz
--SELECT TOP 1 AutoId,BomId,ParentId,ParentScrap,SharingPartId into #bb FROM bom_parent  WHERE ParentId=@zz ORDER BY UFTS DESC
 SELECT TOP 1 AutoId,BomId,ParentId,ParentScrap,SharingPartId into #bb  FROM bom_parent  WHERE BomId in(
 select bomid from #bom_bom where bomid in( select BOMid  from bom_parent  where parentid=@zz) AND ModifyTime in ( select max(ModifyTime)  from #bom_bom where bomid in( select BOMid  from bom_parent  where parentid=@zz ) )
 )
while @@fetch_status=0
begin 
  fetch next from youbiao into @zz
  if @@fetch_status=0
   begin
      insert #bb(AutoId,BomId,ParentId,ParentScrap,SharingPartId) 
       SELECT TOP 1 AutoId,BomId,ParentId,ParentScrap,SharingPartId   FROM bom_parent  WHERE BomId in(
 select bomid from #bom_bom where bomid in( select BOMid  from bom_parent  where parentid=@zz) AND ModifyTime in ( select max(ModifyTime)  from #bom_bom where bomid in( select BOMid  from bom_parent  where parentid=@zz ) )
 )
   end
  end
  close youbiao
  deallocate youbiao 

/*--
declare @zz int 
declare youbiao cursor for
        SELECT DISTINCT PARENTID FROM bom_parent GROUP BY PARENTID
open youbiao
fetch next from youbiao into @zz
SELECT TOP 1 AutoId,BomId,ParentId,ParentScrap,SharingPartId into #bb FROM bom_parent  WHERE ParentId=@zz ORDER BY UFTS DESC
while @@fetch_status=0
begin 
  fetch next from youbiao into @zz
  if @@fetch_status=0
   begin
      insert #bb(AutoId,BomId,ParentId,ParentScrap,SharingPartId) select top 1 AutoId,BomId,ParentId,ParentScrap,SharingPartId from bom_parent  WHERE ParentId=@zz ORDER BY UFTS DESC
   end
  end
  close youbiao
  deallocate youbiao 

*/
  ---展开BOM子件信息并存入临时表 #BBTEMP
select  t1.bomid,t2.ParentId,t1.ComponentId,t1.BaseQtyN 分子, t1.BaseQtyD 分母 into #BBTEMP from bom_opcomponent T1 left join bom_parent T2 on 
t1.BomId=t2.BomId 

  where t1.bomid in  ( select BomId from  #bb)               
---( select bomid from  (select distinct bomid,max(Ufts) 时间 from bom_bom group by bomid ) tt)  select * from  #BBTEMP

--声明表量，用于标记几阶。
declare @bit int
select @bit=1
--使用CTE函数，展开下阶子件信息，并运算出有母件为单位总量，按单位占比分配下价子件用量
begin
with bb as
(select ParentId 母件ID,bomid,ParentId,ComponentId, @bit 阶,分子,分母,分子*分母 用量  from #BBTEMP -- where bomid=1
union all
select  t2.母件ID,t2.bomid,t2.ComponentId ParentId ,t1.ComponentId,阶=t2.阶+1,t1.分子,t2.分母,t2.用量*t1.分子 用量   from bb t2 inner join #BBTEMP t1 on t2.ComponentId=t1.ParentId  and @bit<10 
)

select * into #bbt1 from bb   --select * from #bbt1  允许CTE函数后，存入临时表#bbt1
end
--，去除重复出现的数据，并存入临时表 #bbt1
select distinct 母件ID,bomid,ParentId,ComponentId,分子,分母,用量 , 阶 into #bbt2  from #bbt1 group by  母件ID,bomid,ParentId,ComponentId,分子,分母,用量,  阶 order by bomid,阶

--标记是否有下阶。
select k1.*,case when k2.ParentId is null then '无' else '有' end 是否有下阶 into #bbt3 from #bbt2 k1 left join 
(select distinct bomid,parentid from #bbt2 group by bomid,parentid) k2  on k1.BomId=k2.BomId  and k1.ComponentId=k2.ParentId 
--从存货档案  Inventory 获取母件和子件的 档案信息 并存入临时表 #bbt4
select zz1.*,zz2.*,zz3.*,zz4.母件代码 母件 into #bbt4 from #bbt3  ZZ1
left join 
(select tt1.PartId 母件PartId,tt1.InvCode 母件代码,tt2.cInvAddCode 母件编码,tt2.cInvName 母件名称 from bas_part TT1 left join Inventory TT2 on tt1.InvCode=tt2.cInvCode) zz2 on zz1.ParentId=zz2.母件PartId
left join 
(select tt1.PartId 子件PartId,tt1.InvCode 子件代码,tt2.cInvAddCode 子件编码,tt2.cInvName 子件名称 from bas_part TT1 left join Inventory TT2 on tt1.InvCode=tt2.cInvCode) zz3 on zz1.ComponentId=zz3.子件PartId
left join
(select tt1.PartId 母件PartId,tt1.InvCode 母件代码,tt2.cInvAddCode 母件编码,tt2.cInvName 母件名称 from bas_part TT1 left join Inventory TT2 on tt1.InvCode=tt2.cInvCode) zz4 on zz1.母件ID=zz4.母件PartId
--select * from #bbt4

--从存货核算表 IA_Summary 获取单价，与用量相乘计算出金额
select jiage1.*,jiage2.单价,case when jiage1.是否有下阶='有' then 0 else jiage1.用量*isnull(jiage2.单价,0) end 金额 into #bb5 from #bbt4 jiage1 left join 
( select distinct cInvCode,case when sum(isnull(iNum,0)) =0 then 0  else sum(isnull(iMoney,0))/ sum(isnull(iNum,0)) end  单价
from IA_Summary 
where iYear=@nian  and imonth=@yue 
and (inum is not null) and inum <>0  
group by cInvCode
) jiage2 on jiage1.子件代码=jiage2.cInvCode

--存货核算表 找不到单价的，从最近采购入库单价获取价格。rdrecords01 
--select * from #bb5 JJ1 where JJ1.是否有下阶='无' and (JJ1.单价 is null or JJ1.单价=0)
UPDATE JJ1 SET JJ1.单价=JJ2.iUnitCost,JJ1.金额=JJ2.iUnitCost*JJ1.用量
--select *
 from #bb5 JJ1 LEFT JOIN
(
select kkk1.cinvcode,kkk2.iUnitCost from (
select distinct cinvcode,max(autoid) autoid from rdrecords01 
group by cinvcode) kkk1 left join rdrecords01 kkk2 on kkk1.autoid=kkk2.autoid) JJ2
ON JJ1.子件代码=JJ2.cinvcode
where JJ1.是否有下阶='无' and (JJ1.单价 is null or JJ1.单价=0)

---存货核算和入库单都找不到单价的，从母件中找单价.
UPDATE JJ1 SET JJ1.单价=JJ2.iUnitPrice,JJ1.金额=JJ2.iUnitPrice*JJ1.用量
--select *
from #bb5 JJ1 left join
(
select kk2.cinvcode,kk2.iUnitPrice from 
(select  distinct cinvcode,max(autoid) autoid from IA_Summary where  iUnitPrice is not null and iUnitPrice<>0 group by cinvcode) kk1
left join IA_Summary kk2
on kk1.autoid=kk2.autoid

 ) JJ2
ON JJ1.子件代码=JJ2.cinvcode
where JJ1.是否有下阶='无'  and (JJ1.单价 is null or JJ1.单价=0)

--select * from #bb5  where 是否有下阶='无' and (单价 is null or 单价=0)

--计算单个母件的总金额，并存第一行号
select *,ROW_NUMBER()over(partition by 母件ID order by 阶) 排号 into #bb6 from #bb5 order by bomid,阶

insert into #bb7 select *, case when 排号=1  then  sum(金额)over(partition by 母件ID) else null end  合计金额  from #bb6 
select * from #bb7 order by bomid,阶  

drop table #bbt1
drop table #BBTEMP
drop table #bbt2
drop table #bb
drop table #bbt3
drop table #bbt4
drop table #bb5
drop table #bb6
DROP TABLE #bb7

--不明白的可以加我企鹅：58556211