with bomComponent (FbomMaster,子件料号,子件件号,母件品名,规格,用量,母件底数,料品的形态属性,发料方式) as (
select c3.BOMMaster FbomMaster , c4.Code 子件料号,c4.Code1 子件件号, c4.Name 母件品名, c4.SPECS 规格, c3.UsageQty 用量, c3.ParentQty 母件底数,
(case when c4.ItemFormAttribute=0 then '模型' when c4.ItemFormAttribute=1 then '按订单拣货' when c4.ItemFormAttribute=2 then '按订单装配' when c4.ItemFormAttribute=3 then '可选类' when c4.ItemFormAttribute=4 then '委外加工件' when c4.ItemFormAttribute=5 then '工序委外加工' when c4.ItemFormAttribute=6 then '虚拟' when c4.ItemFormAttribute=7 then '计划' when c4.ItemFormAttribute=8 then '系列' when c4.ItemFormAttribute=9 then '采购件' when c4.ItemFormAttribute=10 then '制造件' when c4.ItemFormAttribute=11 then '最终装配件' when c4.ItemFormAttribute=12 then '套件' when c4.ItemFormAttribute=13 then '工艺 ' when c4.ItemFormAttribute=14 then '低值易耗品' when c4.ItemFormAttribute=15 then '商品' when c4.ItemFormAttribute=16 then '杂项料品' when c4.ItemFormAttribute=17 then '服务' when c4.ItemFormAttribute=18 then '资产' when c4.ItemFormAttribute=19 then '备件' when c4.ItemFormAttribute=20 then '配方' when c4.ItemFormAttribute=21 then '重复' when c4.ItemFormAttribute=22 then '费用性料品' end) 料品的形态属性,
(case when c3.IssueStyle=0 then '推式' when c3.IssueStyle=1 then '工序倒冲' when c3.IssueStyle=2 then '完工倒冲' when c3.IssueStyle=3 then '开工倒冲' when c3.IssueStyle=4 then '不发料' end) 发料方式
from CBO_BOMComponent c3
left join CBO_ItemMaster c4 on c3.ItemMaster = c4.ID
),
FbomComponent (bomMaster,母件料号,母件件号,母件品名,规格) as (
select m1.ID bomMaster, c2.Code 母件料号,c2.Code1 母件件号, c2.Name 母件品名, c2.SPECS 规格
from CBO_BOMMaster m1
left join CBO_ItemMaster c2 on m1.ItemMaster = c2.ID
),
fullBom (bomMaster,母件料号,母件件号, 母件品名, 规格, FbomMaster, 子件料号,子件件号,子件品名, 子件规格, 用量, 母件底数, 料品的形态属性, 发料方式) as (
select f.bomMaster bomMaster, f.母件料号 母件料号, f.母件件号 母件件号,f.母件品名 母件品名, f.规格 规格, c.FbomMaster FbomMaster, c.子件料号 子件料号,c.子件件号 子件件号,c.母件品名 子件品名, c.规格 子件规格, c.用量 用量, c.母件底数 母件底数, c.料品的形态属性 料品的形态属性, c.发料方式 发料方式
from FbomComponent f
left join bomComponent c on c.FbomMaster = f.bomMaster
),
tree_teset (bomMaster,母件料号,母件件号,母件品名, 规格, FbomMaster, 子件料号,子件件号,子件品名, 子件规格, 用量, 母件底数, 料品的形态属性, 发料方式) as (
select bomMaster, 母件料号,母件件号, 母件品名, 规格, FbomMaster, 子件料号,子件件号, 子件品名, 子件规格, 用量, 母件底数, 料品的形态属性, 发料方式
from fullBom
----where 母件料号 ='' --查询单个BOM
union all
select t1.bomMaster, t1.母件料号,t1.母件件号,t1.母件品名, t1.规格, t1.FbomMaster, t1.子件料号, t1.子件件号,t1.子件品名, t1.子件规格, t1.用量, t1.母件底数, t1.料品的形态属性, t1.发料方式
from fullBom t1
join tree_teset t2 on t1.母件料号 = t2.子件料号
)
select * from tree_teset
U9Bom阶层展开脚本
最新推荐文章于 2024-01-19 09:52:47 发布