U9Bom阶层展开脚本

declare @maincode char(60)
set @maincode='10.1210.0012'--产成品品号
declare @i int
set @i=0


--SELECT MD003 FROM #BOMMD WHERE MD001='CG.16120509001.00909'

select item.ID MID,item.Code MD001,item.Name MName,itemlin.ID ZID,itemlin.Code MD003,itemlin.Name ZName,
Convert(decimal(18,3),B.UsageQty) UsageQty --用量
,Convert(decimal(18,3),B.ParentQty) ParentQty --母件底数
,Convert(decimal(18,3),Convert(decimal(18,3),B.UsageQty)/Convert(decimal(18,3),B.ParentQty)) MD006 --计算实际用量
INTO #BOMMD
from CBO_BOMMaster as A  
inner join CBO_BOMComponent as B on A.ID = B.BOMMaster
left join CBO_ItemMaster item on A.ItemMaster=item.ID
left join CBO_ItemMaster itemlin on B.ItemMaster=itemlin.ID
where 
A.BOMType=0  --0 为自制件 1为委外
and A.AlternateType=0 --0 生产目的为主制造 1 为委外
and A.BOMType=0 --0 BOM类型为自制  1为委外 10.0115.0006
and 
A.Org=1002011047606283
and A.DisableDate > GETDATE() --失