sQL bom表 标准用量的展开

------------------------------------------------------------------------------标准用量的展开

ALTER proc [dbo].[XXXX_P_GetMetalSubstance]
@fentryid int=null,
@fmaterialid int=null
AS

declare @fmemo nvarchar(500)
declare @color nvarchar(200)

select top 1 @fmemo=b.fmemo,@color=c.F_SRMB_TEXT4 
from T_PRD_MOENTRY a, T_PRD_MOENTRY_L b ,T_BD_MATERIAL c where a.fentryid=b.fentryid and a.fmaterialid=c.fmaterialid
 and   a.fmaterialid=@fmaterialid and a.fentryid=@fentryid

;with cte as
(

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(1000000+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,CAST((t3.FNUMERATOR/t3.FDENOMINATOR) AS decimal(20,10)) 标准用量
	,CAST(((t3.FNUMERATOR/t3.FDENOMINATOR)+((t3.FNUMERATOR/t3.FDENOMINATOR)*CASE WHEN t3.FSCRAPRATE>0 THEN t3.FSCRAPRATE/100 ELSE 0 END)) AS decimal(20,10)) 实际用量
	-----CAST(t3.FNUMERATOR/t3.FDENOMINATOR AS decimal(20,10)) 用量
	,zibase.FERPCLSID 
		,zibase.FBASEUNITID ,ca.FISSKIP,t3.FENTRYID,t3.F_SRMB_ASSISTANT
from dbo.T_ENG_BOM t1
	join T_BD_MATERIAL fxwl			--用父项关联物料表
		on fxwl.FMATERIALID = t1.FMATERIALID
			and t1.FFORBIDSTATUS = 'A'	--只取未禁用状态的BOM
	join T_BD_MATERIAL_L fxwl_L		--用父项关联物料多语言表
		on fxwl.FMATERIALID = fxwl_l.FMATERIALID and fxwl_L.FLOCALEID =2052
	join T_BD_MATERIALPRODUCE fxwl_P
		on fxwl_P.FMATERIALID = fxwl.FMATERIALID
	join T_ENG_BOMCHILD t3
		on t1.fid = t3.FID		
	join T_BD_MATERIAL zxwl			--用子项关联物料表
		on zxwl.FMATERIALID = t3.FMATERIALID
	join T_BD_MATERIAL_L zxwl_L		--用子项关联物料多语言表
		on zxwl.FMATERIALID = zxwl_L.FMATERIALID and zxwl_L.FLOCALEID =2052
    
		join T_BD_MATERIALBASE base on base.FMATERIALID = t1.FMATERIALID
		join T_BD_MATERIALBASE zibase on zibase.FMATERIALID = zxwl_L.FMATERIALID
		join T_ENG_BOMCHILD_A	ca on ca.fid=t1.fid and ca.fentryid=t3.fentryid	

where 1=1
	and fxwl_P.FISMAINPRD = 1		--物料-生产页签的'可为主产品'属性FISMAINPRD,等于1就意味着可以建立BOM 
   -- and fxwl.FNUMBER='5.01.0357'
   and t1.fid in(select max(fid) as fid from T_ENG_BOM 	where fmaterialid=@fmaterialid	group by fmaterialid)

union all
select  
	p.BOM层次+1 as BOM层次,P.最顶级BOM内码 as 最顶级BOM内码
	,t1.FNUMBER as BOM版本,fxwl.FNUMBER as 父项物料代码,fxwl_L.FNAME as 父项物料名称,t3.FSEQ as 分录行号
	,t3.FREPLACEGROUP as 项次,cast(p.项次组合+'.'+CAST(10000+t3.FREPLACEGROUP AS nvarchar) as nvarchar) as 项次组合
	,cast(p.BOM内码和项次组合 +'.'+ ( 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,CAST((t3.FNUMERATOR/t3.FDENOMINATOR)*p.标准用量 AS decimal(20,10)) 标准用量,
	CAST(((t3.FNUMERATOR/t3.FDENOMINATOR)+((t3.FNUMERATOR/t3.FDENOMINATOR)*CASE WHEN t3.FSCRAPRATE>0 THEN t3.FSCRAPRATE/100 ELSE 0 END))*p.实际用量 AS decimal(20,10)) 实际用量
	,zibase.FERPCLSID 
		,zibase.FBASEUNITID ,ca.FISSKIP,t3.FENTRYID,t3.F_SRMB_ASSISTANT
from cte P		--调用递归CTE本身
	join dbo.T_ENG_BOM t1
		on t1.FMATERIALID = p.子项物料内码
	join T_BD_MATERIAL fxwl			--父项关联物料表
		on fxwl.FMATERIALID = t1.FMATERIALID
			and t1.FFORBIDSTATUS = 'A'
	join T_BD_MATERIAL_L fxwl_L		--父项关联物料多语言表
		on fxwl.FMATERIALID = fxwl_l.FMATERIALID and fxwl_L.FLOCALEID =2052
	join T_ENG_BOMCHILD t3
		on t1.fid = t3.FID		
	join T_BD_MATERIAL zxwl			--子项关联物料表
		on zxwl.FMATERIALID = t3.FMATERIALID
	join T_BD_MATERIAL_L zxwl_L		--子项关联物料多语言表
		on zxwl.FMATERIALID = zxwl_L.FMATERIALID and zxwl_L.FLOCALEID =2052

		join T_BD_MATERIALBASE base on base.FMATERIALID = t1.FMATERIALID
		join T_BD_MATERIALBASE zibase on zibase.FMATERIALID = zxwl_L.FMATERIALID
		join T_ENG_BOMCHILD_A	ca on ca.fid=t1.fid and ca.fentryid=t3.fentryid	

    WHERE T1.FDocumentStatus='C'
),cte2_ZuiXinZiXiangBom as
(
select
t1.BOM层次 as BOM层级,t1.最顶级BOM内码,t1.BOM版本
,t1.父项物料代码 as 物料代码,t1.父项物料名称 as 物料名称,0 as 分录行号,0 as 项次,t1.项次组合 as 项次组合,BOM内码和项次组合,
0 as 子项物料内码,'' as 子项物料代码,'' as 子项物料名称,'最顶层父项' as 子项类型,0 as 分子,0 as 分母,0 as 固定损耗,0 as 变动损耗,
0 as BOM内码,t1.FUSEORGID,'1' as 标准用量,1 as 实际用量
--t1.用量
,dense_rank() over(partition by t1.最顶级BOM内码,t1.父项物料代码 order by t1.BOM版本 desc) as BOM版本号分区
,FERPCLSID 
		,FBASEUNITID ,FISSKIP,FENTRYID,F_SRMB_ASSISTANT
from cte t1
where 1=1
and t1.BOM层次 = 0 and t1.项次组合 = '10001'

union 
select 
	t1.BOM层次+1 as BOM层级,t1.最顶级BOM内码,t1.BOM版本
	,t1.子项物料代码 as 物料代码,t1.子项物料名称 as 物料名称,t1.分录行号 as 分录行号,t1.项次 as 项次,t1.项次组合 as 项次组合,
	BOM内码和项次组合,0 as 子项物料内码,t1.子项物料代码 as 子项物料代码,'' as 子项物料名称,t1.子项类型 as 子项类型,t1.分子 as 分子,t1.分母 as 分母,t1.固定损耗 as 固定损耗,t1.变动损耗 as 变动损耗,t1.FBOMID as BOM内码,t1.FUSEORGID,t1.标准用量,t1.实际用量
	,dense_rank() over(partition by t1.最顶级BOM内码,t1.父项物料代码 order by t1.BOM层次+1,t1.BOM版本 desc) as BOM版本号分区
	,FERPCLSID 
		,FBASEUNITID ,FISSKIP,FENTRYID,F_SRMB_ASSISTANT
from cte t1
where 1=1
)
--select * from cte2_ZuiXinZiXiangBom t2 ----调试第二段CTE

select t2.BOM层级 as BOM层级
,t2.物料代码 as 子项物料编码,t2.物料名称 as 物料名称,t2.分录行号 as 分录行号,t2.项次 as 项次,t2.子项类型 as 子项类型,t2.分子 as 分子,t2.分母 as 分母
,t2.固定损耗 as 固定损耗,t2.变动损耗 as 变动损耗
,t2.FUSEORGID,t2.项次组合 as 项次组合,t2.BOM内码和项次组合,t2.BOM内码 as 子项BOM版本内码,t2.BOM版本 as 所属BOM,t2.最顶级BOM内码,
round(t2.标准用量+0.004,2) 标准用量,
t2.实际用量,
left(t2.BOM版本,charindex('_',t2.BOM版本)-1) 父物料,
case when 		FERPCLSID = 10 then '资产'
		when 		FERPCLSID = 9 then '配置'
		when 		FERPCLSID = 2 then '自制'
		when 		FERPCLSID = 11 then '费用'
		when 		FERPCLSID = 12 then '模型'
		when 		FERPCLSID = 5 then '虚拟'
		when 		FERPCLSID = 7 then '一次性'
		when 		FERPCLSID = 13 then '产品系列'
		when 		FERPCLSID = 3 then '委外'
		when 		FERPCLSID = 4 then '特征'
		when 		FERPCLSID = 6 then '服务'
		when 		FERPCLSID = 1 then '外购'
		end 物料属性,
		FISSKIP 是否跳层,
		mm2.fmaterialid,
		mm2.F_SRMB_TEXT31 客户编码,
		mm2.F_SRMB_TEXT3 材质,
		d2.fname 单位	,	
		c2.fnumber 单位编码, 
		case when t2.BOM层级<>0 then e.fdatavalue else null end 工位,
		isnull(@fmemo,'') as fmemo,
		case when isnull(@color,'')='' then '' else	'委外 '+isnull(@color,'') end as color
--这一行的可以注释掉,只是为了排查SQL问题用的.
from cte2_ZuiXinZiXiangBom t2
left join T_BD_MATERIAL mm2 on  mm2.fnumber=t2.物料代码
LEFT JOIN  T_BD_UNIT c2 ON  c2.FUNITID=t2.FBASEUNITID    
LEFT JOIN T_BD_UNIT_L d2 ON d2.FUNITID = c2.FUNITID 
LEFT JOIN  T_BAS_ASSISTANTDATAENTRY_L e ON t2.F_SRMB_ASSISTANT=e.FENTRYID 
where 1=1
and t2.BOM版本号分区 = 1 --通过“BOM版本号分区”标识最新版本的BOM，按照父项物料分区之后，把BOM版本降序排列，BOM版本高的值就是1
and ( (t2.BOM层级 = 0 and t2.项次组合 = '10001' ) or (t2.BOM层级 > 0) ) --这个是为了查询出最终的结果.
--and t2.FUSEORGID =100003 --蓝海机械账套的‘总装事业部’组织
--and t2.BOM层级=2
--and t2.BOM层级=t2.项次
and t2.FISSKIP=0 --不跳层
	and (t2.FERPCLSID=2 or t2.FERPCLSID=3) --自制 或者 委外
	and t2.BOM层级>0
order by t2.BOM内码和项次组合