报表类：金蝶-BOM多级展开（方法一）

临时表方式，适用于单一产品产品。

   alter proc [dbo].[BOMSELECT_CW]  @kkk VarCHAR(80)
    AS
    
set nocount ON




if OBJECT_ID('tempdb..HWbom')<>0  
drop table HWbom

if OBJECT_ID('tempdb..HWbomchild')<>0  
drop table HWbomchild





create table HWbom  
(  
FId int identity(1,1),  
FItemID int  
)  

create table HWbomchild  
(  
    FID int identity(1,1),  
    FOrgID int,  
    FParentID int,  
    FLevel int,  
    FSN nvarchar(200),  
    FItemID int,  
    FQty decimal(28,19),  
    FBOMInterID int,  
    FEntryID int  
)  
insert into HWbom  
(FItemID)  
select t.Fitemid from t_ICItem t  
inner join t_item t5 ON t5.FItemID = t.Fitemid  
left join icbom t6 on t6.fitemid=t.Fitemid  
left join ICBOMGROUP t7 on t7.finterid=t6.fparentid  
where   
t.FErpClsID in (2,3,5)--2代表自制件，代表委外件，代表虚拟件 
and t5.fdeleted=0   
--and t5.fnumber = '@kkk' --可根据需要限定BOM范围 
and t6.fusestatus=1072
order by t.fnumber

insert into HWbomchild  
(FOrgID,FParentID,FSN,FItemID,FQty,FBOMInterID,FEntryID,FLevel)  
select   
FId,-1 as FParentID,'001',u1.FItemID,1 as Fqty,t1.FInterID as FBOMInterID,-1  as FEntryID,0  
from HWbom u1  
left join ICBOM t1 on u1.FItemID=t1.FItemID   where t1.fusestatus=1072


declare @level int  
set @level=1  
  
while exists(  
    select 1 from   
    HWbomchild  
    where FLevel=@level-1  
    and FItemID in   
    (select Fitemid from icbom where fusestatus=1072 )  
)  
and @level<20  
begin  
  
    insert into HWbomchild  
    (FOrgID,FParentID,FSN,FItemID,FQty,FBOMInterID,FEntryID,FLevel)  
    select  
    u1.FOrgID,u1.FID,u1.FSN+'.'+right('000'+CONVERT(nvarchar(50),t2.Fentryid),3),t2.Fitemid,u1.FQty*(t2.FQty/t1.FQty)/(1-t2.FScrap/100),  
    t2.FInterID,t2.FEntryID,@level  
    from HWbomchild u1  
    inner join icbom t1 on u1.FItemID=t1.FItemID  
    inner join ICBOMChild t2 on t2.FInterID=t1.FInterID  
    where u1.FLevel=@level-1  and t1.fusestatus=1072
  
    set @level=@level+1  
  
end 


select  
t2.FNumber 产品代码,t2.FName 产品名称,t2.FModel 产品规格,  
t1.FSN 序号,  
t3.FNumber 材料代码,t3.FName 材料名称,t3.FModel 材料规格,t1.FQty 产品用量,yy.fname 材料属性,  
t5.FQty 单位用量,  
t5.FScrap 损耗率,  
t4.FBOMNumber BOM编号,  
t6.FName as 是否跳层,  
t5.FNote 备注,  
t5.FPositionNo 位置号  
 from HWbom u1  
inner join HWbomchild t1 on u1.FId=t1.FOrgID  
inner join t_icitem t2 on t2.FItemID=u1.FItemID  
inner join t_ICItem t3 on t3.FItemID=t1.FItemID  
left join ICBOM t4 on t4.FInterID=t1.FBOMInterID  
left join ICBOMChild t5 on t5.FInterID=t1.FBOMInterID and t5.FEntryID=t1.FEntryID  
left join t_SubMessage t6 on t6.FInterID=t4.FBOMSkip  
inner join t_SubMessage yy on yy.FInterID=t3.FErpClsID  
WHERE  t2.FNumber LIKE @KKK

order by u1.FId,t1.FSN 



drop table HWbom  
--删除父表 
drop table HWbomchild  
--删除子表 
--set nocount off
--EXEC BOMSELECT_CW 'HJ9003A'