取云星空所有有效bom数据

;with cte as
(
–1、定点（Anchor）子查询，用来查询最顶级的产品的BOM的
select
0 as BOM层次,t1.fid as 最顶级BOM内码
,t1.FNUMBER as BOM版本,fxwl.FNUMBER as 父项物料代码,fxwl_L.FNAME as 父项物料名称,t3.FSEQ as 分录行号
,t3.FREPLACEGROUP as 项次,CAST(10000+t3.FREPLACEGROUP AS nvarchar) as 项次组合
,cast(CAST(t1.fid AS nvarchar)+‘-’+CAST(10000+t3.FREPLACEGROUP AS nvarchar) as nvarchar(max)) as BOM内码和项次组合
,t3.FMATERIALID as 子项物料内码,zxwl.FNUMBER as 子项物料代码,zxwl_L.FNAME as 子项物料名称
,case when FMATERIALTYPE = 1 then ‘标准件’
when FMATERIALTYPE = 2 then ‘返还件’
when FMATERIALTYPE = 3 then ‘替代件’
else ‘未知类型’ end as 子项类型
,t3.FNUMERATOR as 分子,t3.FDENOMINATOR as 分母,t3.FFIXSCRAPQTY as 固定损耗,t3.FSCRAPRATE as 变动损耗,t3.FBOMID,t1.FUSEORGID
,0 as 是否有子项BOM版本
from dbo.T_ENG_BOM t1
join T_BD_MATERIAL fxwl --用父项关联物料表
on fxwl.FMATERIALID = t1.FMATERIALID
and t1.FFORBIDSTATUS = ‘A’ --只取未禁用状态的BOM
join T_BD