K3 BOM多层展开及即时库存SQL

create table #bom
(
FId int identity(1,1),
FItemID int,
FNumber nvarchar(200)
)

create table #bomchild
(
    FID int identity(1,1),
    FOrgID int,
    FParentID int,
    FLevel int,
    FSN nvarchar(200),
    FItemID int,
    FQty decimal(28,19),
    FBOMInterID int,
    FEntryID int

)

Create Table #TempInventory(

FItemID int NOT NULL ,
FQty decimal(28,10))

insert into #bom
(FItemID,FNumber)
select  Fitemid,FNumber from t_ICItem where FErpClsID in (2,3)



insert into #bomchild
(FOrgID,FParentID,FSN,FItemID,FQty,FBOMInterID,FEntryID,FLevel)
select
FId,-1 as FParentID,'0',u1.FItemID,1 as Fqty,t1.FInterID as FBOMInterID,-1  as FEntryID,0
from #bom u1
left join ICBOM t1 on u1.FItemID=t1.FItemID and t1.FUseStatus=1072   
order by FItemID


declare @level int
set @level=1

while exists(
    select 1 from
    #bomchild
    where FLevel=@level-1
    and FItemID in
    (select Fitemid from icbom)
)
and @level<20
begin

    insert into #bomchild
    (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 #bomchild u1
    inner join icbom t1 on u1.FItemID=t1.FItemID
    inner join ICBOMChild t2 on t2.FInterID=t1.FInterID and t1.FUseStatus=1072
    where u1.FLevel=@level-1

    set @level=@level+1

end

Insert Into #TempInventory
Select t1.fitemid,sum(u1.FQty) From ICInventory u1
left join t_ICItem t1 on u1.FItemID = t1.FItemID
left join t_Stock t2 on u1.FStockID=t2.FItemID
left join t_MeasureUnit t3 on t1.FUnitID=t3.FMeasureUnitID
left join t_MeasureUnit t4 on t1.FStoreUnitID=t4.FMeasureUnitID
left join t_StockPlace t5 on u1.FStockPlaceID=t5.FSPID
left join t_AuxItem t9 on u1.FAuxPropID=t9.FItemID
left join t_Measureunit t19 on t1.FSecUnitID=t19.FMeasureunitID
where (Round(u1.FQty,t1.FQtyDecimal)<>0 OR Round(u1.FQty/t4.FCoefficient,t1.FQtyDecimal)<>0)
and t1.FDeleted=0 AND t2.FTypeID in (500,20291,20293)
group by t1.fnumber,t1.fitemid


select
t2.FNumber 产品代码,t2.FName 产品名称,t2.FModel 产品规格,
t1.FSN 序号,
t3.FNumber 材料代码,t3.FName 材料名称,t3.FModel 材料规格,t1.FQty 产品用量,
t5.FQty 单位用量,
t5.FScrap 损耗率,
t4.FBOMNumber BOM编号,
t6.FName as 是否跳层,
t5.FNote 备注,
t5.FPositionNo 位置号,
inv2.FQty as 成品即时库存,
inv1.FQty as 即时库存,
t1.FBOMInterID,t1.FEntryID
 from #bom
inner join icbom on icbom.FItemID=#bom.FItemID and icbom.FUseStatus = 1072
left join #TempInventory inv2 on inv2.fitemid=#bom.FItemID
inner join #bomchild t1 on #bom.FId=t1.FOrgID
left join #TempInventory Inv1 on Inv1.fitemid=t1.FItemID
inner join t_icitem t2 on t2.FItemID=#bom.FItemID
inner join t_ICItem t3 on t3.FItemID=t1.FItemID
left join ICBOM t4 on t4.FInterID=t1.FBOMInterID and t4.FUseStatus = 1072
left join ICBOMChild t5 on t5.FInterID=t1.FBOMInterID and t5.FEntryID=t1.FEntryID and t4.FUseStatus = 1072
left join t_SubMessage t6 on t6.FInterID=t4.FBOMSkip
where t2.FNumber = '1.01.GU7T9A006'
order by t2.FNumber,t1.FSN,#bom.FId
select      
t1.FTrantype,t1.FinterID as FOrgInterID,t2.FEntryID as FOrgEntryID,t1.Finterid,t2.Fentryid,81 as Ftype,t1.FBillNo,t2.FItemID,t2.FAdviceConsignDate as FDate,      
t2.FDate as FEndDate,t2.FQty-FStockQty,0,t1.fstatus,t2.FSourceInterid,t2.FSourceTrantype,0 as FWorkShop,t2.FSourceEntryID                                                                        
from SEOrder t1      --销售订单
inner join SEOrderEntry t2 on t1.Finterid=t2.Finterid   
inner join t_ICItem t3 on t3.FItemID=t2.FItemID and t3.FPlanTrategy=321
--INNER JOIN t_Organization t3 ON t3.FItemID=t1.FCustID AND (t3.FNumber='81' OR t3.FNumber='K65')  --select * from t_Organization where fnumber IN ('81','K65')
where FCancellation=0 and t2.FMrpclosed=0 and t1.fstatus>0  



drop table #bom
drop table #bomchild
drop table #TempInventory