月份: 2022 年 10 月

–bom2要被減少的階層 此處用量皆為重量，算出總重量，屆時可依比例更新用量回主BOM，即bom1

update Cf_inv_bom_2
set Cf_inv_bom_2.sum_qty = t1.c_sum
from (select assy_no,sum(qty) c_sum from Cf_inv_bom_2 group by assy_no) t1
where Cf_inv_bom_2.assy_no = t1.assy_no

–bom1無下階先抓出來

select * from cf_inv_bom_1
where item_no not in (select assy_no from cf_inv_bom_2 )

–再結合有bom1有下階的資料(用量=>被替換子件原用量*(下階子件用量/下階用量之和))

select * from cf_inv_bom_1
where item_no not in (select assy_no from cf_inv_bom_2 )
union all
select T1.assy_no,T2.item_no,CAST(t1.qty*t2.qty/t2.sum_qty AS DECIMAL(10,4) ) d_qty,T1.sum_qty
from cf_inv_bom_2 T2,cf_inv_bom_1 T1
where T2.assy_no=T1.item_no order by 1,2

–由於合併後發現兩階間有共用子件因此多一層group來加總資件用量

select Tu.assy_no,tu.item_no,sum(tu.qty),max(Tu.sum_qty) from
(select [assy_no]
,[item_no]
,[qty]
,[sum_qty] from cf_inv_bom_1
where item_no not in (select assy_no from cf_inv_bom_2 )
union all
select T1.assy_no,T2.item_no,CAST(t1.qty*t2.qty/t2.sum_qty AS DECIMAL(10,4) ) d_qty,T1.sum_qty
from cf_inv_bom_2 T2,cf_inv_bom_1 T1
where T2.assy_no=T1.item_no) as Tu
group by Tu.assy_no,tu.item_no
order by 1,2

Sub LoopThroughRowsByRefBom()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
FirstRow = 1
i = FirstRow
FirstColumn = 1
Do Until i > LastRow
    LastColumn = Cells(i, Columns.Count).End(xlToLeft).Column
    j = FirstColumn
    Do Until j > LastColumn
        If i Mod 15 = 1 And j Mod 3 = 1 Then
            For k = 0 To 10
               Cells(i + k, j + 2).Value = Cells(i, j)
            Next k
        End If
        j = j + 3
    Loop
    i = i + 15
Loop

 Dim dynArray(1 To 5000, 1 To 4) As String
 i = 1
  k = 1
 Do Until i > LastRow
     LastColumn = Cells(i, Columns.Count).End(xlToLeft).Column
    j = 1
    Do Until j > LastColumn
        If j Mod 3 = 1 And i Mod 15 <= 10 And Not IsEmpty(Cells(i, j + 1).Value) And Cells(i, j + 1).Value > 0 Then
            dynArray(k, 1) = Cells(i, j + 2)
            dynArray(k, 2) = Cells((i \ 15) * 15 + 12, j + 1)
            dynArray(k, 3) = Cells(i, j)
            dynArray(k, 4) = Cells(i, j + 1)
             k = k + 1
        End If
        j = j + 3
    Loop
  i = i + 1
Loop

Dim De1 As Range
Set De1 = Range("AK1")
De1.Resize(UBound(dynArray, 1), UBound(dynArray, 2)).Value = dynArray
End Sub