含主件信息版本

select x.PLANT_NAME as 工厂名称,
c.ITEM_CODE as 主件编号,c.ITEM_NAME as 主件名称,c.ITEM_SPECIFICATION as 主件规格,
d.ITEM_CODE as 子件编号,d.ITEM_NAME as 子件名称,d.ITEM_SPECIFICATION as 子件规格,
case b.ITEM_TYPE when '4' then '不发料' else '' end as 发料方式,
b.QTY_PER as 组成用量,b.DENOMINATOR as 底数,b.FIXED_LOSS_RATE as 固定损耗量,b.DYNAMIC_LOSS_RATE as 变动损耗,
CONVERT(char(10),b.EFFECTIVE_DATE, 120) as 生效日期,CONVERT(char(10),b.EXPRITY_DATE, 120) as 失效日期,
b.STANDARD_COST_ITEM as 成本计算,b.REMARK as 备注,
CONVERT(char(10),a.ApproveDate, 120) as 审核日期,y.USER_NAME as 审核人

---a.REFERENCE_PLANT_ID,a.ITEM_ID,b.PARENT_ITEM_ID,b.SOURCE_ID_ROid
FROM BOM a 
left  join BOM_D b on b.BOM_ID=a.BOM_ID
left join ITEM c on a.ITEM_ID=c.ITEM_BUSINESS_ID
left join ITEM d on d.ITEM_BUSINESS_ID=b.SOURCE_ID_ROid
left join PLANT x on x.PLANT_ID=a.REFERENCE_PLANT_ID
left join [USER] y on a.ApproveBy=y.USER_ID
where b.ITEM_TYPE ='4' and a.ApproveStatus ='Y'
ORDER BY c.ITEM_CODE,x.PLANT_NAME

不含主件信息版本

select DISTINCT x.PLANT_NAME as 工厂名称,
d.ITEM_CODE as 子件编号,d.ITEM_NAME as 子件名称,d.ITEM_SPECIFICATION as 子件规格,
case b.ITEM_TYPE when '4' then '不发料' else '' end as 发料方式


---a.REFERENCE_PLANT_ID,a.ITEM_ID,b.PARENT_ITEM_ID,b.SOURCE_ID_ROid
FROM BOM a 
left  join BOM_D b on b.BOM_ID=a.BOM_ID
left join ITEM c on a.ITEM_ID=c.ITEM_BUSINESS_ID
left join ITEM d on d.ITEM_BUSINESS_ID=b.SOURCE_ID_ROid
left join PLANT x on x.PLANT_ID=a.REFERENCE_PLANT_ID
left join [USER] y on a.ApproveBy=y.USER_ID
where b.ITEM_TYPE ='4' and a.ApproveStatus ='Y'
ORDER BY x.PLANT_NAME,d.ITEM_CODE